How To: Create a recordset from a query (with criterias)

[06082007]

Hi,

-I am getting an error "3061" Too few parameters expected 3.

-I want to create a recordset from a query so that I can use the findnext method to search for some data and edit it. The following code gives me the error:

Dim db As Database
Dim rst As Recordset
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("Data") <----Error from this line


-Here's information about my program:

I have 2 tables, a query. The query links the two tables together.

Tables: Info & Price
Query: Data

In the query, I have set some columns with criteria. Columns strDwgNo, strIDCust, strqrStatus.

The criteria: strDwgNo: Like [forms]![queryform]![drawingNo] & "*"
strIDCust: Like [forms]![queryform]![txtIDCust] & "*"
strqrStatus: Like [forms]![queryform]![Temp] & "*"

queryform is a form that links to the query (Data) to control some outputs. drawingNo, txtIDCust & Temp are names of some textbox.

Highly appreciate any help. Thankyou

yhp

 

[Remou]

Set up an SQL string and use that in place of "Data"

strSQL="SELECT strDwgNo, strIDCust, strqrStatus " _
& "FROM Info INNER JOIN Price ON Price.ID=Info.ID " _
& "WHERE strDwgNo Like '" & _
[forms]![queryform]![drawingNo] _
& "*' AND strIDCust Like '" _
& [forms]![queryform]![txtIDCust] _
& "*' AND strqrStatus Like '" _
&[forms]![queryform]![Temp] & "*'"

The above is typed, not tested, it assumes that all the fields in the Where statement are text.

You should be able to cut and past from SQL view on the query design screen, and then tidy to create a string resembling the above.

Make sure that queryform is open.

 

[PHV]

what about this ?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("Data")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886