email a report on current client

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

i created a database that keeps track of clients and the
meetings they attend. i created a billing sheet report
(invoice) which will promt for a start and end date and
when entered will show clients who have attended between
those 2 dates.
i put in a command button the the form, so that the
billing sheet can be emailed to our accounting department.
the problem i am having is that when i click on the button
in prompts me to enter the dates and it emails the report,
but it emails the billing sheet on every client. I want it
to email the billing sheet on the current client in the
form.Can someone please tell me how to fix? this is the
code i have on click:

Private Sub cmdMailBillingSheet_Click()
On Error GoTo Err_cmdMailBillingSheet_Click

Dim stDocName As String
Dim stlinkcriteria As String

stDocName = "rptBillingSheet"

stlinkcritera = "[clientid]=" & Me![ClientID]
DoCmd.SendObject acReport, stDocName, , stlinkcriteria

Exit_cmdMailBillingSheet_Click:
Exit Sub

Err_cmdMailBillingSheet_Click:
MsgBox Err.Description
Resume Exit_cmdMailBillingSheet_Click

End Sub
 
SendObject does not have any way to specify selection criteria. The 4th
argument is actually the email address of the recipient, so your code would
try to send mail to "[clientid]=xxx"!

To select a specific client, you must filter the report's recordset in
(presumably) the same way you are doing for the start and end dates.

I suggest you add the start and end dates as textboxes on your form and add
the selection criteria to the query on which your report is based:

For DateField: Between Forms!YourForm!StartDate and Forms!YourForm!EndDate
and for ClientID: =Forms!YourForm!ClientID
 
The criteria should be in the query behind the report.
When the report runs, prior to being sent, then the query
behind the report will select the correct record(s).

Hope that helps!

Kevin
 
Back
Top