Need to loop all records in a table - run query, print & email reports

[jimpaige via AccessMonster.com]

I think I need to write code for a LOOP I want to replace having to run a
macro and manually input different variables 30- 40 times.

Any help or direction would save me hours each month since I have 30- 40
salespeople, and the number varies, and I do similar things for other reports.
I now have to run the query 30- 40 times (once for each sales person)


I have a query that uses two tables, linked by the field [REP]

Table name: INQUIRIES (each record is one inquiry - a sales prospect)
Table name: REPMASTER (one record for each salesman “repâ€)

Query name: BYREPS- contains all fields from table INQUIRIES and 3 fields
from
table REPMASTER

Report Name: NEW INQUIRIES

Email Address: field [repEMAIL] in table REPMASTER


I run a macro REPORTS BY REP that runs the select query BYREP, I input one
variable for [REP] (before I run the macro, I edit the Send Object line to
put in the correct email address that corresponds to the [REP] for whom I am
running the query/report (email address is a field in table REPMASTER..

Based on the query BYREPS, macro REPORTS BY REP then:
• prints a report NEW INQUIRIES of all records in the query
• prints a report EACH NEW INQUIRY- 1 page for each record in query BYREPS
• email the report NEW INQUIRIES and report EACH NEW INQUIRY as snapshot
files to an email address in [repEMAIL] field in the table REPMASTER (I have
to input each email address)

If I can get this done automatically in a Loop or ?, then I can move to
Mail Merge to send a personalized letter to each inquiry (each record in
query BY REP)

 

[Guest]

If I understand your post correctly then you might be looking for something
like this:
Simply copy and past this into a new module and tweak it as needed:
This example of looping would pull all of the "Reps" with new inquiries
within the past 30 days and then alter the sql behind your query.
Then execute your macro that prints your reports and then pass
the email address to your email code module to complete the process.
'------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub LoopReps()

Dim daoDbs As DAO.Database
Dim daoQdf As DAO.QueryDef
Dim daoRec As DAO.Recordset
Dim strSql As String

Set daoDbs = CodeDb
Set daoQdf = daoDbs.QueryDefs("BYREPS")

strSql = _
"SELECT " & _
"REPMASTER.REP, " & _
"REPMASTER.RepEmail " & _
"FROM INQUIRIES " & _
"INNER JOIN REPMASTER " & _
"ON INQUIRIES.REP = REPMASTER.REP " & _
"WHERE ((INQUIRIES.InquiryDate) > DATE()-30) " & _
"AND REPMASTER.REP = '' " & _
"ORDER BY REPMASTER.REP;"

Set daoRec = daoDbs.OpenRecordset(strSql)

If Not (daoRec.BOF And daoRec.EOF) Then
daoRec.MoveFirst
Do While Not daoRec.EOF
strSql = _
"SELECT " & _
"REPMASTER.REP, " & _
"REPMASTER.Field1, " & _
"REPMASTER.Field2, " & _
"REPMASTER.Field3, " & _
"INQUIRIES.FieldA, " & _
"INQUIRIES.FieldB, " & _
"INQUIRIES.FieldC, " & _
"INQUIRIES.InquiryDate " & _
"FROM INQUIRIES " & _
"INNER JOIN REPMASTER " & _
"ON INQUIRIES.REP = REPMASTER.REP " & _
"WHERE ((INQUIRIES.InquiryDate) > DATE()-30) " & _
"AND REPMASTER.REP = '" & daoRec("REP").Value & "' " & _
"ORDER BY REPMASTER.REP;"
daoQdf.SQL = strSql
DoCmd.RunMacro "REPORTS BY REP"
Call SendEmail(daoRec("RepEmail").Value)
daoRec.MoveNext
Loop
Else
MsgBox _
"Sorry No Records Match " & _
"Your Criteria Today.", _
vbInformation, "No Records Found:"
End If

strSql = ""
daoRec.Close
daoDbs.Close
Set daoRec = Nothing
Set daoDbs = Nothing

End Sub

Private Sub SendEmail(strEmail As String)

'Insert your email code here to send the snapshots to strEmail
'If you do not already have an email module created try using CDO

End Sub
'------------------------------------------------------------------------------------------
'------------------------------------------------------------------------------------------

-- Take Care & God Bless ~ SPARKER ~

I think I need to write code for a LOOP I want to replace having to run a
macro and manually input different variables 30- 40 times.

Any help or direction would save me hours each month since I have 30- 40
salespeople, and the number varies, and I do similar things for other reports.
I now have to run the query 30- 40 times (once for each sales person)


I have a query that uses two tables, linked by the field [REP]

Table name: INQUIRIES (each record is one inquiry - a sales prospect)
Table name: REPMASTER (one record for each salesman “repâ€)

Query name: BYREPS- contains all fields from table INQUIRIES and 3 fields
from
table REPMASTER

Report Name: NEW INQUIRIES

Email Address: field [repEMAIL] in table REPMASTER


I run a macro REPORTS BY REP that runs the select query BYREP, I input one
variable for [REP] (before I run the macro, I edit the Send Object line to
put in the correct email address that corresponds to the [REP] for whom I am
running the query/report (email address is a field in table REPMASTER..

Based on the query BYREPS, macro REPORTS BY REP then:
• prints a report NEW INQUIRIES of all records in the query
• prints a report EACH NEW INQUIRY- 1 page for each record in query BYREPS
• email the report NEW INQUIRIES and report EACH NEW INQUIRY as snapshot
files to an email address in [repEMAIL] field in the table REPMASTER (I have
to input each email address)

If I can get this done automatically in a Loop or ?, then I can move to
Mail Merge to send a personalized letter to each inquiry (each record in
query BY REP)