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.
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
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)