Do While Loop

[KevW]

I was advised the other night a create reports function that I could not get working required a Do While Loop. I found the code for a Do While Loop and have amended my code accordingly but I am still getting errors is there and error in this loop

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

Do

Do While Numtstchks < (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 & "));"


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 Do

Else

rstcount.MoveNext


End If

Loop

Loop Until NumManagers = Numtstchks



End Sub

 

[KeithG]

what error are you receiving?

 

[KevW]

Error message

Unfortunately the error message was happening on a a works PC and the same error does not occur on my own laptop.

The when the error occured and I debugged the following line was causing the problem

NumManagers = rstcount![NUMMANtstchk]

When i display this line the following message is shown

rstcount![NUMMANtstchck]=<item not found in this collections>

Any ideas as to why.

Also did you get to view the database that I but on the original thread in regards to this Create Reports function because you mentioned there were other errors in my code.

 

[KeithG]

Try using

rscount.Fields("NUMMANtstchck")

 

[KevW]

Keith

Now I get runtime error "424" object required

 

[KeithG]

Use Dim rscount as DAO.recordset instead of Dim rscount as Object

 

[KevW]

Keith still getting the same mesage. Will try to sort it tomorrow. Did you look at the database, could the problems be related to something else I have overlooked in the table design or in the queries.