Microsoft Access, parameter queries, and simple syntax

[chix001]

I have started on the wrong end of the spectrum I think. i am very familiar with ADO, SQL Server and .NET; however, I am now having to write an Access application and the smallest syntax issues are driving me batty.

I have a query that gives me a distinct list of user IDs. I want to create a FOR loop to loop through this list of user ids and pass the current one to another query so that i can email the results to someone.

The problem: I can't figure out how I'm supposed to assign the recordset's datasource to the first query.

Here's what I have:
dim rs as new recordset
rs.datasource = ???
rs.movefirst
for intcount=0 to rs.recordcount-1
'do some work
rs.movenext
next


Any help is greatly appreciated.

Thanks,
CHIX001

 

[ETID]

when you say "the current one", do you mean that you want to return a record from a table where field X = some variable?

If so,...look at the dlookup() function



DLookup Function Example

The following example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1&quot
The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function. Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Microsoft Access will obtain the current value from the control.

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
& Forms!Shippers!ShipperID)
The next example uses a variable, intSearch, to get the value.

Dim intSearch As Integer, varX As Variant
intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & intSearch

 

[chix001]

Thank you for introducing me to the DLookup() function. Late yesterday, my colleague explained to me that I needed to use a recordset and showed me the correct order of my references list in VBA, so I am accomplishing what I need to.

Thanks,
CHIX001