filter a report based on a form value

The easiest way I have found accomplishing this without updating the query is to assign a small piece of code to a a button named cmd_view_all_shares:

DoCmd.OpenReport "the_report_you_want_to_open", acViewPreview, , "the_field_you_want_to_filter_in_the_report = " & the_field_in_the_form

In practice, the code looks like this:

Private Sub cmd_view_all_shares_Click()
'Filter report to display only Supplier currently showing on frmExample
' (by SupplierID field)
DoCmd.OpenReport "rpt_Employee_Summary", acViewPreview, , "EmployeeID = " & EmployeeID

End Sub

 

 

 

Today, I needed to open a record specific report from a command button on a form.  Rather than building a filter query to serve as the data source for my report, I decided that I would implement a small piece of code to filter the report that opens up.

This code utilizes the DoCmd.OpenReport function and filters the report it opens based on the field “Employee Name”:

Private Sub Command33_Click()
'DoCmd.OpenReport "YourReportName", the Report format, , WhereCondition:="user_name='" & Me.txtUserName & "'"     DoCmd.OpenReport "rpt_profit_shares", acViewPreview, , WhereCondition:="[employee name]='" & Me.[employee name] & "'"    

End Sub

Once I finished that I realized that the report I needed to open would actually be conditional, depending on what kind of bonus plan the employee was on.  To manage that, I implemented an if statement:

Private Sub Command33_Click()

‘DoCmd.OpenReport “YourReportName”, the Report format, , WhereCondition:=”user_name='” & Me.txtUserName & “‘”

If Forms![frm_Grant_info]![Bonus Share or Profit Share] = “Profit” Then
DoCmd.OpenReport “rpt_profit_shares”, acViewPreview, , WhereCondition:=”[employee name]='” & Me.[employee name] & “‘”

ElseIf Forms![frm_Grant_info]![Bonus Share or Profit Share] = “Bonus” Then
DoCmd.OpenReport “rpt_bonus_shares”, acViewPreview, , WhereCondition:=”[employee name]='” & Me.[employee name] & “‘”

End If

End Sub

One thing I noted that gave me errors, was that I could not just use “Else”, I had to use “Elseif”.
source:  http://stackoverflow.com/questions/13863958/access-form-button-to-generate-and-filter-existing-report

source 2: http://www.access-programmers.co.uk/forums/showthread.php?t=172634