Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Forms and Custom Properties

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.

  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 Sales Representative.
  3. 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.

  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 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 using cboTitleQ as source data and select Title as Control Source.
  8. Name the City field Combo-Box as cboCity and Title field Combo-Box as cboTitle.
  9. Design a Datasheet Form on 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 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.

Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts