Query Form For Viewing/Printing A Report, Access 2010

[pclutts]

I have an Access database that has a table named tblDocTrackLog. In the database I use at the office, this is a linked table from a SharePoint list that I can't modify and which I don't have admin rights to. When the user launches the database (a copy I have made for purposes of demonstration), the Report Builder form opens to reveal two cascading combo boxes. The top one selects the Project Name and the bottom one selects the Item Type based on the Project Name selected. If you select a Project Name (without selecting an Item Type) and click the Open Report command button, the report opens as expected. However, I am stumped on how to get the report to open properly when you make a selection from the second combo box as well. I have attached the zipped database. I would appreciate any help/suggestions I can get. Thanks.

[aytee111]

One way is to have two queries, one for when only the project is selected and the other for when both are selected. Then in the OnOpen event of the report, set the RecordSource=qryname, depending on the values in the combo boxes (e.g. if Not IsNull([forms]![frmMain].[cboItemType] then me.RecordSource="qry2" else me.RecordSource=qry1).

You could also recreate the query based on the form values, a little more complex.

[pclutts]

Here is the script I used for the OnClick "Open Report" command button. However, when I make a selection for Item Type and open the report, it is as if I had left that combobox empty.

Private Sub Command0_Click()
If Not IsNull([Forms]![frmMain].[cboItemType]) Then
Me.RecordSource = qryItemType
Else: Me.RecordSource = qryProjectName
End If
DoCmd.OpenReport "rptTest", acViewPreview
End Sub

[aytee111]

The record source needs to change on the report, not on the form. The OnOpen event with the code you have above is done in the report, not the form.