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] & “‘”
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] & “‘”
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: http://stackoverflow.com/questions/13863958/access-form-button-to-generate-and-filter-existing-report
Comments
Post a Comment