DoCmd.OpenReport WHERE condition

**bidbud68** · 10-16-2012, 11:06 AM

Hi I have a Function which scrolls through my database forms Edit Reservations and looks for records which match the date entered into variable rdate. Once found its supposed to open the report "Invoices For Month End Emailing" and pass the rdate1 variable and the ReservationID variable in a WHERE clause. I can get all to work up to the point of 1 condition ie look for the Reservation ID's however it will not pass the rdate1 variable into the report. The report still asks for that variable to be typed in each time. I looked into Public variables and seperate functions etc but nothing works with 2 variables. Please help if you can :

Code:

Option Compare Database

   
   Function Send_Monthly_Invoices()
   
   Dim r As DAO.Recordset
   Dim rdate1 As Variant
   Dim rdate As Date
   Dim strCondition1 As Variant
   Dim strCondition2 As Variant
   Dim strCondition3 As Variant
     
   rdate = InputBox("Enter Date")
   
   DoCmd.OpenForm "Edit Reservations", acNormal, "", "", , acNormal
   Set r = Forms![Edit Reservations].RecordsetClone
   
   r.MoveFirst
   If r.RecordCount > 0 Then
     Do While Not r.EOF
       Set rdate1 = Forms![Edit Reservations]![Accounts subform]![Date]
       Set strCondition1 = Forms![Edit Reservations]![ReservationID] = Reports![Invoices For Month End Emailing]![Reservations.ReservationID]
       Set strCondition2 = Forms![Edit Reservations]![Accounts subform]![Date] = rdate1
       Set strCondition3 = Forms![Edit Reservations]![ReservationID] = Reports![Reservations Letters]![Reservations.ReservationID]
        
       If rdate = rdate1 Then
         DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , strCondition1 And strCondition2
         DoCmd.OpenReport "Booking Confirmation", acViewNormal, , strCondition3
         Beep
         MsgBox "Please Wait For PDF To Print Then Click Here", vbInformation, ""
         DoCmd.OpenReport "Send Invoice Email", acViewNormal, "", "", acNormal
       End If
       DoCmd.GoToRecord , , acNext
    Loop
   End If

    
Send_Monthly_Invoices_Exit:
    Exit Function

Send_Monthly_Invoices_Err:
    MsgBox Error$
    Resume Send_Monthly_Invoices_Exit

End Function

**June7** · 10-16-2012, 11:29 AM

Your strConditionX variables are set to a Yes/No value because that is what the expressions produce. Is that what you want?

If the intent is to open a report, how can the code refer to a value on that report?

Typical WHERE argument for the OpenForm/OpenReport method:

DoCmd.OpenReport "reportname", , , "ID=" & Me.IDtextbox

So:
strCondition3 = "[ReservationID]=" & Forms![Edit Reservations]![ReservationID]
DoCmd.OpenReport "Send Invoice Email", acViewNormal, , strCondition3

Why a Function - it does not return anything to the calling procedure. This could be a Sub.

**bidbud68** · 10-16-2012, 01:13 PM

Thanks for the reply.

Please let me know how I would refer to strCondition1 and strCondition2 in a DoCmd as that seems to be the problem.

Would you recommend a sub rather than a function ?

**June7** · 10-16-2012, 02:23 PM

Declaring procedure as Function will work. It will run like a Sub because it doesn't return a value to the calling procedure.

Populate the other variables the same way. Concatenate variables and text, literal text within quotes.

strCondition1 = "[ReservationID] = " & Forms![Edit Reservations]![ReservationID]
strCondition2 = "[Date] = #" & rdate1 & "#"

DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , strCondition1 & " AND " & strCondition2

**bidbud68** · 10-16-2012, 03:44 PM

Thanks the procedure runs now without prompts for the date input which is great and the report Booking Confirmation is perfect.

I think I need to write a loop though for the Invoices For Month End Emailing report as the Forms![Edit Reservations]![Accounts subform] has quite a few dates which I need to run through to pick up the rdate1 variable in each line and test it against rdate before moving onto the the next client's record (as its only picking it up if its the first line of that clients invoice). How would I run through that I don't think the same as the Do While Not r.EOF with a GoToRecord,,acNext ? Also the ones it does pick up it prints a blank Invoices For Month End Emailing report however the Booking Confirmation is correctly printed/emailed so not sure what is wrong there ?

Code:

Option Compare Database

   
   Function Send_Monthly_Invoices()
   
   Dim r As DAO.Recordset
   Dim rdate1 As Variant
   Dim rdate As Date
   Dim strCondition1 As Variant
   Dim strCondition2 As Variant
   Dim strCondition3 As Variant
     
   rdate = InputBox("Enter Date")
   
   DoCmd.OpenForm "Edit Reservations", acNormal
   Set r = Forms![Edit Reservations].RecordsetClone
   
   r.MoveFirst
   If r.RecordCount > 0 Then
     Do While Not r.EOF
       Set rdate1 = Forms![Edit Reservations]![Accounts subform]![Date]
       strCondition1 = "[Accounts.ReservationID] = " & Forms![Edit Reservations]![Reservations.ReservationID]
       strCondition2 = "[Date] = #" & rdate1 & "#"
       strCondition3 = "[Reservations.ReservationID] = " & Forms![Edit Reservations]![Reservations.ReservationID]
        
       If rdate = rdate1 Then
         DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , strCondition1 & " AND " & strCondition2
         DoCmd.OpenReport "Booking Confirmation", acViewNormal, , strCondition3
         Beep
         MsgBox "Please Wait For PDF To Print Then Click Here", vbInformation, ""
         DoCmd.OpenReport "Send Invoice Email", acViewNormal, "", "", acNormal
       End If
       DoCmd.GoToRecord , , acNext
    Loop
   End If

    
Send_Monthly_Invoices_Exit:
    Exit Function

Send_Monthly_Invoices_Err:
    MsgBox Error$
    Resume Send_Monthly_Invoices_Exit

End Function

**June7** · 10-16-2012, 06:57 PM

I don't understand what you are trying to do by opening a RecordsetClone. The loop code moves to next record on the form, not the RecordsetClone. The code just sits on the first record of RecordsetClone which should cause an endless loop because never get to end of the recordset but the GoToRecord method will keep advancing the form records until you encounter new record row. r.MoveNext would move to next record of recordset.

**bidbud68** · 10-17-2012, 12:50 AM

Hi thanks I have rewritten the code without the RecordsetClone etc as you suggested and tried to filter the report when opening the Recordset by date. I now have 2 problems :

1) The error is "Too few parameters" due to some formatting issue with the rdate variable as if I type in the date manually into the code its fine
2) I am not sure how to send off each invoice of the report to the client seperately, what condition would I use to scroll through and send off individually in the Do Until loop. ?

Thanks for your help.

**bidbud68** · 10-17-2012, 12:52 AM

Code:

Option Compare Database

   Sub Send_Monthly_Invoices()
   
   Dim dbsReservations As DAO.Database
   Dim rstInvoices As DAO.Recordset
   Dim strSQL As String
   Dim rdate As Date
   Dim strCondition1 As Variant

   'On Error GoTo ErrorHandler
   
   Set dbsReservations = CurrentDb
   
   rdate = InputBox("Enter Date")
   
   strCondition1 = "#" & rdate & "#"
   
   strSQL = "SELECT * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE ((Accounts.Date)=strCondition1) ORDER BY Accounts.Date;"
   Set rstInvoices = dbsReservations.OpenRecordset(strSQL, dbOpenDynaset)
   
   With rstInvoices
     .MoveFirst
     Do Until .EOF
         DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal
         DoCmd.OpenReport "Booking Confirmation", acViewNormal
         Beep
         MsgBox "Please Wait For PDF To Print Then Click Here", vbInformation, ""
         DoCmd.OpenReport "Send Invoice Email", acViewNormal, "", "", acNormal
         .MoveNext
     Loop
   End With

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

   Exit Sub

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

   End Sub

**June7** · 10-17-2012, 01:24 AM

Not knowing your db, my guess is something like:

DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , "CustomerID=" & rstInvoices!CustomerID

Your query doesn't work because need to concatenate the strCondition1 variable. If you don't concatenate variable, the sql statement will have the literal text string 'strCondition1' instead of the date value that the variable is set to.

strSQL = "SELECT * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE ((Accounts.Date)=" & strCondition1 & ") ORDER BY Accounts.Date;"

**bidbud68** · 10-17-2012, 02:57 AM

Hi thank you.

The below code works when I manually add the date :

Code:

strCondition1 = "#" & rdate & "#"
   
   strSQL = "SELECT Reservations.ReservationID, Accounts.Item, * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE ((Accounts.Date)=#19/09/2012#) ORDER BY Accounts.Date;"

However when I use the code suggested below the code doesnt continue to run :

Code:

   strCondition1 = "#" & rdate & "#"
   
   strSQL = "SELECT Reservations.ReservationID, Accounts.Item, * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE ((Accounts.Date)=" & strCondition1 & ") ORDER BY Accounts.Date;"

Please advise what could be the problem

**pbaldy** · 10-17-2012, 09:29 AM

See if this helps:

http://access.mvps.org/access/datetime/date0005.htm

**June7** · 10-17-2012, 12:07 PM

I will have to analyse db. If you want to provide, follow instructions at bottom of my post.

**bidbud68** · 10-18-2012, 05:48 AM

Thanks so much, here is my latest code, I cant seem to open the copy of the database I make due to security passwords etc.

Code:

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"

   Sub Send_Monthly_Invoices()
   
   Dim dbsReservations As DAO.Database
   Dim rstInvoices As DAO.Recordset
   Dim strSQL As String
   Dim rdate As Date
   Dim strCondition1 As String
   Dim strEmailRecipient As String
   
   'On Error GoTo ErrorHandler
   
   Set dbsReservations = CurrentDb
   
   rdate = InputBox("Enter Date")
   
   strCondition1 = "#" & rdate & "#"
   
   strSQL = "SELECT Reservations.ReservationID, Accounts.Item, Customers.EmailAddress, * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE ((Accounts.Date)=" & strCondition1 & ") ORDER BY Accounts.Date;"
   Set rstInvoices = dbsReservations.OpenRecordset(strSQL, dbOpenDynaset)
   
   With rstInvoices
     Do Until .EOF
         DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID]
         DoCmd.OpenReport "Booking Confirmation", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID]
         Sleep (10000)
         Call SendInvoiceEmail(rstInvoices![EmailAddress])
         .MoveNext
     Loop
   End With

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

   Exit Sub

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

   End Sub

**June7** · 10-18-2012, 12:40 PM

Exactly what is the issue now that you want help with?

**bidbud68** · 10-18-2012, 02:12 PM

Basically I still have the same problem with dates (maybe a formatting issue Global Const JetDateTimeFmt = "\#dd\/mm\/yy hh\:nn\:ss\#;;;\N\u\l\l" ?)

In the code below the line with ((Accounts.Date)=" & strCondition1 & ") works fine if I take out " & strCondition1 & " and put the following into the code in its place "#9/19/2012#" however if I have ((Accounts.Date)=" & strCondition1 & ") and then enter 9/19/2012 into the rdate = InputBox("Enter Date") box the code doesnt run at all after pressing enter.

Code:

Option Compare Database
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

   Sub Send_Monthly_Invoices()
   
   Dim dbsReservations As DAO.Database
   Dim rstInvoices As DAO.Recordset
   Dim strSQL As String
   Dim rdate As Date
   Dim strCondition1 As Variant
   Dim strEmailRecipient As String
   
   'On Error GoTo ErrorHandler
   
   Set dbsReservations = CurrentDb
   
   rdate = InputBox("Enter Date")
   
   strCondition1 = "#" & rdate & "#"
   
   strSQL = "SELECT * FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE ((Accounts.Date)=" & strCondition1 & ") ORDER BY Accounts.Date;"
   Set rstInvoices = dbsReservations.OpenRecordset(strSQL, dbOpenDynaset)
   
   With rstInvoices
     Do Until .EOF
         DoCmd.OpenReport "Invoices For Month End Emailing", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID] & " AND Date= #" & rstInvoices![Accounts.Date] & "#"
         'DoCmd.OpenReport "Booking Confirmation", acViewNormal, , "Reservations.ReservationID=" & rstInvoices![Reservations.ReservationID]
         Sleep (10000)
         Call SendInvoiceEmail(rstInvoices![SalesPerson Email], rstInvoices!EmailAddress)
         .MoveNext
     Loop
   End With

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

   Exit Sub

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

   End Sub

DoCmd.OpenReport WHERE condition

Thread Tools

DoCmd.OpenReport WHERE condition

Similar Threads

Need help w/ docmd.openreport

OpenReport WHERE Condition Not Working

Difference between using Query based Report and Where Condition in OpenReport

DoCmd OpenReport ... where condition with a Like

Can docmd.openreport print X copies on Y printer?

Posting Permissions