Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Forms and Custom Properties


Searching and finding a record in a Form is easy with the 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 match 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 the Employees Table that match 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.

  1. Create a temporary table with the name temp_param with two text fields; City and Title.
  2. Add a single record with City field value as London and Title field value as a Sales  Representative.
  3. Design the 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 of the City and Title fields from the Employees Table. Create two Select Queries; by grouping values of these fields and using them as the source for the Combo Boxes. Do that by following the steps given below.

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

  5. Query Name: cboTitleQ
    SELECT Employees.Title
    FROM Employees
    GROUP BY Employees.Title;
  6. Create a Combo-Box in the Header Section of the Main Form using cboCityQ as source data and select City as Control Source.
  7. Create another Combo-Box, use the cboTitleQ Query as source data, and select Title as Control Source.
  8. Name the City field Combo-Box, as cboCity and Title field, Combo-Box name as a cboTitle.
  9. Design a Datasheet Form on the Employees Table and save the Form with the name Employees_Sub.
  10. Insert the Employees_Sub Form as Sub-Form in the Detail Section of the Main Form.
  11. 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

  12. 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 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 instances of the Main Form are used by individual Users, on different Machines, they are sharing the same Table to set different search criteria at the same time. These are likely to clash with each other when the Main Form is refreshed and the temp_param table record is updated.  This can end up with an unexpected result for both users.

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 Boxes/Combo-Boxes 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 back 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
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 in 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]

Exit Sub

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

Exit Sub

MsgBox Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

How about positioning a particular record on the Form, which 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.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code