Access 2007 - Calling a Parameter Query with VBA

[smiley1978]

I have the following code:

Set db = CurrentDb
Set dbQdef = db.QueryDefs("qSel_Pull_Data")

dbQdef.Parameters("Select Tier") = InputBox("Please select a tier.", "Select Tier")

Set rsResults = db.OpenRecordset("qSel_Pull_Data")

I get the following error message:

"Too Few parameters. Expected 1."


The query "qSel_Pull_Data" is a parameter query. There is a field in the query called [tbl_CommunicationLists]![Tier] that has the a parameter criteria [Select Tier]. In my VBA, I am using the InputBox to supply the tier (which is an integer).

I have tried to DIM the dbQdef as DAO.QueryDef but that did not change anything. Any assistance is appreciated.

 

[ranman256]

you went outside of the qdf.. by: set rsResults = db.OpenRecordset("qSel_Pull_Data")
the parameter is IN the qdf , so..

Set rsResults = dbQdef.OpenRecordset()

 

[smiley1978]

Thank you. Now I get a data conversion error. Not sure if it is referring to the value that I enter or if the rsResults will not accept the open recordset. It works fine when I remove the parameters from the source query and just call the query in the VBA.

 

[Micron]

The input box default value is text.
Change InputBox("Please select a tier.", "Select Tier") to InputBox("Please select a tier.", "Select Tier",,,,,,1)

InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)