Code Problems With Variables

[bidbud68]

Hi I have a code that works and I am trying to adapt it for another report bit for some reason I am getting Variable Undefined, is there anything you can see that could be incorrect ?

Option Compare Database
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Global Const JetDateTimeFmt = "\#dd\/mm\/yy hh\:nn\:ss\#;;;\N\u\l\l"

   Function Send_Welcome_Letters()
   
   Dim dbsReservations As DAO.Database
   Dim rstWelcome As DAO.Recordset
   Dim strSQL As String
   Dim strEmailRecipient As String
   
   'On Error GoTo ErrorHandler
   
   Set dbsReservations = CurrentDb
   
   strSQL = "SELECT Reservations.ReservationID, Reservations.UnitDescription, Reservations.[Date IN], Reservations.[Date OUT], Reservations.Tenant, Reservations.TenantEmail AS Email, Reservations.SalesPerson, Reservations.[SalesPerson Email] AS Email1 From Reservations WHERE (((Reservations.[Date IN])=Date()+1));"
   Set rstWelcome = dbsReservations.OpenRecordset(strSQL, dbOpenDynaset)
   
   With rstWelcome
     Do Until .EOF
         DoCmd.OpenReport "Welcome Letter", acViewNormal, , "Reservations.ReservationID=" & rstWelcome![Reservations.ReservationID]
         Sleep (20000)
         Call SendWelcomeLetter(rstWelcome![SalesPerson Email], rstWelcome!EmailAddress)
         .MoveNext
     Loop
   End With

   rstWelcome.Close
   dbsReservations.Close
   
   Set rstInvoices = Nothing
   Set dbsReservations = Nothing

   Exit Function

   'ErrorHandler:
   '  MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

   End Function

[pbaldy]

On what line? Do the functions being called exist?

[bidbud68]

It says Item Not Found In This Collection and it highlights the following line :

DoCmd.OpenReport "Welcome Letter", acViewNormal, , "Reservations.ReservationID=" & rstWelcome![Reservations.ReservationID]

[Missinglinq]

Well, you've got this line


Set rstInvoices = Nothing

and I don't see where rstInvoices has been declared.

Linq ;0)>

[pbaldy]

Try

DoCmd.OpenReport "Welcome Letter", acViewNormal, , "ReservationID=" & rstWelcome![ReservationID]

and be sure the report exists.

[bidbud68]

Thanks so much, it works and there was also that one incorrect reference which I fixed

[pbaldy]

Happy to help.