PURPOSE: Outline a method I have used to create a flexible search tool in MS Access. This is just one way to create a flexible search tool. It is not the only way, and may not even be the best, but it is effective.
OVERVIEW: This method uses 3 forms; one to gather search criteria, one to retrieve and display results in a summarized form, and another that displays all the detailed information of a record selected from the list.
Criteria is entered into the search form and then a command button is clicked. The On_Click procedure builds the WHERE clause for the retreive form based on the criteria entered and then opens the retrieve form and sets the recordsource to the selected records.
HOW IT WORKS:
My application tracks work orders for a number of locations. Each work order includes an order ID, an order manager who is repsonsible for the order, a customer name, a location, an order entry date, and an order completion date.
The search form allows a user to search for orders by any combination of these fields. When the search command button is clicked, the search form opens the retrieve form and sets the recordsource for that form by modifying the WHERE clause of the query that populates that form.
If no criteria is entered, a list of all orders is retrieved.
Tables involved:
Orders
OrderID Long (PK)
OrdMgrID Long (FK) Employees.EmpID
Cust Text
LocID Long (FK) Locations.LocID
OrdEntry Date
OrdComp Date
Cost Double
Other1 Text
Other2 Text
Other3 Text
Other4 Text
Other5 Text
Locations
LocID Long (PK)
LocName Text
SiteMgr Text
Employees
EmpID Long (PK)
Name Text
The Search form:
frmSearch is a form that has a number of controls (with appropriate labels) to collect criteria from user input:
Control Name Label
Text box txtOrderID Order Number
Combo box cboOrderMgr Order Manager
Text box txtCust Customer Name
Check box chkCustExact Exact Customer Name
Text box txtLocation Location
Frame fraLocSearchType Location Search Type
Text box txtOrdEntryFrom Order Entered From:
Text box txtOrdEntryTo Order Entered To:
Checkbox chkOpenOnly Open Orders Only
Checkbox chkClosedOnly Closed Orders Only
Command Button cmdSearch Retrieve Orders
Command Button cmdCancel Cancel
Command Button cmdClear Clear
Option buttons in the frame:
Name Option Value Caption
optExact 1 Exact
optBegins 2 Begins With
optContains 3 Contains
optEnds 4 Ends With
cboLocation gets a list of valid locations from the Locations table and is bound to the LocID field.
CODE FOR frmSearch
Private Sub chkClosedOnly_Click()
‘*****************************************************************
‘Purpose: An order cannot be both open and closed, so these
‘ check boxes are mutually exclusive.
‘*****************************************************************
‘If the closed only check box is checked, uncheck open only
If chkClosedOnly = True Then
ChkOpenOnly = False
End If
End Sub
Private Sub chkOpenOnly_Click()
‘*****************************************************************
‘Purpose: An order cannot be both open and closed, so these
‘ check boxes are mutually exclusive.
‘*****************************************************************
‘If the closed only check box is checked, uncheck closed only
If chkOpenOnly = True Then
ChkClosedOnly = False
End If
End Sub
Private Sub cmdCancel_Click()
DoCmd.Close
End If
Private Sub cmdCancel_Click()
‘*****************************************************************
‘Purpose: Clear all criteria from previous searches
‘*****************************************************************
txtOrderID = “”
cboOrderMgr = “”
txtCust = “”
txtLocation = “”
fraLocSearchType = 3 ‘Sets Default to “Contains”
txtOrdEntryFrom = “”
txtOrdEntryTo = “”
chkOpenOnly = True ‘Sets Default to Open Orders Only
chkClosedOnly = False
End If
Private cmdSearch_Click()
‘*****************************************************************
‘Purpose: Build the WHERE clause for the SQL statement that
‘ populates the search results form (frmRetrieve),
‘ open that form, and set the recordsource based on
‘ that WHERE clause
‘*****************************************************************
Dim sSQL As String 'SQL for recordsource for frmRetrieve
Dim sWHERE As String 'WHERE clause for SQL statement
Dim sAND As String 'And string starts as an empty string
‘until the first criteria. Thereafter
‘it contains “ AND “ to connect
‘additional criteria to the clause
‘If no criteria is entered, set WHERE string to nothing and
‘retrieve all records.
If IsNull(txtOrderID) And _
IsNull(cboOrderMgr) And _
IsNull(txtCust) And _
IsNull(txtLocation) And _
IsNull(txtOrdEntryFrom) And _
IsNull(txtOrdEntryTo) And _
chkOpenOnly = False And _
chkClosedOnly = False Then
sWHERE = “;”
GoTo GetIt
Else
sWHERE = “WHERE “
sAND = “”
End If
‘Go through all the fields and build WHERE clause
‘Order ID
If Not IsNull(txtOrderID) Then
sWHERE = sWHERE & “Orders.OrderID = “ & txtOrderID & “ “
‘Skip checking other fields
GoTo GetIt
End If
‘Order Manager
If Not IsNull(cboOrderMgr) Then
sWHERE = sWHERE & “Orders.OrdMgrID = “ & cboOrderMgr & “ “
sAND = “ AND “
End If
‘Customer
If Not IsNull(txtCust) Then
If chkCustExact = True Then
sWHERE = sWHERE & sAND & “Orders.Cust = ‘” & txtCust _
& “’ “
Else
sWHERE = sWHERE & sAND & “Orders.Cust Like ‘*” & _
txtCust & “*’ “
End If
sAND = “ AND “
End If
‘Location
If Not IsNull(txtLocID) then
Select Case fraLocSearchType
Case 1 ‘Exact match
sWHERE = sWHERE & sAND & “Locations.LocName = ‘” & _
txtLocation & “’ “
Case 2 ‘Begins With Entered Text
sWHERE = sWHERE & sAND & “Locations.LocName LIKE ‘*” & _
txtLocation & “’ “
Case 3 ‘Contains Entered Text
sWHERE = sWHERE & sAND & “Locations.LocName LIKE ‘*” & _
txtLocation & “*’ “
Case 4 ‘Ends With entered text
sWHERE = sWHERE & sAND & “Locations.LocName LIKE ‘” & _
txtLocation & “*’ “
End Select
sAND = “ AND “
End If
‘Order Entry Date – From Only
If Not IsNull(txtOrdEntryFrom) And IsNull(txtOrdEntryTo) Then
sWHERE = sWHERE & sAND & “Orders.OrdEntry >= #” & _
txtOrdEntryFrom & “# “
sAND = “ AND “
End If
‘Order Entry Date – To Only
If IsNull(txtOrdEntryFrom) And Not IsNull(txtOrdEntryTo) Then
sWHERE = sWHERE & sAND & “Orders.OrdEntry <= #” & _
txtOrdEntryTo & “# “
sAND = “ AND “
End If
‘Order Entry Date – Between From and To
If Not IsNull(txtOrdEntryFrom) And IsNull(txtOrdEntryTo) Then
sWHERE = sWHERE & sAND & “Orders.OrdEntry Between #” & _
txtOrdEntryFrom & “# AND #“ & txtOrdEntryTo & “# “
sAND = “ AND “
End If
‘Open or closed Orders Only
If chkOpenOnly = True Then
sWHERE = “Orders.OrdComp IS NULL “
ElseIf chkClosedOnly = True Then
sWHERE = “Orders.OrdComp IS NOT NULL “
End If
sWHERE = sWHERE & “;”
GetIt:
‘Print WHERE clause in debug window
Debug.Print sWHERE
SSQL = “SELECT Orders.OrderID, Employees.Name, Orders.Cust, “ & _
“Locations.LocName, Orders.OrdEntry, Orders.OrdComp, “ & _
“Orders.Cost, Locations.SiteMgr “ & _
“FROM (Locations INNER JOIN Orders ON “ & _
“Locations.LocID = Orders.LocID) INNER JOIN “ & _
“Employees ON Orders.OrdMgrID = Employees.EmpID “ & _
sWHERE
‘Open the retrieve form
DoCmd.OpenForm “frmRetrieve”, acNormal, , , acFormReadOnly, _
acHidden
Forms!frmRetrieve.RecordSource = sSQL
Forms!frmRetrieve.Visible = True
Forms!frmSearch.Visible = False
End If
Private Sub txtOrderID_AfterUpdate()
‘*****************************************************************
‘Purpose: If a search is done on an Order ID, no other criteria
‘ is necessary. If an Order ID is entered, all other
‘ fields are disabled. If the order ID is removed, other
‘ fields are enabled.
‘*****************************************************************
Dim bEnableIt As Boolean
‘If an Order ID is entered, disable all other fields
If IsNull(txtOrderID) Then
bEnableIt = True
Else
bEnableIt = False
End If
CboOrderMgr.Enabled = bEnableIt
txtCust.Enabled = bEnableIt
txtLocation.Enabled = bEnableIt
fraLocSearchType.Enabled = bEnableIt
txtOrdEntryFrom.Enabled = bEnableIt
txtOrdEntryTo.Enabled = bEnableIt
chkOpenOnly.Enabled = bEnableIt
chkClosedOnly.Enabled = bEnableIt
End Sub
The Retrieve form:
frmRetrieve is a form that has labels with field names in a row in the Form Header and and a single row of fields below those labels in the Detail secion. The form’s Default View property is set to Continuous Forms to get a tabular form that will show a list of records retrieved by the search. The fields on the retrieve form are OrderID, OrderMgr, Cust, LocID, OrdEntry, OrdComp, OrdCost, SiteMgr. This form also has a command button that closes the form and returns to the search form.
The double-click even of the controls on this form opens the third form displaying the details of the record that was double-clicked.
The third form is the form where records are actually entered/edited.
_________
Rott Paws
OVERVIEW: This method uses 3 forms; one to gather search criteria, one to retrieve and display results in a summarized form, and another that displays all the detailed information of a record selected from the list.
Criteria is entered into the search form and then a command button is clicked. The On_Click procedure builds the WHERE clause for the retreive form based on the criteria entered and then opens the retrieve form and sets the recordsource to the selected records.
HOW IT WORKS:
My application tracks work orders for a number of locations. Each work order includes an order ID, an order manager who is repsonsible for the order, a customer name, a location, an order entry date, and an order completion date.
The search form allows a user to search for orders by any combination of these fields. When the search command button is clicked, the search form opens the retrieve form and sets the recordsource for that form by modifying the WHERE clause of the query that populates that form.
If no criteria is entered, a list of all orders is retrieved.
Tables involved:
Orders
OrderID Long (PK)
OrdMgrID Long (FK) Employees.EmpID
Cust Text
LocID Long (FK) Locations.LocID
OrdEntry Date
OrdComp Date
Cost Double
Other1 Text
Other2 Text
Other3 Text
Other4 Text
Other5 Text
Locations
LocID Long (PK)
LocName Text
SiteMgr Text
Employees
EmpID Long (PK)
Name Text
The Search form:
frmSearch is a form that has a number of controls (with appropriate labels) to collect criteria from user input:
Control Name Label
Text box txtOrderID Order Number
Combo box cboOrderMgr Order Manager
Text box txtCust Customer Name
Check box chkCustExact Exact Customer Name
Text box txtLocation Location
Frame fraLocSearchType Location Search Type
Text box txtOrdEntryFrom Order Entered From:
Text box txtOrdEntryTo Order Entered To:
Checkbox chkOpenOnly Open Orders Only
Checkbox chkClosedOnly Closed Orders Only
Command Button cmdSearch Retrieve Orders
Command Button cmdCancel Cancel
Command Button cmdClear Clear
Option buttons in the frame:
Name Option Value Caption
optExact 1 Exact
optBegins 2 Begins With
optContains 3 Contains
optEnds 4 Ends With
cboLocation gets a list of valid locations from the Locations table and is bound to the LocID field.
CODE FOR frmSearch
Private Sub chkClosedOnly_Click()
‘*****************************************************************
‘Purpose: An order cannot be both open and closed, so these
‘ check boxes are mutually exclusive.
‘*****************************************************************
‘If the closed only check box is checked, uncheck open only
If chkClosedOnly = True Then
ChkOpenOnly = False
End If
End Sub
Private Sub chkOpenOnly_Click()
‘*****************************************************************
‘Purpose: An order cannot be both open and closed, so these
‘ check boxes are mutually exclusive.
‘*****************************************************************
‘If the closed only check box is checked, uncheck closed only
If chkOpenOnly = True Then
ChkClosedOnly = False
End If
End Sub
Private Sub cmdCancel_Click()
DoCmd.Close
End If
Private Sub cmdCancel_Click()
‘*****************************************************************
‘Purpose: Clear all criteria from previous searches
‘*****************************************************************
txtOrderID = “”
cboOrderMgr = “”
txtCust = “”
txtLocation = “”
fraLocSearchType = 3 ‘Sets Default to “Contains”
txtOrdEntryFrom = “”
txtOrdEntryTo = “”
chkOpenOnly = True ‘Sets Default to Open Orders Only
chkClosedOnly = False
End If
Private cmdSearch_Click()
‘*****************************************************************
‘Purpose: Build the WHERE clause for the SQL statement that
‘ populates the search results form (frmRetrieve),
‘ open that form, and set the recordsource based on
‘ that WHERE clause
‘*****************************************************************
Dim sSQL As String 'SQL for recordsource for frmRetrieve
Dim sWHERE As String 'WHERE clause for SQL statement
Dim sAND As String 'And string starts as an empty string
‘until the first criteria. Thereafter
‘it contains “ AND “ to connect
‘additional criteria to the clause
‘If no criteria is entered, set WHERE string to nothing and
‘retrieve all records.
If IsNull(txtOrderID) And _
IsNull(cboOrderMgr) And _
IsNull(txtCust) And _
IsNull(txtLocation) And _
IsNull(txtOrdEntryFrom) And _
IsNull(txtOrdEntryTo) And _
chkOpenOnly = False And _
chkClosedOnly = False Then
sWHERE = “;”
GoTo GetIt
Else
sWHERE = “WHERE “
sAND = “”
End If
‘Go through all the fields and build WHERE clause
‘Order ID
If Not IsNull(txtOrderID) Then
sWHERE = sWHERE & “Orders.OrderID = “ & txtOrderID & “ “
‘Skip checking other fields
GoTo GetIt
End If
‘Order Manager
If Not IsNull(cboOrderMgr) Then
sWHERE = sWHERE & “Orders.OrdMgrID = “ & cboOrderMgr & “ “
sAND = “ AND “
End If
‘Customer
If Not IsNull(txtCust) Then
If chkCustExact = True Then
sWHERE = sWHERE & sAND & “Orders.Cust = ‘” & txtCust _
& “’ “
Else
sWHERE = sWHERE & sAND & “Orders.Cust Like ‘*” & _
txtCust & “*’ “
End If
sAND = “ AND “
End If
‘Location
If Not IsNull(txtLocID) then
Select Case fraLocSearchType
Case 1 ‘Exact match
sWHERE = sWHERE & sAND & “Locations.LocName = ‘” & _
txtLocation & “’ “
Case 2 ‘Begins With Entered Text
sWHERE = sWHERE & sAND & “Locations.LocName LIKE ‘*” & _
txtLocation & “’ “
Case 3 ‘Contains Entered Text
sWHERE = sWHERE & sAND & “Locations.LocName LIKE ‘*” & _
txtLocation & “*’ “
Case 4 ‘Ends With entered text
sWHERE = sWHERE & sAND & “Locations.LocName LIKE ‘” & _
txtLocation & “*’ “
End Select
sAND = “ AND “
End If
‘Order Entry Date – From Only
If Not IsNull(txtOrdEntryFrom) And IsNull(txtOrdEntryTo) Then
sWHERE = sWHERE & sAND & “Orders.OrdEntry >= #” & _
txtOrdEntryFrom & “# “
sAND = “ AND “
End If
‘Order Entry Date – To Only
If IsNull(txtOrdEntryFrom) And Not IsNull(txtOrdEntryTo) Then
sWHERE = sWHERE & sAND & “Orders.OrdEntry <= #” & _
txtOrdEntryTo & “# “
sAND = “ AND “
End If
‘Order Entry Date – Between From and To
If Not IsNull(txtOrdEntryFrom) And IsNull(txtOrdEntryTo) Then
sWHERE = sWHERE & sAND & “Orders.OrdEntry Between #” & _
txtOrdEntryFrom & “# AND #“ & txtOrdEntryTo & “# “
sAND = “ AND “
End If
‘Open or closed Orders Only
If chkOpenOnly = True Then
sWHERE = “Orders.OrdComp IS NULL “
ElseIf chkClosedOnly = True Then
sWHERE = “Orders.OrdComp IS NOT NULL “
End If
sWHERE = sWHERE & “;”
GetIt:
‘Print WHERE clause in debug window
Debug.Print sWHERE
SSQL = “SELECT Orders.OrderID, Employees.Name, Orders.Cust, “ & _
“Locations.LocName, Orders.OrdEntry, Orders.OrdComp, “ & _
“Orders.Cost, Locations.SiteMgr “ & _
“FROM (Locations INNER JOIN Orders ON “ & _
“Locations.LocID = Orders.LocID) INNER JOIN “ & _
“Employees ON Orders.OrdMgrID = Employees.EmpID “ & _
sWHERE
‘Open the retrieve form
DoCmd.OpenForm “frmRetrieve”, acNormal, , , acFormReadOnly, _
acHidden
Forms!frmRetrieve.RecordSource = sSQL
Forms!frmRetrieve.Visible = True
Forms!frmSearch.Visible = False
End If
Private Sub txtOrderID_AfterUpdate()
‘*****************************************************************
‘Purpose: If a search is done on an Order ID, no other criteria
‘ is necessary. If an Order ID is entered, all other
‘ fields are disabled. If the order ID is removed, other
‘ fields are enabled.
‘*****************************************************************
Dim bEnableIt As Boolean
‘If an Order ID is entered, disable all other fields
If IsNull(txtOrderID) Then
bEnableIt = True
Else
bEnableIt = False
End If
CboOrderMgr.Enabled = bEnableIt
txtCust.Enabled = bEnableIt
txtLocation.Enabled = bEnableIt
fraLocSearchType.Enabled = bEnableIt
txtOrdEntryFrom.Enabled = bEnableIt
txtOrdEntryTo.Enabled = bEnableIt
chkOpenOnly.Enabled = bEnableIt
chkClosedOnly.Enabled = bEnableIt
End Sub
The Retrieve form:
frmRetrieve is a form that has labels with field names in a row in the Form Header and and a single row of fields below those labels in the Detail secion. The form’s Default View property is set to Continuous Forms to get a tabular form that will show a list of records retrieved by the search. The fields on the retrieve form are OrderID, OrderMgr, Cust, LocID, OrdEntry, OrdComp, OrdCost, SiteMgr. This form also has a command button that closes the form and returns to the search form.
The double-click even of the controls on this form opens the third form displaying the details of the record that was double-clicked.
The third form is the form where records are actually entered/edited.
_________
Rott Paws
