Function problems

[KevW]

I am having problems getting the function to work correctly. What it should do is create reports for every manager who has staff members in the tbl_Testcheck. It should then open outlook and allow the user to email the report as an attachment, then it should move on to the next manager who has staff and repeat the process. Unfortunately it is now correctly emailing the firsrt manager but does not move to the next. Where am I going wrong it must be something that i am overlooking.


Private Sub Create_Reports()

Dim rstcount As Object
Dim MyDb As Object
Dim strsql As String
Dim ReportManager As Integer
Dim ReportEmail As String
Dim NumManagers As Integer
Dim Numtstchks As Integer
Dim QdfAction As Object

DoCmd.SetWarnings False

Numtstschks = 0

DoCmd.OpenQuery "Qry001_Create_list_of_testcheck_managers", acViewNormal, acReadOnly

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set rstcount = MyDb.OpenRecordset("Qry002_Calculate_number_manager_requireing_tstchk")
rstcount.MoveFirst
NumManagers = rstcount![NUMMANtstchk]
Set rstcount = MyDb.OpenRecordset("tbl_testcheck_managers")
rstcount.MoveFirst

For Numtstchks = 1 To (NumManagers + 1)

ReportManager = rstcount![Manager_ID]
ReportEmail = rstcount![manager_email_add]



strsql = "SELECT tbl_Testcheck.Search_No,tbl_Testcheck.User_Staff_No,tbl_Testcheck.User_Name,tbl_Testcheck.User_Forename,tbl_Testcheck.User_Surname,tbl_Testcheck.Reason,tbl_Testcheck.SQL_SCRIPT,tbl_Testcheck.QUERY_START_DATE,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, tbl_testcheck_managers.Manager_ID INTO tbl_ReportData FROM ((tbl_testcheck_managers INNER JOIN tbl_Manager ON tbl_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.User_Name WHERE (((tbl_testcheck_managers.Manager_ID)=" & ReportManager & "));"

'Msgbox strsql
DoCmd.RunSQL strsql

DoCmd.OpenReport "rpt_Testcheck", acViewPreview

DoCmd.SendObject acSendReport, "rpt_Testcheck", "Snapshot Format", ReportEmail, , , "SPOC Testcheck", "Please find attached SPOC testchecks for your staff members"

DoCmd.Close acReport, "rpt_Testcheck"


DoCmd.DeleteObject acTable, "tbl_ReportData"

If Numtstchks = NumManagers Then

Exit Sub

Else

rstcount.MoveNext


End If

Next Numtstchks



End Sub

 

[KeithG]

You don't have any loops. You need a Do Until Loop in there.

 

[KevW]

Do Until Loop

Keith thanks for your advice, I have only just recently started to code using VBA so have never done A Do Until Loop have you got any pointers as to what you mean and how to implement it.

Thanks

KevW

 

[KeithG]

I can spot quite a few mistakes in your code, can you post your db?

 

[KevW]

Database

KeithG

I have never put a database onto this forum, so I was wondering what do I have to do. I have noticed that a lot of the database already on this site are in a zip format, do they have to be zipped as I don't have a zip programme on my laptop, if it needs to be zipped I will put it on from work tomorrow

 

[KevW]

Database

Keith

Thanks for you advice last night have only just got chance to attach database now.

The all the functions in the module work correctly except the Create Reports function.

Any pointers you can give me would be much appreaciated.

Thanks

Kevin