Question Recordset gives error "too few parameters"

[Malcolmneill]

I have viewed a lot of posts regarding problems in this area but can't seem to find the answer. I am not a VBA/Access beginner, but am still at an early stage.
My problem description:
I have a form with a subform which allows for the input of a number of items. Think of a reference sheet with related items. Each sheet can have multiple items from 1 to "n", but in practice less than 10. On saving the Form I need to determine the number of items attached to this sheet and then store it. So on exiting/saving the data I execute code to count the number of records and store the result.

I have attached a word document showing the error and the statement, BUT I have used this format elsewhere several times and it's fine. The issue seems to be that it doesn't like a temporary Variable, but why? The query simply selects all records attached to a specified sheet, the Sheet Number is in a "TempVar". If I execute the query with a DoCmd it's fine but doing the same thing with the openrecordset fails.

I tried using a variable defined in the sub routine but no change and you can see in the code various options of the Set statement. If I try the last one (labelled no 5) with a valid sheet no "M244" it does not fail but does not select anything either! If I use no **3 this is OK, but another query which has fixed criteria for the selection.

This has got me baffled so please any suggestions? If there's a better way then please advise.

Thanks
Malcolm

 

[JHB]

Put the fieldname in []
And the criteria outside the string

If [tempvars]![sheet_no] number criteria then:
Set rst = db.OpenRecordset("SELECT * FROM item_table  WHERE [B][COLOR=Red][[/COLOR][/B]Sheet no:[B][COLOR=Red]][/COLOR][/B]=[B][COLOR=Red]" & [/COLOR][/B][tempvars]![sheet_no] 
If [tempvars]![sheet_no] text criteria then:
Set rst = db.OpenRecordset("SELECT * FROM item_table  WHERE [B][COLOR=Red][[/COLOR][/B]Sheet no:[B][COLOR=Red]][/COLOR][/B]=[B][COLOR=Red]'"[/COLOR][/B] & [tempvars]![sheet_no] & [B][COLOR=Red]"'"[/COLOR][/B]

 

[Malcolmneill]

Thanks JHB, it works great. Not sure I understand why you need the "&" etc but hey If that's the way to do it that's fine.

Regards
Malcolm

 

[namliam]

You need the [] because you are using spaces in your column names (and likely elsewhere).... it is good practice to not use spaces or special characters (and reserved words) as column names to prevent this issue from causing issues.

You use & to put two things together, commonly these things are strings or atleast one of them is a string like in your SQL.
Much like you need a +, *, - or / when using multiple numbers like
2 + 2
8 * 3
etc.... Thus you need to do something like
"this text" & " that text " & SomeVar & "more text"