Parameter Query based on Form Control (1 Viewer)

I have a question regarding a previous post--using recordset as query criteria?

I have a form with several combo boxes corresponding to the fields in a query. The idea is that when a user clicks on the "Find" button on the form, the selections they have made in the combo boxes will be passed to a parameter query.

I came up with the following code based on the above post:

Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset

Set db = CurrentDb
Set qd = db.QueryDefs("qselVariable")
qd.Parameters("Manufacturer") = Forms!frmVariable!cboMFR
Set rs = qd.OpenRecordset

However, when I run this code I receive the following error message:

Run-time error '3061':
Too few parameters. Expected 20.

When I click on debug, the Set rs = qd.OpenRecordset line is the line causing the error.

I am using Access 97 in a windows 98 environment.

Any help with this wuold be much appreciated!!

I think this problem has something to do with needing to use the eval() function. I have used this code after receiving error message 3061 and it solved the issue.

Try inserting this code after your "set qd" and see if this helps at all.

Add this variable first:
Dim prm As Parameter

Then add:

For Each prm In qd.Parameters
prm.value = Eval(prm.Name)

Next prm


Good luck - hope this gets you started in the right direction. Let me know.

E

Well, I think that's a step in the right direction...at least now I'm getting a different error!

I'm getting an "item not found in this collection" error when I don't predefine the Manufacturer parameter in the parameter list for the query. This error points to the "qd.parameters" line

When I do predifnine it, I get:
Run-time error '2482'
Miscrosoft Access can't find the name 'Manufacturer' you entered in the expression

the error points to the prm.Value = Eval(prm.Name) line

After adding eval do I need to change my "qd.parameters" line--or where do I use the "prm.value"

Well, that's the rub. I'm not as good at VBA coding as I would like - i.e., knowing the ins and outs of each property. I'll show you my code that I used to search the recordset of a parameter query (looking for records where the State=CA) - it works perfectly. Maybe it will give you some ideas though:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst1 As DAO.Recordset
Dim CalRec As Boolean

'check to see if a parent has california locations

Set db = CurrentDb
Set qdf = db.QueryDefs("qryParentLocs")
For Each prm In qdf.Parameters
prm.value = Eval(prm.Name)
Next prm

Set rst1 = qdf.OpenRecordset(dbOpenDynaset).Clone

If rst1.RecordCount > 0 Then
rst1.MoveFirst

Do Until rst1.EOF

If rst1!ST = "CA" Then
CalRec = True
Exit Do
Else
CalRec = False

End If

rst1.MoveNext

Loop

Sorry I can't be a bit more helpful. Normally, I just use the trial and error method until the code works...don't always know precisely why it works!

When you figure this out, I hope you post the solution so we can all benefit.

I know what you mean about trial and error! I know very little about VB. I wasn't even familiar with access when I started this project--I've just been learning as I go.

Anyway, It looks like all I needed to do was add the .clone to my openrecordset statement. I did that and it ran without error.

Thanks for the help!

Great - glad I could help!