Dynamic WHERE statement or criteria

[Guest]

This is SO frustrating, as I did this ages ago, but have since lost the code
and I've been trawling the internet all day.

All I want to do is pass an argument to a select query. The field name is
'Authority Type', in table 'Authority'. I'm positive there was a docmd
function or something similar. I tried using .parameter(), but don't quite
get how that works. I stuck some dummy criteria in the query, but the
parameter.count value never showed more than 0. Is a parameter something else
entirely? RETARDED.

Is there not something like qdf.execute(Where options) or something???? I
just need to pass the field name and lookup value!!!

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


Set db = CurrentDb
Set qdf = db.QueryDefs("Authorities") 'Authorities is a basic two-field
query

Set rs = qdf.OpenRecordset
Do While rs.EOF = False
thisAuthority = rs(1).Value
MsgBox thisAuthority
rs.MoveNext
Loop

 

[David C. Holley]

When you have a parameter in a query, SET the .PARAMETER object variable
before opening the RecordSet.

 

[Guest]

Alright, well neither of these work, so what's the syntax? Is a parameter
just a field added to the query design mode editor?

qdf.Parameters("Authority Type") = "BC"
qdf.parameters("[Authority Type]") = "BC"

I always get a "No Items in this collection" error.

 

[Guest]

Apologies. Somehow, I missed the emphasis on SET.

I can't get any Set commands to work, though.

Set qdf.Parameters(1) = "BC" ....returns an invalid use of Property error

 

[Tim Ferguson]

qdf.Parameters("Authority Type") = "BC"
qdf.parameters("[Authority Type]") = "BC"

I always get a "No Items in this collection" error.

So what is the SQL of the query?

Tim F