Combo box to open a report with multiple criteria

[Guest]

Hi

Can anyone help. I'm not too good at VBA and I am trying to write a
timesheet database. I want to open a specific report based on the client
name and the month. These are selected from unbound combo boxes on an
unbound form. I want the user to click on a button to generate the relevant
report. However, it's not working!!! The code is on the On Click event of
the button. Can someone have a look at this code and tell me what I am doing
wrong. I would be very grateful!!!

Private Sub btnOpenClientMonth_Click()

On Error GoTo HandleError

DoCmd.OpenReport "rpt_timesheet_by_client_and_month", acViewPreview, , _
"Client = Forms!frm_select_report!cmbClientName" _
And "Month = Forms!frm_select_report!cmbMonthName"

Exit Sub

HandleError:
MsgBox "You must select a client and a month"

End Sub

Thanks for taking a look
Aehan

 

[John Spencer]

DoCmd.OpenReport "rpt_timesheet_by_client_and_month", acViewPreview, , _
"Client =""" & Forms!frm_select_report!cmbClientName & """ AND " & _
"Month = """ & Forms!frm_select_report!cmbMonthName & """"

The last argument should be building a where string that could be used to
filter the data for the report. To do so, you need to provide the field
names as seen in the report's source and the VALUES of the controls on the
form.

Your version was not doing that. the above would return
Example: cmbClientName is equal to "Jones" and cmbMonthName = "Sept"

Client = "Jones" AND Month = "Sept"

if your Client field is not a string or your Month field is not a string,
then the above will fail (Probably Type 13 - Mismatch error).

 

[Guest]

Thank you so much! This works perferctly.

Aehan