I am trying to write a while loop that will e mail a report to each individual manager within a query. So far I can import a file into a table called tblTestcheck. I can then get it to run a query that creates a list of the manager who will need emailing and finally run a query that creates the report that I wish to send by emai. So in my module I want a while loop that will produce the report for each manager. How do I go about creating the while loop.
I am using Access 2000 and my module is below
Option Compare Database
Private Sub import_testcheck_file()
End Sub
Private Sub run_testmanagerqry()
DoCmd.OpenQuery "Qry01_Create_list_of_testcheck_managers", acViewNormal, acReadOnly
End Sub
Private Sub run_manager_testcheck_production()
Dim rstcount As Object
Dim MyDB As Object
Dim ReportManager As String
Dim ReportEmail As String
End Sub
Private Sub tim_test()
Dim rstcount As Object
Dim MyDB As Object
Dim ReportManager As String
Dim ReportEmail As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rstcount = MyDB.openrecordset("Qry01_Create_list_of_testcheck_managers")
rstcount.MoveFirst
'while not greater than last record do
ReportManager = rstcount![manager_ID]
ReportEmail = rstcount![Manager_email_add]
'"SELECT tbl_Testcheck.Testcheck_ID, tbl_Testcheck.Tstchk_Username, tbl_Testcheck.Tstchk_Forename, tbl_Testcheck.Tstchk_Surname, tbl_Testcheck.Tstchk_SQL, tbl_Testcheck.Tstchk_Date, tbl_Testcheck.Tstchk_time, tbl_User.User_Location, tbl_User.User_extn, tbl_Manager.Manager_Forename, tbl_Manager.Manager_Surname, tbl_Manager.Manager_Location, tbl_Manager.Manager_extn, tbl_Manager.Manager_email_add, testcheck_managers.Manager_ID INTO tbl_ReportData
'FROM ((testcheck_managers INNER JOIN tbl_Manager ON testcheck_managers.Manager_ID = tbl_Manager.Manager_ID) INNER JOIN tbl_User ON tbl_Manager.Manager_ID = tbl_User.User_Manager_ID) INNER JOIN tbl_Testcheck ON tbl_User.User_Name = tbl_Testcheck.Tstchk_Username
'WHERE (((testcheck_managers.Manager_ID)=" & reportmanager & "));"
DoCmd.OpenReport "rpt_Testcheck", acViewPreview
'open an email where to address = ReportEmail
'Attach Rpt_testcheck to email and send
'delete tbl_report_data
rstcount.MoveNext
'end while
End Sub
I am using Access 2000 and my module is below
Option Compare Database
Private Sub import_testcheck_file()
End Sub
Private Sub run_testmanagerqry()
DoCmd.OpenQuery "Qry01_Create_list_of_testcheck_managers", acViewNormal, acReadOnly
End Sub
Private Sub run_manager_testcheck_production()
Dim rstcount As Object
Dim MyDB As Object
Dim ReportManager As String
Dim ReportEmail As String
End Sub
Private Sub tim_test()
Dim rstcount As Object
Dim MyDB As Object
Dim ReportManager As String
Dim ReportEmail As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rstcount = MyDB.openrecordset("Qry01_Create_list_of_testcheck_managers")
rstcount.MoveFirst
'while not greater than last record do
ReportManager = rstcount![manager_ID]
ReportEmail = rstcount![Manager_email_add]
'"SELECT tbl_Testcheck.Testcheck_ID, tbl_Testcheck.Tstchk_Username, tbl_Testcheck.Tstchk_Forename, tbl_Testcheck.Tstchk_Surname, tbl_Testcheck.Tstchk_SQL, tbl_Testcheck.Tstchk_Date, tbl_Testcheck.Tstchk_time, tbl_User.User_Location, tbl_User.User_extn, tbl_Manager.Manager_Forename, tbl_Manager.Manager_Surname, tbl_Manager.Manager_Location, tbl_Manager.Manager_extn, tbl_Manager.Manager_email_add, testcheck_managers.Manager_ID INTO tbl_ReportData
'FROM ((testcheck_managers INNER JOIN tbl_Manager ON testcheck_managers.Manager_ID = tbl_Manager.Manager_ID) INNER JOIN tbl_User ON tbl_Manager.Manager_ID = tbl_User.User_Manager_ID) INNER JOIN tbl_Testcheck ON tbl_User.User_Name = tbl_Testcheck.Tstchk_Username
'WHERE (((testcheck_managers.Manager_ID)=" & reportmanager & "));"
DoCmd.OpenReport "rpt_Testcheck", acViewPreview
'open an email where to address = ReportEmail
'Attach Rpt_testcheck to email and send
'delete tbl_report_data
rstcount.MoveNext
'end while
End Sub