While Loop

KevW

Registered User.
Local time
Today, 05:44
Joined
Nov 11, 2005
Messages
41
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
 

Users who are viewing this thread

Back
Top Bottom