g a TempVar in an Access SQL query

[Petrol]

I was under the impression that TempVars could be used in SQL queries, including in the Criteria field, but this seems not to be the case.
I wrote the following test query:

 SELECT People.PersonID , Board_service_h istory.Communit yCode
FROM People INNER JOIN Board_service_h istory ON People.PersonID = Board_service_h istory.PersonID
WHERE (((Board_servic e_history.Commu nityCode)=[TempVars]![communitycode]));

and tested it with the following sub:

Private Sub TestSub()
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordse t("TestQuery" , dbOpenForwardOn ly)
Debug.Print rs!PersonID, rs!CommunityCod e
End Sub

It gets "Error 3061. Too few parameters. Expected 1." on the Set rs statement.
If I remove the WHERE condition from the query it runs successfully.

[VarType(TempVar s!CommunityCode ) = 8, and the apparent spaces in the above code segments are not there in the original].

Am I missing something?

[Nauticalgent]

Can you share the SQL for TestQuery? I suspect the issue is there...

[Petrol]

Sure, Nauticalagent. The SQL for TestQuery is the first code segment in the OP above, except that in the process of copying it into PostBytes some spurious spaces get inserted that aren't there in the ooriginal. The query runs without error when the third line (WHERE clause) is removed. I presume the capitalisation in CommunityCode makes no difference.

[Nauticalgent]

Sorry, I missed that! The first step it to verify that [TempsVars]![CommunityCode] has a value (add a debug.Print TempVars("Commu nityCode") before you set the RS. If that isnt the problem, does the query run outside of the sub? In other words, can you use the QBE to design the query and run it?

[Nauticalgent]

Also, I think I experienced something similar at one time and resorted to using a parameterized query:

Private Sub TestSub()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("T estQuery")

qdf.Parameters( "pCommunity Code ").Value = [TempVars]![communitycode]

Set rs = qdf.OpenRecords et(dbOpenForwar dOnly)

Debug.Print rs!PersonID, rs!CommunityCod e

rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End Sub

[NeoPa]

Why not see what happens when you simply try to open the QueryDef ("TestQuery" )?
Obviously, show the value in the Immediate Pane first with :
?TempVars!commu nitycode

[Petrol]

Thank you both.
Nauticalagent, yes, I had verified CommunityCode. As you'll see, there is a debug.print in TestSub, and if I remove the WHERE clause in the query the sub runs fine and prints out the values of PersonID (=627) and TempVars!Commun ityCode (=BS). And yes, the query runs fine outside the sub, and results in a datasheet with many rows having different PersonIDs and all with the correct Community code of 'BS'.

I must confess that I have survived 7 years of Access without every having to get into QueryDefs, and parameterised queries are a dark mystery to me, so it's probably high time I learned. But I don't know what to do with your "pCommunityCode ". When I replaced it with CommunityCode, or again when I replaced it with BoardServiceHis tory.CommunityC ode, I got "Error 3265, Item not found in this collection" on the qdf.Parameters line. What should I do to TestQuery to make it run?

NeoPa, TestQuery runs fine from the Navigation pane, and I know the tempvar is valid. All very mysterious!

[Petrol]

Ok, mystery solved. Well no, the mystery isn't solved, but I've got the thing running. It seems Access took exception to my opening the recordset as dbOpenForwardOn ly. When I changed it to dbOpenDynaset, it all ran smoothly.
Sorry to have troubled you. If anyone can explain why ForwardOnly didn't work I'll be grateful.

[NeoPa]

A couple of points, well I suppose three really :

1. I have no idea why the dbForwardOnly would have any such affect as you explain.
2. When displaying the value of TempVars!commun itycode be sure to do it in the code before it crashes so that we get to see the value you intended.
3. The value of 'BS' tells us (for the first time) that it's a string value rather than a numeric one. This is often important when dealing with SQL code, though as you'll see later, this may not be so critical in this case.
   
   TempVars values should behave as typed. This means you should be able to specify the reference without treating it as you would literal strings ordinarily. However, perhaps try your last line as (look out for and remove any spaces that look as if they shouldn't be there) :
   WHERE (((Board_servic e_history.Commu nityCode)='[TempVars]![communitycode]'));

I must say I hold out little hope for that change, for the reasons explained above, but it won't hurt to try it.

[Nauticalgent]

I have no idea why the dbForwardOnly would have any such affect as you explain.

No clue...PFM is the only explanation that comes to mind.

[Nauticalgent]

Ok, mystery solved. Well no, the mystery isn't solved, but I've got the thing running. It seems Access took exception to my opening the recordset as dbOpenForwardOn ly. When I changed it to dbOpenDynaset, it all ran smoothly.
Sorry to have troubled you. If anyone can explain why ForwardOnly didn't work I'll be grateful.

I thought of suggesting to remove that, but that would have been spit-balling, I had no idea if it would work, let alone provide a reason to try it.

[Petrol]

Thanks again, gentlemen.
NeoPa - Point taken, I'll remember that in future - but I did say in the OP "VarType(TempVa rs!CommunityCod e) = 8".
Nauticalagent - I'm not up with the jargon. What's 'PFM' mean?

If anybody comes up with an explanation of why Dynaset was more acceptable than ForwardOnly, let me know - if only for my general education and future understanding. Are there any general principles for when to use ForwardOnly? I had looked up the RecordsetTypeEn umeration in Microsoft Learn, but it was singularly unhelpful about the different types.

Finally, is there any general knowledge out there of what this "Too few parameters" means - or rather, how you can work out which specific item has too few parameters? I've seen it a few times over the years, and haven't been able to recognise any pattern for when it occurs.

[NeoPa]

but I did say in the OP "VarType(Te mpVa rs!CommunityCod e) = 8"

Well, I have to say I'm flattered to think there may be people who assume I can translate numerical VarType values into their textual meanings off the top of my head :-D

Unfortunately I suspect that too few parameters is simply telling you that it's been led to expect some value or other that isn't present. It's general & non-specific. It almost always requires that you examine the SQL, or sometimes even other details of a QueryDef such as what it has for Parameters in a Parameter query, in order to determine for yourself what might be expected that isn't there.

Not always easy as many, many before you have found.

[NeoPa]

As for dbForwardOnly, This (Microsoft Learn) link may answer your questions.

[Nauticalgent]

...Nauticalagen t - I'm not up with the jargon. What's 'PFM' mean?....

Pure F-ing Magic. It's an industry term and widely accepted explanation for Access's behavior...