Open query with "form element" as a dao.recordset

[ino_mart]

All

Hereby a simplified example of my problem.

I created a form with a combobox and a regular Access Query with next SQL-code

SELECT tblItems.Item, tblItems.Cost, tblItems.ID
FROM tblItems
WHERE (((tblItems.ID)=[forms]![frmmain]![cboItem]));

If frmMain is open and you select an item from cboItem, query above runs fine if you double click it.

However, I also have a button on my form to run some VB-code where this query has to be used. So the code contains

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("qrySelectedItem")

When the VBA-code tries to open the recordset, it results in error "Run time error 3061: too few parameters. Expected 1".

I guess this is somehow caused as the VBA-code takes the SQL-string literally and it does not replace [forms]![frmmain]![cboItem])) into the real value on the form.

How can I solve this? Important notice: I must use the Access query as base. I do know I could solve this by using something like below, but this is no option. The recordset must use "qrySelectedItem"

strSQL="select * from tblItems where [ID]=" & me.cboItem
set rs = CurrentDB.OpenRecordset(strSQL)

Does somebody knows a solution?

 

[ino_mart]

I found some solution, although it is a rather "dirty" one. If someone knows a better method, please let me know.

Dim rs As Recordset
Dim QD As DAO.QueryDef
Dim strSQLDef, strSQL As String
Set QD = CurrentDb.QueryDefs("qryselecteditem")
strSQLDef = QD.SQL
strSQL = Replace(strSQLDef, "WHERE (((tblItems.ID)=[forms]![frmmain]![cboItem]));", "WHERE (((tblItems.ID)= " & [Forms]![frmmain]![cboItem] & "));")
Set rs = CurrentDb.OpenRecordset(strSQL)

 

[spikepl]

Google qdf parameters

 

[namliam]

Dim strSQLDef, strSQL As String

Just as an FYI, this way of defining string is not "right" strSQLDef is currently a "variant".
If you want both to be a string you need to do

Dim strSQLDef as string, strSQL As String

And as spike said, querydef parameters is your way to go

 

[ino_mart]

Thanks!
The "QueryDef.Parameters"-option did solve my problem:

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelectedItem")
qdf.Parameters("[forms]![frmmain]![cboItem]") = Me.cboItem
Set rs = qdf.OpenRecordset
While Not rs.EOF
    Debug.Print rs!Item
    rs.MoveNext
Wend

Topic can be closed

 

[namliam]

STRONG recomendation.... disambiguate
Recordsets can be DAO or ADODB in this case it is mostlikely the default DAO.... but making sure it is can save a few headaches.

Also instead of hardcoding your parameter, you are aware you can work with indexes
qdf.Parameters(1) = ...
Can save a few headaches here and there as well.