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

Comments

Post a Comment

Popular posts from this blog

Export Access Object to Excel ,PDF, RTF , etc. using DoCmd.OutputTo

Microsoft Access allows us to export access object like table, query ,etc to other formats like Excel, PDF, RTF , etc. Using “DoCmd.OutputTo” To know about DoCmd.OutputTo visit the below link http://msdn.microsoft.com/en-us/library/aa141534(v=office.10).aspx Syntax DoCmd.outputto ObjectType, ObjectName, OutputFormat, Output file, Autostart, Templatefile, encoding, ObjectType – acOutputTable( to output table), acOutputquery(to output query),acoutputreport(to output report),ascendform(to output form), acOutputModule(to output module) ObjectName – Name of table, query, Report etc. OutputFormat – acFormatHTML ,acFormatRTF ,acFormatSNP,acFormatTXT ,acFormatXLS, acFormatXLSX , acFormatPDF , etc. Output file – Complete path of output file eg. C:\ abc.xls  or d:\ abc.pdf  Keep it blank if you want to save at run time. AutoStart Optional Variant Templatefile – The full name, including the path, of the file to use as a template for an HTML file Encoding- Optional Variant  If you are fa
Read more

HOW TO OPEN ACCESS IN FULL SCREEN: OPENING A FORM AS MAXIMIZED ?

Image
https://accessdatabasetutorial.com/open-access-database-full-screen-opening-form-maximized/ Once users get a grip on form design techniques in  Microsoft Access database , they normally want to jump ahead and customize forms in their applications that appear polished and professional. One technique they can use is to  open Access in full screen  view which hides other no essential screens keeping your database neat and tidy. It requires a combination of setting various form properties during the design time mode and optionally (but ideally) applying either macros or VBA code  to the form’s module too. The final touches to this process would be to create a desktop icon shortcut to load the database file and run the assigned AutoExec macro to the application which will  open  Access Database  in full screen  hiding non-essential components. How To Open Access In Full Screen: Opening A Form As Maximized Create your form in design view and make sure you set the following pr
Read more

DoCmd.OutputTo in xlsx (Excel 2007+) Format

https://www.devhut.net/2014/08/07/docmd-outputto-in-xlsx-excel-2007-format/ A common technique for exporting data to Excel is to utilize the   DoCmd.OutputTo  Method. Now, previously (pre Office 2007), the basic format to export a query to Excel format was simply: DoCmd.OutputTo acOutputQuery, "YourQueryName" , acFormatXLS, , True BUT, once you port such a database to a post Office 2007 computer, you start to get it exported as a “ Microsoft Excel 5.0/95 Workbook (*.xls) ” format and such a file will thus open Excel in compatibility mode possibly given the user compatibility errors/messages when they go to save the file after working with it. So unless you have to ensure legacy compatibility, it is strongly advisable to update your code to export the data in “ Excel Workbook (*.xlsx) ” format, the new Excel file format thus eliminating any compatibility issues. Hence, we simply need to change the  OutputFormat  format variable to a more appropriate format ( a
Read more