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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 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.

Free! Subscribe To Our YouTube Channel!

by