MS Access – Open a Report fed by a Combo Box

Most administrators want or have a huge database with all the systems/equipment that they manage. There are a few tricks I have learned over the years for a variety of tasks in Access, this is one of them.
This code allows you to open a report by clicking on a button fed by a combo box. The user selects something, clicks a button and spits out a report based on what they pick.

Code

Private Sub openReport_Click()
If IsNull(Me.Combo0) Then
MsgBox "Please select a server."
Me.Combo0.SetFocus
Else
DoCmd.openReport "Software Installed per Device", _
acViewPreview, _
WhereCondition:="devices_name=" & _
Chr(34) & Me.Combo0 & Chr(34)
End If
End Sub

Explanation

If it’s a combo box, so only one item can be selected, the code for the button’s Click event procedure would look something like this:
Private Sub cmdReport_Click()
If IsNull(Me.cboYourComboBox) Then
MsgBox "Please select something first."
Me.cboYourComboBox.SetFocus
Else
DoCmd.OpenReport "rptYourReport", _
acViewPreview, _
WhereCondition:="SomeField=" & Me.cboYourComboBox
End If
End Sub
In the above,
cmdReport = the name of your button cboYourComboBox = the name of your combo box rptYourReport = the name of the report SomeField = the name of the field (in the report’s recordsource) that you want to filter by
The above code assumes that the field (“SomeField”) is a numeric field. If it’s a text field, then the code needs to be modified to wrap the value of the combo box quotes:
WhereCondition:=“SomeField=” & Chr(34) & Me.cboYourComboBox & Chr(34)
Advertisements
MS Access – Open a Report fed by a Combo Box