Access Querydef with 2 parameters problem.

[LSteve]

I am a newbie at VBA and in need of some help with how best to create a recordset from an existing Access Query that requires 2 Parameters from an open form. This query works fine as an ordinary query but i just can't get it to work using VBA. The 2 fields in the form to be used as parameters are
[CompanyName] and [MthFwd]. I am working with Access 2003 in Win XP. I have Microsoft DAO 3.6 in the references. Thanks in advance for any help.
My code is:

Dim qdf as DAO.Querydef
Dim Rst as DAO.Recordset
Dim Dbs as DAO.Database
Dim sMthFwd, sCompanyName as String

sCompanyName = ([Forms]![ViewIndents by Store]![CompanyName])
sMthFwd = [Forms]![ViewIndents by Store]![IndentOrdDetails Subform]![MthFwd]

set Dbs = CurrentDb()
Set qdf = Dbs.QueryDefs("qryIndentOrdersForEmailing")
   qdf.Parameters![CustomerName] = sCompanyName
   qdf.Parameters![MthFwd] = sMthFwd

Set Rst = qdf.OpenRecordset()

This code gives me an error '3265'
Item not found in this collection

I have tried all kinds of variations in code but no joy so far.

LSteve (L = Learner)

[ADezii]

Just subscribing, be back later.

[ADezii]

1. Hard code the Criteria itself within the Query Grid in the Criteria Row for each Field:
   [CODE=sql]
   'For the Company Name, in the Criteria Row
   [Forms]![ViewIndents by Store]![CompanyName]
   
   'For the MthFwd Field in the Sub-Form, in the Criteria Row
   [Forms]![ViewIndents by Store]![IndentOrdDetail s Subform]![MthFwd][/CODE]
2. Evaluate each Parameter in qryIndentOrders ForEmailing by using the Eval() Function.
3. Please find the complete code below:
   [CODE=vb]Dim qdf As DAO.QueryDef
   Dim Rst As DAO.Recordset
   Dim Dbs As DAO.Database
   Dim prm As DAO.Parameter
   
   Set Dbs = CurrentDb()
   Set qdf = Dbs.QueryDefs(" qryIndentOrders ForEmailing")
   
   For Each prm In qdf.Parameters
   prm.Value = Eval(prm.Name)
   Next prm
   
   Set Rst = qdf.OpenRecords et(dbOpenDynase t)[/CODE]
4. Open the Recordset once the Parameters have been evaluated (last code line).
5. Remember, since Rst was declared at the Procedure Level, once you exit the procedure your Recordset has no existence. If any processing of the Recordset will be done within this Procedure, all is fine, if not Rst must be declared at either the Form Level or Publicly within a Standard Code Module.

[NeoPa]

Subscribing.
Interested in Parameters at the moment.

[NeoPa]

Steve, you don't give the SQL for [qryIndentOrders ForEmailing].

I suspect it is missing one or other of [CustomerName] and [MthFwd] in the PARAMETERS clause.

[NeoPa]

Rereading your first post, perhaps it should be [CompanyName] instead of [CustomerName]?

[LSteve]

Thankyou all for your replys. I solved the problem by using the code sugested by ADezii. It works well. Thankyou All