Email report with query criteria or filter doesn't work

[Christine]

My report, rptCAR, can be sent from a number of different
forms based on the current record (CARNum).

strWhere = "[CARNum]=" & Me!CARNum
DoCmd.OpenReport "rpt CAR", acViewPreview, , strWhere

This works fine IF I wanted to preview the report. But,
what I want to do is to send the report via Email. There
is no option in the command DoCmd.SendObject acSendReport
to set the report's criteria. So, I have tried the
following:

1. Base the report on a query (qryCAR), in which the
criteria is set to [Me]![CARNum]. Then, in the form:

DoCmd.SendObject acSendReport, "rpt CAR", acFormatRTF, ...

2. Store the current form's name to a variable:

Public mCurrentForm
Dim mCurrentForm as mCurrentForm = "frm Initiate CAR".
DoCmd.SendObject acSendReport, "rpt CAR", acFormatRTF,...

In the qry, set the criteria as:

[Forms]![mCurrentForm]![CARNum]

In each of the above attempts it asks me to provide the
CARNum. What am I doing wrong?

 

[Alphonse Giambrone]

A query will not recognize vb variables or 'Me'.
If always using the same form just use Forms!yourformname!CARNum.

If not always the same form or you have any problem with the above, create a
public function in a standard module (not the form's module) and use it as
the query criteria.

e.g. (air code)
Public Function GetCarNum
GetCarNum = Screen.ActiveForm.Controls("CARNum").Value
End Function

The query Where clause would simply be:
Where CarNum = GetCarNum()


HTH