Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.



We normally save data in Tables, even though we enter them on Forms. We cannot save data on Forms as we do on Tables. But, we can definitely save some useful information on the Form itself without the support of the Data Table to store the values.

Let us look into a situation where we need such a facility for our day-to-day activities. Assume that we want to open the Employees Form in data editing mode (not in data entry mode) with the last record that we were working on during the earlier session, to continue our unfinished work from there. To do this, we must save the key information for the last worked record on the Form before closing the Employees Form.

Import the Employees Table and Employees Form from the NorthWind.mdb sample database located at C:\Program Files\Microsoft Office\Office\Samples (MS-Office 2000) or C:\Program Files\Microsoft Office\Office11\Samples (MS-Office 2003). Make a copy of this File to your current working folder so that whenever you need information from this file, you can easily locate it.

Property Sheets

We already know that the Database Objects like Forms, Reports, Tables, and others have properties and we can modify the Property Values by editing them manually at design time on the Property Sheet or change many of them through the program, during the run time. Besides these properties, there are others maintained by MS-Access and some of these are visible to us, like Name (always shown in the Table, Forms, and other Tabs of the database), Description, Created, Modified, and Object Type on the database window when you select the Icon Type as Detail for display.

Name and Description we can change the database window itself by right-clicking on the object and selecting Rename/Properties options from the shortcut menu.

Invisible Properties

There are other Properties like Owner " visible on the property sheet " Permissions, All Permissions, and others not visible but we can examine those properties with the help of the Code.

We can modify the Properties of Forms or Reports Collection Objects through VB Code only when they are loaded into Memory (when open in design view or Normal View). In such cases, we refer to the Properties in relation to the Form Object or Report Object. Examine the Menu Bar/Toolbar set-up programs in Custom Menubars & Toolbars2 Topic. There, we have opened Forms and Reports one by one in Design View to insert Names of the Menu Bars & Toolbars in their respective Properties and saved them automatically with the help of the Code.

But, there is another approach to refer to these objects. A graphical representation of these Objects' hierarchy is shown below:

Even though the above structural hierarchy appears to be somewhat difficult to understand. The relationship between one and the other, when we relate them to the Objects that we are already familiar with, but we will learn it in no time. Let us look at them from the Database Object onwards.

  1. The database we already know, let us call it Northwind.mdb
  2. Containers:

    When we look at the left side of a database (older versions on the top) we can see the above-listed Tabs, each of them we can call a Container, and all of them put together as a Group, as you have guessed, is Containers Collection. Queries are part of the Tables-Container; there is no Queries Container Object. When we want to refer to the Forms Container in the Program we will write:

    Dim db as database, ctr as Container, doc as Document
    Set db = Currentdb
    Set ctr = db.Containers("Forms")

    A dot (.) links the db (Database) and the Containers Objects together, in its hierarchical order.

  3. When we click on the Forms Tab on the database window we can see all the Forms that we have created and we call all of them together as Documents Collection.
  4. One particular Form is a Document. Individual Table, Report, and Scripts (Macros) all are referred to as Document, a member of the Documents Collection under their respective Container. To set a reference to our Employees Form (or Document) to a Document object variable, we write:

    Set doc = db.Containers("Forms").Documents("Employees")

    The Container Object reference is inserted between the Database and Document references. Or you may write:

    Set ctr = db.Containers("Forms")

    Set doc = ctr.Documents("Employees")

    After setting a reference to the Document Object Employees in doc, we can work with the Properties of the Employees Document.

Is it hard now to visualize the relationship between the elements of the diagram that we have drawn above and the database components that we see every time we open it?

Every day we work with these objects in an orderly fashion, like you open the Database first, click on the Forms Tab (Container) to display the Forms (Documents), and click on a single Form (Document) and open it. So physically you have to follow that route map to work with any single object in the database window. The only difference is we are using different class names in Programs.

Once we are able to refer to the Employees Form this way we can display the Form's Property Names and the Values they contain or create a new Custom Property and add it to the collection of existing built-in properties to store the values that we want for later use.

Displaying Property Values

First, let us write a small program to print the Employees Form's built-in properties in the debug window.

Before continuing you have to ensure that all the essential Library Files are linked to your Database, particularly Microsoft DAO x.xx Object Library, where x.xx is the Version Number of your installation.

NB: Visit the Page Command Button Animation and follow the procedure to Link the Library Files listed there (7 of them) and then come back and continue.

Copy and paste the following Code into a Global Module and save the Module:

Public Function DisplayProperties()
Dim db As Database, doc As Document
Dim i As Integer, j As Integer

On Error Resume Next

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")

   i = doc.Properties.Count

   For j = 0 To i - 1
       Debug.Print doc.Properties(j).Name, doc.Properties(j).Value

Set doc = Nothing
Set db = Nothing

End Function

Press Ctrl+G to display the Immediate Window (debug window). Click anywhere within the program and press F5 to run it. The Employees Document's property Names and Values are printed in the debug window. When running the program, while attempting to print some property values the program may stop with an error. To sidestep that problem we have added an On Error Resume Next statement to continue executing the program ignoring the Error. Examine the output in the debug window.

Creating a Custom Property

Next step, we will create a Custom Property, alongside the built-in properties that we have displayed, to store the EmployeeID Value. Copy and paste the following code into the same Global module that you have pasted the above program:

Public Function CreateProp()
Dim db As Database, doc As Document
Dim prop As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")
Set prop = doc.CreateProperty("EmpCode", dbLong, 1)
doc.Properties.Append prop

Set doc = Nothing
Set prop = Nothing
Set db = Nothing

End Function

Let us examine the code before running it. The first statement Set db = Currentdb points to the active database that we are working on. The Employees Form is a Document-Object, a member of the Documents Collection of Forms Container Object. After setting the Document reference into the doc object we call the CreateProperty() method of the Document object to create a Custom Property with the Name, Data Type & Initial Value that we have provided and stores it in a Property Object type variable prop. The new Custom property is not yet added to the Document's Properties collection. The next step calls the Append method of Properties object and the Custom Property in prop variable is passed on to the method to add it to the Properties Collection and refreshes it in the next line of code.

Click anywhere within the above Code and press F5 to run the program, to create EmpCode Property, with data type Long-Integer and with an initial value of 1.

If you will run the program again, an error message will display saying that the Property EmpCode already exists. You can now run the first program, that we have created at the beginning to display the properties of Employees Form in the debug window, to print the EmpCode property and its initial value alongside the built-in properties. If you want to create more custom properties, change the Property Name, data type constants (dbinteger, dblong, dbtext, dbdate, dbdouble, and dbsingle), and initial value for the new property and run the program again with the change.

Saving EmployeeID in Custom Property

Now, that we have created the Custom Property for EmployeeID, let us proceed with our original plan of opening the form with the last worked record.

  1. Assume that we are currently working on King Robert's (EmployeeID No. 7) Record and close the Form. Before we close the Form we must save EmployeeID 7 (or EmployeeID of the current record) into the Custom Property EmpCode.
  2. When we open the Form again, we will read the EmployeeID back from the EmpCode property and use the EmployeeID to search and find the Employee record and make that record current on the form, so that we can continue our work on that record onwards.

We need two programs on the Employees Form Module. Copy and paste the following Programs into the Employees Form Module (you may delete the existing code in the Form Module, which came with the Form when Imported), before pasting our Code) and save the Form:

Private Sub Form_Unload(Cancel As Integer)
' Author  : a.p.r. pillai
' Date    : 05/08/2007
' Remarks : Saving Data in Custom Property EmpCode
Dim db As Database, doc As Document
Dim EC As Long

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")
doc.Properties("EmpCode").Value = Me![EmployeeID]

Set db = Nothing
Set doc = Nothing
End Sub

Private Sub Form_Load()
'Author    : a.p.r. pillai
'Date      : 05/08/2007
'Remarks   : Read Custom Property EmpCode Value
'          : and use it to find the Record.
Dim db As Database, doc As Document
Dim rst As Recordset, ECode As Long

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("Employees")
ECode = doc.Properties("EmpCode").Value

Set rst = Me.RecordsetClone
rst.FindFirst "EmployeeID = " & ECode

If Not rst.noMatch Then
    Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing
Set doc = Nothing
Set db = Nothing

End Sub

It is time to test our program and check whether the whole project works according to our original plan. Open the Employees Form in Normal View and click on the Record Navigator to move the records further down into a record (not necessary that it should be 7) and note down the EmployeeID and then close the Form when you are still on that record.

When you open the Form again the current record should be the one that you have noted the Employee ID.

How it works

In the Form_Load() Event procedure, first, we are reading the value stored in the EmpCode Custom property and storing it in ECode Variable. Next, we are making use of the RecordsetClone Object of the Form, a parallel recordset, that is created when a form is opened with Record Source from a Table or Query, and assigning it to the rst Recordset object so that we can make use of the object's methods like FindFirst to search and find the record that we are looking for.

A Unique identifier known as a Bookmark is created for each record every time when the Form is open with the Record Source from a Table or Query. There are two sets of them, one is in the Form-based records and another set for the RecordsetClone, and both work independently.

We cannot search for our record on the Form-based Recordset, instead, we must look for it in the RecorsetClone Object of the Form. We make use of the FindFirst method of this object to locate the record that we are looking for, with the statement: rst.FindFirst "EmployeeID = " & Ecode. We have to test and find whether the search was successful or not. So, the next statement If Not rst.NoMatch then (we are using the rst.NoMatch method of the RecordsetClone object) to test and determine the success of the search. If found successful, then read the Bookmark of the record that we have found in the RecordsetClone and use it to set the Bookmark of the Form. The record on the Form automatically moves to the record that we were working on in the earlier session and becomes current on the Form.


1 comment:

  1. [...] off, no empty record? I can show you an example on the above quoted issue at the following link: Saving data on Forms not in Table __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]


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