Hello everyone,
I'm having a problem here and I'd really appreciate your help if it were possible.
I have a form that filters by date and filters/shows by driver name the services that the driver has to do during the day.
This form has a button that opens the report with the following command:
DoCmd.OpenReport "Daily driver service booking", acViewPreview, , "[ServiceDate]=" & Format(Me.[ServiceDate], "\#mm/dd/yyyy\#") & " AND [DriverName] = '" & Trim(Me.DriverName) & "'", acWindowNormal
Now I'd like to adapt it so that I can send this report as an attachment by email.
I've been researching and testing and I've found a code that I've adapted and that looks like this:
Dim sExistingReportName As String
Dim sAttachmentName As String
'Input variables
sExistingReportName = "Daily driver service appointment"
sAttachmentName = "Service day " & Me![ServiceDate] & " from " & Me![DriverName]
'The code to make it happen
DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden
Reports(sExistingReportName).Caption = sAttachmentName
DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, Me![E-mail_MemberDL], _
, , "Service of the day " & Me![DateService] & " to " & Me![DriverName], "We hope this message finds you well." & vbNewLine & vbNewLine & "Attached is a list of the services scheduled for tomorrow." & vbNewLine & vbNewLine & "Thank you"
DoCmd.Close acReport, sExistingReportName
End Sub
It's working fine except that it's not displaying the report properly, i.e. it always attaches the same report, regardless of the name of the driver we choose, it always exports the service of the driver who is first in the list.....
I think you need to indicate this part of the code for it to filter and display the report correctly:
"[DataService]=" & Format(Me.[DataService], "\#mm/dd/yyyy\#") & " AND [DriverName] = '" & Trim(Me.DriverName) &
But unfortunately I can't put them together, could you please help?
Thank you very much
I'm having a problem here and I'd really appreciate your help if it were possible.
I have a form that filters by date and filters/shows by driver name the services that the driver has to do during the day.
This form has a button that opens the report with the following command:
DoCmd.OpenReport "Daily driver service booking", acViewPreview, , "[ServiceDate]=" & Format(Me.[ServiceDate], "\#mm/dd/yyyy\#") & " AND [DriverName] = '" & Trim(Me.DriverName) & "'", acWindowNormal
Now I'd like to adapt it so that I can send this report as an attachment by email.
I've been researching and testing and I've found a code that I've adapted and that looks like this:
Dim sExistingReportName As String
Dim sAttachmentName As String
'Input variables
sExistingReportName = "Daily driver service appointment"
sAttachmentName = "Service day " & Me![ServiceDate] & " from " & Me![DriverName]
'The code to make it happen
DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden
Reports(sExistingReportName).Caption = sAttachmentName
DoCmd.SendObject acSendReport, sExistingReportName, acFormatPDF, Me![E-mail_MemberDL], _
, , "Service of the day " & Me![DateService] & " to " & Me![DriverName], "We hope this message finds you well." & vbNewLine & vbNewLine & "Attached is a list of the services scheduled for tomorrow." & vbNewLine & vbNewLine & "Thank you"
DoCmd.Close acReport, sExistingReportName
End Sub
It's working fine except that it's not displaying the report properly, i.e. it always attaches the same report, regardless of the name of the driver we choose, it always exports the service of the driver who is first in the list.....
I think you need to indicate this part of the code for it to filter and display the report correctly:
"[DataService]=" & Format(Me.[DataService], "\#mm/dd/yyyy\#") & " AND [DriverName] = '" & Trim(Me.DriverName) &
But unfortunately I can't put them together, could you please help?
Thank you very much
