Running same query for each diff value in a table field (loop a recordset?)

[johnnywinter via AccessMonster.com]

I have been told I need to write code for “Looping a recordset†but I am
confused where to start..

I want to replace having to manually input different variables for the same
field [REP] 30- 40 times and run the same query 30-40 times to run the same
report for each salesperson [REP] and to send 30-40 different emails.

I have a query “BYREP†that uses two tables, linked by the field [REP]
Table 1 “CASH COLLECTIONS†(each record is one cash receipt)
Table 2 “REPMASTER†(one record for each salesperson )



Here is what I do manually- 30-40 times and input the value for [REP] each
time:
1) RUN Query name: “BYREP- which query contains:
all fields from table “CASH COLLECTIONSâ€
and 3 fields from table “REPMASTERâ€
field [REP]
field: [repName]
field: [repEmailAddress]

2) OPEN REPORT "CURRENT MONTH CASH COLLECTIONS BY REPâ€- as a pdf file
I use Cute Pdf Creator to print (save) this as a pdf file.
All PDF files are saved in same directory: “c:/Current Month
Collections/[REP] cash
collections.pdfâ€
(each different pdf file is named with the value of the field [Rep]
used in each diff query)

3) Email each salesperson [REP] to the email address in the field
[repEmailAddress]
and attach the applicable pdf file that matches that [REP].

NOTE: I can use reports saved as Access Snapshot files rather than pdf files
if necessary,

 

[raskew via AccessMonster.com]

Hi -

I'm confused. What are you manually inputting? Isn't the value, or sum of
values contained
in CashCollections, as well the rep ID for each receipt?

Please clarify and please post your query SQL.

Bob

I have been told I need to write code for “Looping a recordset†but I am
confused where to start..

I want to replace having to manually input different variables for the same
field [REP] 30- 40 times and run the same query 30-40 times to run the same
report for each salesperson [REP] and to send 30-40 different emails.

I have a query “BYREP†that uses two tables, linked by the field [REP]
Table 1 “CASH COLLECTIONS†(each record is one cash receipt)
Table 2 “REPMASTER†(one record for each salesperson )

Here is what I do manually- 30-40 times and input the value for [REP] each
time:
1) RUN Query name: “BYREP- which query contains:
all fields from table “CASH COLLECTIONSâ€
and 3 fields from table “REPMASTERâ€
field [REP]
field: [repName]
field: [repEmailAddress]

2) OPEN REPORT "CURRENT MONTH CASH COLLECTIONS BY REPâ€- as a pdf file
I use Cute Pdf Creator to print (save) this as a pdf file.
All PDF files are saved in same directory: “c:/Current Month
Collections/[REP] cash
collections.pdfâ€
(each different pdf file is named with the value of the field [Rep]
used in each diff query)

3) Email each salesperson [REP] to the email address in the field
[repEmailAddress]
and attach the applicable pdf file that matches that [REP].

NOTE: I can use reports saved as Access Snapshot files rather than pdf files
if necessary,

 

[johnnywinter via AccessMonster.com]

Hi -

I'm confused. What are you manually inputting? Isn't the value, or sum of
values contained
in CashCollections, as well the rep ID for each receipt?

Please clarify and please post your query SQL.

Bob

I am inputting the value for [REP] when I run the Query and print the report
as a pdf.
(query asks for a value for [REP] tos elect off only thsoe
records for that [REP].

Then I input the next value for [REP] and run the Query and print the report
as a pdf ---- and continue to do this until I have input all the values for
[REP] that are in the table "RepMaster"

 

[raskew via AccessMonster.com]

Hate to say it, but you're spinning your wheels. You've got rep information
in one table and you've got value information in another table. It's just a
matter of bring them together.

Here's a sample make-table query (you can revert it to a Select query if more
desireable) based on Northwind's Orders and Employees. It creates tblTest
and populates it with the sum of [freight] charges for each employee, during
a specified time period. There's nothing manual about it, other than
invoking the query--just once.

******************************************************
SELECT
Employees.EmployeeID
, [LastName] & ", " & [FirstName] AS Employee
, Sum(Orders.Freight) AS SumOfFreight INTO tblTest
FROM
Employees
INNER JOIN
Orders
ON
Employees.EmployeeID = Orders.EmployeeID
WHERE
(((Orders.OrderDate) Between #8/1/1995#
AND
#12/31/1995#))
GROUP BY
Employees.EmployeeID
, [LastName] & ", " & [FirstName]
ORDER BY
[LastName] & ", " & [FirstName];
******************************************************

To see it in action, just copy/paste the above SQL to a new query in
Northwind. Be aware that, as written, it's going to create tblTest.

It sounds as if you need something similar.

Please post back.

Hi -

[quoted text clipped - 5 lines]

Bob

I am inputting the value for [REP] when I run the Query and print the report
as a pdf.
(query asks for a value for [REP] tos elect off only thsoe
records for that [REP].

Then I input the next value for [REP] and run the Query and print the report
as a pdf ---- and continue to do this until I have input all the values for
[REP] that are in the table "RepMaster"

 

[johnnywinter via AccessMonster.com]

Guess I am not explaining it well.

My query "BY REP" already selects off the records from table "Current Month
Cash Collections" for only one [REP] but query asks me to input the value
for [REP] to use in the query.

Report "CASH COLLECTIONS BY REP" uses the query "BY REP" as its source.

So to create a report for each value fof [REP] that is in the table
"REPMASTER" I now run the Query and print the report 30-40 times.

I was told I can use VBA code to loop through each record in the table
"REPMASTER" and use each record to run the Query & report.

Hate to say it, but you're spinning your wheels. You've got rep information
in one table and you've got value information in another table. It's just a
matter of bring them together.

Here's a sample make-table query (you can revert it to a Select query if more
desireable) based on Northwind's Orders and Employees. It creates tblTest
and populates it with the sum of [freight] charges for each employee, during
a specified time period. There's nothing manual about it, other than
invoking the query--just once.

******************************************************
SELECT
Employees.EmployeeID
, [LastName] & ", " & [FirstName] AS Employee
, Sum(Orders.Freight) AS SumOfFreight INTO tblTest
FROM
Employees
INNER JOIN
Orders
ON
Employees.EmployeeID = Orders.EmployeeID
WHERE
(((Orders.OrderDate) Between #8/1/1995#
AND
#12/31/1995#))
GROUP BY
Employees.EmployeeID
, [LastName] & ", " & [FirstName]
ORDER BY
[LastName] & ", " & [FirstName];
******************************************************

To see it in action, just copy/paste the above SQL to a new query in
Northwind. Be aware that, as written, it's going to create tblTest.

It sounds as if you need something similar.

Please post back.[quoted text clipped - 10 lines]

as a pdf ---- and continue to do this until I have input all the values for
[REP] that are in the table "RepMaster"

 

[raskew via AccessMonster.com]

Is the value you're asked to input somehow different than that from
[CurrentMonth...]?
If so, where do you get that value?

Please post your query SQL.

Bob

Guess I am not explaining it well.

My query "BY REP" already selects off the records from table "Current Month
Cash Collections" for only one [REP] but query asks me to input the value
for [REP] to use in the query.

Report "CASH COLLECTIONS BY REP" uses the query "BY REP" as its source.

So to create a report for each value fof [REP] that is in the table
"REPMASTER" I now run the Query and print the report 30-40 times.

I was told I can use VBA code to loop through each record in the table
"REPMASTER" and use each record to run the Query & report.

Hate to say it, but you're spinning your wheels. You've got rep information
in one table and you've got value information in another table. It's just a

[quoted text clipped - 39 lines]

as a pdf ---- and continue to do this until I have input all the values for
[REP] that are in the table "RepMaster"

 

[pietlinden]

Guess I am not explaining it well.

My query "BY REP" already selects off the records from table "Current Month
Cash Collections" for only one [REP] but query asks me to input the value
for [REP] to use in the query.

Report "CASH COLLECTIONS BY REP" uses the query "BY REP" as its source.

So to create a report for each value fof [REP] that is in the table
"REPMASTER" I now run the Query and print the report 30-40 times.

I was told I can use VBA code to loop through each record in the table
"REPMASTER" and use each record to run the Query & report.

Sounds like you're making this way harder than need be. First off,
remove the parameters from the underlying query for your report.
Then just pass a valid where clause when you open the report.

Something like this untested aircode...
1. open a recordset based on your table REPMASTER, so you can get all
the RepID's
2. pass them one at a time to the report when you open it in the Where
clause.
3. print the report to PDF or whatever.
4. close the report
5. go to the next RepID in your recordset (continue until you reach
the end of the list).


Dim rsRep as DAO.Recordset
set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER",dbOpenTable,
dbForwardOnly)
do until rsRep.EOF
DoCmd.OpenReport "Cash Collections By Rep",,"[Rep]=' " &
rsRep.Fields("RepName") & " ' "
DoCmd.PrintOut...
DoCmd.Close acReport, "Cash Collections By Rep",acSaveNo
rsRep.MoveNext
Loop

rsRep.close
set rsrep=nothing

 

[raskew via AccessMonster.com]

I was getting to that, very slowly.

Bob

Guess I am not explaining it well.

[quoted text clipped - 9 lines]

I was told I can use VBA code to loop through each record in the table
"REPMASTER" and use each record to run the Query & report.

Sounds like you're making this way harder than need be. First off,
remove the parameters from the underlying query for your report.
Then just pass a valid where clause when you open the report.

Something like this untested aircode...
1. open a recordset based on your table REPMASTER, so you can get all
the RepID's
2. pass them one at a time to the report when you open it in the Where
clause.
3. print the report to PDF or whatever.
4. close the report
5. go to the next RepID in your recordset (continue until you reach
the end of the list).

Dim rsRep as DAO.Recordset
set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER",dbOpenTable,
dbForwardOnly)
do until rsRep.EOF
DoCmd.OpenReport "Cash Collections By Rep",,"[Rep]=' " &
rsRep.Fields("RepName") & " ' "
DoCmd.PrintOut...
DoCmd.Close acReport, "Cash Collections By Rep",acSaveNo
rsRep.MoveNext
Loop

rsRep.close
set rsrep=nothing

 

[raskew via AccessMonster.com]

Re: emailing multiple reports. Take a look at this:
http://granite.ab.ca/access/email/reporttomultiplerecipients.htm

Bob

I was getting to that, very slowly.

Bob

On Aug 3, 8:25 pm, "johnnywinter via AccessMonster.com" <u41874@uwe>
wrote:

[quoted text clipped - 31 lines]

rsRep.close
set rsrep=nothing

 

[johnnywinter via AccessMonster.com]

I used the code you provided --

It does not select off records but rather prints the report that includes all
records from table CURR MO COLLECTIONS BY REP"

It is looping because it Prints separate full reports until it has looped
through all the records in table"REPMASTER" . So with 38 records in
REPMASTER I am getting 38 full reports.
(when I try to save as a SNP file, it saves a file with all record 38 times
using the same file name)

Any suggestions?

thanks

Here's my code. I hope I wrapped it correctly.

Private Sub Command2_Click()

Dim rsRep As DAO.Recordset
Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)

Do Until rsRep.EOF
DoCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, "rsRepFields.
[REP] ='"""


'DoCmd.OutputTo acOutputReport, "Curr Mo Collections By Rep", acFormatSNP, _
' "C:\Curr Mo Cash Rep\" & "Cash Collections & _ [REP] & .snp", False


DoCmd.Close acReport, "Curr Mo Collections By Rep", acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing



End Sub

Guess I am not explaining it well.

[quoted text clipped - 9 lines]

I was told I can use VBA code to loop through each record in the table
"REPMASTER" and use each record to run the Query & report.

Sounds like you're making this way harder than need be. First off,
remove the parameters from the underlying query for your report.
Then just pass a valid where clause when you open the report.

Something like this untested aircode...
1. open a recordset based on your table REPMASTER, so you can get all
the RepID's
2. pass them one at a time to the report when you open it in the Where
clause.
3. print the report to PDF or whatever.
4. close the report
5. go to the next RepID in your recordset (continue until you reach
the end of the list).

Dim rsRep as DAO.Recordset
set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER",dbOpenTable,
dbForwardOnly)
do until rsRep.EOF
DoCmd.OpenReport "Cash Collections By Rep",,"[Rep]=' " &
rsRep.Fields("RepName") & " ' "
DoCmd.PrintOut...
DoCmd.Close acReport, "Cash Collections By Rep",acSaveNo
rsRep.MoveNext
Loop

rsRep.close
set rsrep=nothing

 

[raskew via AccessMonster.com]

See the previous post re setting filters.

Bob

I used the code you provided --

It does not select off records but rather prints the report that includes all
records from table CURR MO COLLECTIONS BY REP"

It is looping because it Prints separate full reports until it has looped
through all the records in table"REPMASTER" . So with 38 records in
REPMASTER I am getting 38 full reports.
(when I try to save as a SNP file, it saves a file with all record 38 times
using the same file name)

Any suggestions?

thanks

Here's my code. I hope I wrapped it correctly.

Private Sub Command2_Click()

Dim rsRep As DAO.Recordset
Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)

Do Until rsRep.EOF
DoCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, "rsRepFields.
[REP] ='"""

'DoCmd.OutputTo acOutputReport, "Curr Mo Collections By Rep", acFormatSNP, _
' "C:\Curr Mo Cash Rep\" & "Cash Collections & _ [REP] & .snp", False


DoCmd.Close acReport, "Curr Mo Collections By Rep", acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing

End Sub

On Aug 3, 8:25 pm, "johnnywinter via AccessMonster.com" <u41874@uwe>
wrote:

[quoted text clipped - 31 lines]

rsRep.close
set rsrep=nothing

 

[johnnywinter via AccessMonster.com]

I could not get it to work.

Now it does not include any records in the reports -- and loops through all
records in the recordset and prints a report each time with no data.


What am I doing wrong?

I left the previous VBA code intact and entered the following code by
opening the report in design mode and in properties selected On Open - and
input this VBA code

Option Compare Database
Private Sub Report_Open(Cancel As Integer)

Me.Filter = "Curr Mo Collections By Rep!REP" = " '""" & "rs.[REP] =" '"""
Me.FilterOn = True

End Sub

See the previous post re setting filters.

Bob

I used the code you provided --

[quoted text clipped - 43 lines]