Hi, this post is on “How To Make An Access Report Based On Form Inputs”.
Many times you want your report based on dynamic criteria. In this post I am going
to show you how you can generate a report based on data selected from combo box inputs on your form.
It’s easier to do with VBA. I’ll show you here:
Here I have a form called “frmParameters” with 3 combo boxes reflecting data from my table, “tblReportData”.
When I make my selections and click the “Produce Report” button, I get the following report result:
Here is my code for the filter form, “frmParameters”:
Option Compare Database Private Sub cboFindingID_AfterUpdate() Dim strSQL2 As String If Len(Me.cboFindingID) > 0 Then strSQL2 = "SELECT DISTINCT [Bldg Number], [Finding ID] FROM tblReportData WHERE [Finding ID]= '" & Me.cboFindingID & "'" Else strSQL2 = "SELECT DISTINCT [Bldg Number] FROM tblReportData " End If Me.cboBuildingNumber.RowSource = strSQL2 End Sub Private Sub cboBuildingNumber_AfterUpdate() Dim strSQL3 As String If Len(Me.cboBuildingNumber) > 0 Then strSQL3 = "SELECT DISTINCT [Rating Description] FROM tblReportData WHERE [Bldg Number]= '" & Me.cboBuildingNumber & "'" Else strSQL3 = "SELECT DISTINCT [Rating Description] FROM tblReportData " End If Me.cboRatingDesc.RowSource = strSQL3 End Sub Private Sub btnProduceReport_Click() DoCmd.OpenReport "rptReportData", acViewPreview End Sub
…and here is the code for the report, “rptReportData”
Option Compare Database Private Sub Report_Open(Cancel As Integer) Dim strSQL As String Dim strWhere As String strSQL = "SELECT * FROM tblReportData WHERE " If Not IsNull(Forms("frmParameters").Controls("cboFindingID")) Then strWhere = "[Finding ID] = '" & Forms("frmParameters").Controls("cboFindingID") & "'" End If If Not IsNull(Forms("frmParameters").Controls("cboBuildingNumber")) Then strWhere = strWhere & " AND [Bldg Number] = '" & Forms("frmParameters").Controls("cboBuildingNumber") & "'" End If If Not IsNull(Forms("frmParameters").Controls("cboRatingDesc")) Then strWhere = strWhere & " AND [Rating Description] = '" & Forms("frmParameters").Controls("cboRatingDesc") & "'" End If 'clean strWhere string If Left(strWhere, 4) = " AND" Then strWhere = Mid(strWhere, 5) End If Me.RecordSource = strSQL & strWhere End Sub
Here’s the database:
access report parameters.accdb
Let me know if you have any questions.






