Need Loop to fill in field used in Query

[JDubP]

I know a bit about VBA in Excel, but not about looping records in Access 2003, so any help will be appreciated. I think if someone can send me some sample code, I can muddle my way through. (or if you can send me alink to read how to do it that is fine also)


I presently run a macro that:
1) runs a query where User has to input a value for field [REP1] and
2) prints/saves a report in snapshot view (i have to manually type in file name)

I have to manually input the values for 20- 30 different Sales Reps [REP1] plus to manually type in the file name to save the report as.

Have a TableREPMASTER that has 1 record for each Sales Rep, field is [REP]

How do I:
1) Run the Query CASH COLLECTIONS ONE REP
using tableCURRENT MONTH CASH COLLECTIONS where field[arcashsman] = the field[REP] from the tableREPMASTER- but loop only each record in table REPMASTER one at a time.

2) Save the Report in c:\Curr Mo Sales Reports\..... as a Snapshot with the value in field [REP] added to the file name, so I know which reprot is for which [REP]

(End purpose is to automate printing a Snapshot report for only those records in table CURRENT MONTH CASH COLLECTIONS that relate to only one Sales Rep)

Thanks

[NeoPa]

This question probably makes sense to you, but I tried to read it a number of times and I keep finding myself up a blind alley. Every explanation seems to refer to more unexplained items so forming a picture of what you're trying to do is complicated to say the least.
If you reword your question more clearly, I will promise at least to look at it again.

NB. Try to avoid explaining one thing by using another thing which is still unexplained.

[JDubP]

Sorry for the confusion.

Basically I want to loop through the table(REPMASTER ) and for each record to run a query and save a report.

As Follows:

1) Use the field[REP1] from table(REPMASTER ) to run a query against table(X) to select only those records from table(X) where [tableX ! REP] =[REPMASTER ! REP1]. (query takes all fields from tableX)


2) Save Report "ZZZ" as a Snapshot
in the location c:/Current Month Sales Reports/
NOTE: add the value from the field [REPMASTER ! REP1]
to the end of the report name when saved)
For example if value is "AG" then save Report "ZZZ"
as "ZZZ AG"

[NeoPa]

This seems clearer. Thank you.
I think I need to answer this in a different order to that in which it was asked. Please bear with me. It should make sense in the end.

1. Create (define) a report which is based on (has a .RecordSource of) tableX.
2. Create a form where this job is triggered from. This form will need some VBA code behind it. The easiest and most straightforward way to do this is to have the code triggered by a CommandButton. We'll call that Me.cmdReports for reference.
3. The code will need to cover the following steps :
   1. Prepare variables to process through the RecordSet of [REPMASTER]. See Basic DAO recordset loop using two recordsets for clues on how the code needs to be written to process through RecordSets.
   2. Loop through for each record in [REPMASTER]
   3. In each iteration of the loop prepare a filter string to match the current value of REPMASTER.REP1.
   4. Open the report with the filter string just prepared.
   5. As the last instruction of the loop export the report to the specified file (Use DoCmd.OutputTo( ) method).

That should get you more than started. Let us know how you get on.