First create a report based on the table so that by default it returns all
rows.
Ideally you should also have a table Areas with one row per area and related
your plant and equipment table to this, enforcing referential integrity.
This protects the integrity of the data by ensuring that only valid area
names can be entered in the plant and equipment table. On your dialogue form
you'd then have a combo box, cboArea say, with a RowSource property of:
SELECT Area FROM Areas ORDER BY Area;
Without an Areas table the RowSource property for the combo box would be:
SELECT DISTINCT Area FROM [PlantAndEquipment] ORDER BY Area;
where PlantAndEquipment is the name of your current table.
I'd suggest having two buttons on the form, one to preview and one to print
the report. The code for the Click event procedure of the former would be
like this:
Const conMESSAGE = "No area selected."
Const conREPORT = "YourReportNameGoesHere"
Dim strCriteria As String
' first make sure an area has been selected
If Not IsNull(Me.cboArea) Then
strCriteria = "Areas = """ & Me.cboArea & """"
' open report in print preview, filtered to selected area
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
The code for the button to print the report would be the same apart from not
specifying acViewPreview as the View argument of the OpenReport method:
Const conMESSAGE = "No area selected."
Const conREPORT = "YourReportNameGoesHere"
Dim strCriteria As String
' first make sure an area has been selected
If Not IsNull(Me.cboArea) Then
strCriteria = "Areas = """ & Me.cboArea & """"
' print report, filtered to selected area
DoCmd.OpenReport conREPORT, _
WhereCondition:=strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
You could if you wished open the report filtered to one or more areas by
using a multi-select list box rather than a combo box. To do this set up the
list box like this:
Its RowSource property would be the same as for a combo box.
For other properties:
Name: lstAreas
MultiSelect: Simple or Extended as preferred.
Add a button to the form to open the report, in print preview in this
example, with the following in its Click event procedure:
Const conMESSAGE = "No area selected."
Const conREPORT = "YourReportNameGoesHere"
Dim varItem As Variant
Dim strAreaList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.lstAreas
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAreaList = strAreaList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem
' remove leading comma
strAreaList = Mid(strAreaList, 2)
strCriteria = "Areas In(" & strAreaList & ")"
DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox conMESSAGE , vbExclamation, "Invalid Operation"
End If
If you are unfamiliar with entering code in event procedures, this is how
its done:
Select the control in form design view and open its properties sheet if its
not already open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.
Ken Sheridan
Stafford, England
