Introduction
Searching for and locating a record in a Form is quite easy using the Edit → Find (Ctrl + F) option on a specific field value. However, this method retrieves only the first matching record, even when multiple records share the same search text. In practice, we often need to filter records that match criteria across fields, for example, finding all Sales Representatives of Northwind Traders who are located in the City of London.
Let’s create a simple method to find and display all records from the Employees table that match both the City and Title fields.
If you haven’t already done so, import the Employees sample table from:
C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
-
Next, create a temporary table named temp_param with two text fields: City and Title.
Add a single record with the following values:
-
City: London
-
Title: Sales Representative
-
Now, design the Main Form using this table as its Record Source and place both fields in the Header Section of the Form.
For a better and more user-friendly approach, create two Combo Boxes instead of directly placing the fields on the Form. Populate these Comboboxes using distinct values from the City and Title fields of the Employees table. To do this, create two Select Queries that group these field values, and then use them as the Row Source for the Combo Boxes. Follow the steps outlined below.
Query Name: cboCityQ
SELECT Employees.City FROM Employees GROUP BY Employees.City;
Copy and paste the above SQL String into a new Query's SQL editing window and save it with the name cboCityQ. Create a second Query with the SQL string given below and save the Query with the name cboTitleQ.
- Query Name: cboTitleQ
SELECT Employees.Title FROM Employees GROUP BY Employees.Title;
Create a Combo-Box in the Header Section of the Main Form using cboCityQ as source data and select City as Control Source.
Create another Combo-Box, use the cboTitleQ Query as source data, and select Title as Control Source.
Name the City field Combo-Box, as cboCity, and the Title field, Combo-Box name as a cboTitle.
Design a Datasheet form on the Employees Table and save the Form with the name Employees_Sub.
Insert the Employees_Sub Form as a Sub-Form in the Detail Section of the Main Form.
Click on the Sub-Form, display the Property Sheet (View -> Properties), and set the following Property values as shown below:
Link Child Fields = City; Title
Link Master Fields = cboCity;cboTitle
Now you can select the City and Title values from the combo boxes, and all the matching records will immediately show up in the Datasheet Sub-Form.
Database Sharing Issues
If the database is used by a single user (or opened with exclusive access by one user on the network), the above method will work perfectly without any issues. However, the situation changes when the database is shared among multiple users on a network.
Even though each user opens a separate instance of the Main Form on their own machine, they are all sharing the same underlying table to set their search criteria at the same time. This shared access can lead to conflicts, because when one user refreshes the Main Form and updates the record in the temp_param table, it can overwrite or interfere with another user’s search parameters.
As a result, both users may end up with unexpected or incorrect results when the form is refreshed and the shared parameter table is modified simultaneously.
A Workaround Method
A workaround to this problem is to use Unbound Text Boxes/Combo-Boxes on the Main Form and not to use the temp_param Table at all to store the values selected from cboCity and cboTitle Combo-Boxes.
Users can set the Values on the Unbound Text Box/combo box on their own instance of the Form without conflicts.
This method also has some, not-so-serious, side effects. When the User opens the Main Form, it will be empty till they select some values from the unbound combo boxes. We can rectify this to a certain extent by creating two Custom Properties on the Main Form. Save the last used value from the combo box controls into these custom properties when the Form closes, and restore them when the Form opens again.
Why, I said to a certain extent, because if several Users are sharing the Main Form, then the Custom Property values are saved by each User in the Main Form (when he/she closes their instance of the Form) and will retain only the value saved last. But all the Users who open the Main form next time will be presented with the records related to the Custom Property Values saved last. But this is not a big issue because most probably next time when they open the Form, they may require records for different criteria and can change it too.
Creating Custom Properties on Form
To implement this method, first, we must create the Custom Properties: prpCity and prpTitle on the Main Form and save some initial values into them. We can do this only with VBA Code. The Sample Code is given below:
Public Function CustomProperty()
Dim db As DAO.Database, doc As Document, prp As Property
Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")
'creates the Custom Property with Name, data type and initial value
Set prp = doc.CreateProperty("prpCity", dbText, "London")
'add the new Custom Property to the Properties collection of the Form Main
'NB: This will not appear in the Property Sheet of the Form
doc.Properties.Append prp
Set prp = doc.CreateProperty("prpTitle", dbText, "Manager")
doc.Properties.Append prp
doc.Properties.Refresh
End FunctionHere, you can see that the reference to the Main Form is addressed differently than the usual method of Forms_Main or Forms![Main] or Forms("Main"), etc.. The Forms group is addressed as Container; a member of the Containers (Tables, Forms, Reports, etc.) The group and the Main Form are addressed as a Document, a member of the Documents Collection. To learn more about Containers and Documents, visit the page with the Title: Saving Data on Forms not in a Table.
Since this is a one-time exercise, you can copy the above Code into a Global Module (Standard Module) and run the Code directly by placing the cursor in the middle of the Code and pressing F5 (Run).
If you attempt to run the Code a second time, it will show Errors indicating that the Custom Properties with the given name are already present in the Form.
The next step is to use these Custom Properties within the Form_Close() and Form_Load() event procedures. In the Form_Close() event, we will save the current values from the Combo Box controls in the Form’s Custom Properties. Then, in the Form_Load() event, these saved values will be restored automatically into the corresponding Combo Box controls when the Form is opened again.
This ensures that the user’s last-selected filter criteria are remembered and re-applied the next time the Form is opened, providing a smoother and more user-friendly experience.
Saving Combo Box Value into Custom Property
The following Code saves the combo box contents into prpCity and prpTitle custom properties on the Main Form when the Form is closed:
Private Sub Form_Close()
Dim db As Database, doc As Document, prp As Property
On Error GoTo Form_Close_Err
Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")
'Save the current values from the combo boxes into the custom properties
doc.Properties("prpCity").Value = Me![cboCity]
doc.Properties("prpTitle").Value = Me![cboTitle]
Form_Close_Exit:
Exit Sub
Form_Close_Err:
MsgBox Err.Description, , "Form_Close()"
Resume Form_Close_Exit
End Sub
Restoring Value from Custom Property
The following Code restores the cboCity and cboTitle values when the Main Form opens for Users again:
Private Sub Form_Load()
Dim db As Database, doc As Document, prp As Property
On Error GoTo Form_Load_Err
Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Main")
'Set the Combobox values from the Custom Property values saved earlier
Me![cboCity] = doc.Properties("prpCity").Value
Me![cboTitle] = doc.Properties("prpTitle").Value
Form_Load_Exit:
Exit Sub
Form_Load_Err:
MsgBox Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub
How about positioning a particular record on the Form that you worked on last time, as the current record when the Form opens? Click here to find out.
No comments:
Post a Comment
Comments subject to moderation before publishing.