Forms and Custom Properties ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Searching and finding a record in a Form is easy with Edit - -> Find (Ctrl + F) Option on a particular field value. But, this will fetch only the first record even if there are more records matching the same search text. Most of the time we need to find records that matches values in more than one field, like records of Sales Representatives of Northwind Traders located in the City of London.

We will create a simple method to find all records of Employees Table that matches both fields (City and Title) and display them. If you have not imported the Employees sample Table from C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb before then you may do it now.

Create a temporary table with the name temp_param with two text fields; City and Title.
Add a single record with City field value as London and Title field value as Sales Representative.
Design a Main Form with this Table and place both Fields on the Header Section of the Form.
Even better, if you create two Combo Boxes (instead of placing the above fields directly) using values from City and Title fields from Employees Table. Create two Select Queries; by grouping values on these fields and use them as source for the Combo Boxes. Do that by following the steps given 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 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 using cboTitleQ as source data and select Title as Control Source.
Name the City field Combo-Box as cboCity and Title field Combo-Box as cboTitle.
Design a Datasheet Form on Employees Table and save the Form with the name Employees_Sub.
Insert the Employees_Sub Form as 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 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 it is a single User database (or given Exclusive Access to a single User on Network) then the above method works fine and will have no issues. But, it is different when the database is shared on a Network. Even though different instance of the Main Form is used by individual User, on different Machines, they are sharing the same Table to set different search criteria at the same time and likely to clash each other when the Main Form is refreshed and the temp_param table record is updated and end up with unexpected result.

A Workaround Method

A work-around 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.

The Users can set the Values on the Unbound Text Boxes/Combo-Boxes on their own instance of the Form without conflicts.

This method also has some minor side effects. When the User opens the Main Form it will be empty till they Select some values from the unbound Combo-Boxes. This we can rectify to certain extend 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 back when the Form opens again.

Why I said 'to certain extend' because if several Users are sharing the Main Form then the Custom Property values are saved by each User of 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. 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 Function

Here, 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.) Group and the Main Form is 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 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 on the Form.

The next step is to use these Properties on the Form_Close() and Form_Load() EventProcedures to save values from the Combo-Box Controls into the Custom Properties and restore them back into the Combo-Box Controls when the Form is open.

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 by 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, which you have worked on last time, as current record when the Form opens? Click here to find out.