Loop Table Data and pass to a query

[Rob Pacer]

I have a single field table that contains client i.d.'s. I
also have a query that will pull from external data sets to
generate a client-specific report. I am looking for vb
coding that can loop through the client i.d.'s as a
variable and run the report/query for each client on the
list. Currently the query prompts the user to input the
client i.d., and someone will run the report client by
client. I understand how to loop the list, but I do not
know how to use it as criteria in a query. I plan to use a
DoCmd.SendObject after each report completes. My current
version of access is '97.

Thanks,
Rob

 

[Tim Ferguson]

Do While Not rs_Client.EOF ' do while not end of file
varClient = rs_Client.Fields("client_id").value
Docmd.OpenReport "rpt_client", acNormal, varClient

shouldn't this be

DoCmd.OpenReport "rpt_client", acNormal, _
"ClientNumber = " & varClient

in order for Access to know what to do with the client number?

' the 3rd argument is, in essence, a WHERE condition
rs_Client.MoveNext 'move to next record
Loop

B Wishes


Tim F

 

[rob pacer]

Both of these compile well, but the variable is not being
applied in the query. I feel we're getting closer though.
Thanks so far, and please send any other ideas.

 

[Rob Pacer]

I got it! I had to go into the field int he table and use
the "Caption" in the general tab. I used this in place of
where you have ClientNumber. Strange place to pull from
in my opinion - I was using the field name.

Thanks again.

Rob