SAVING DATA ON FORMS NOT IN TABLE

Introduction.

We typically store data in Tables, even though we interact with them through Forms. Unlike Tables, Forms are not designed to store data themselves. However, Forms can hold certain useful values independently, without relying on a data table.

Let’s consider a practical example where this capability becomes valuable. Suppose you want the Employees form to open in data editing mode (not data entry mode), and automatically display the last record you were working on during your previous session. This would let you resume your unfinished work right where you left off. To make this happen, we need to save the key identifying information of that last record on the Form before it is closed.

Import the Employees table and Employees form from the NorthWind.mdb sample database. This file is typically located in one of the following folders, depending on your version of Microsoft Office:

• MS Office 2000:
  C:\Program Files\Microsoft Office\Office\Samples

• MS Office 2003:
  C:\Program Files\Microsoft Office\Office11\Samples

Before proceeding, make a copy of the NorthWind.mdb file and place it in your current working folder. This ensures that you have easy access to the file whenever you need to extract forms, tables, or other sample objects from it.

Property Sheets.

We already know that database objects such as Forms, Reports, Tables, and others have properties that can be modified. These properties can either be edited manually at design time using the Property Sheet or programmatically changed at runtime.

In addition to these editable properties, Microsoft Access maintains other internal properties for each object. Some of these are visible in the database window when the view mode is set to "Details", including:

• Name

• Description

• Created

• Modified

• Object Type

Among these, Name and Description can be easily modified directly in the database window. Just right-click on the object, and choose Rename or Properties from the shortcut menu.

Invisible Properties

There are additional properties, such as Owner—which is visible on the Property Sheet—as well as Permissions, AllPermissions, and several others that are not directly visible. However, we can examine and access these hidden properties using VBA code.

We can modify the properties of Form or Report objects through VBA code only when they are loaded into memory, either in Design View or Normal View. In such cases, these properties are referenced to the Form or Report object itself. For example, in the Custom Menu Bars & Toolbars 2 topic, we examined a program that opens Forms and Reports one by one in Design View, updates their Menu Bar and Toolbar property settings, and then saves the changes automatically using VBA.

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

Although the structural hierarchy mentioned above may initially seem a bit complex, the relationships between the components become easier to grasp when we relate them to familiar database objects. With a little practice, understanding these connections will come naturally. Let’s begin by examining the structure starting from the Database Object.

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

   When we look at the left side of the database window (or at the top, in older versions), we can see several tabs representing different object types—each of these is referred to as a Container. Collectively, these make up the Containers Collection. It’s worth noting that Queries are stored within the Tables Container; there is no separate Queries Container object. When we want to refer to the Forms container in code, we would use:

   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 Container Objects together, in its hierarchical order.

3. When we click on the Forms tab in the database window, we see a list of all the Forms we have created. Collectively, these are referred to as the Documents Collection under the Forms Container. Each individual form is referred to as a Document. Similarly, individual Tables, Reports, and Macros are also referred to as Documents, each belonging to the Documents Collection of their respective containers.

4. To set a reference to our Employees form (i.e., the document named "Employees") using 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 still difficult to visualize the relationship between the elements shown in the diagram above and the actual database components we see every time we open Microsoft Access?

Every day, we work with these objects in an orderly fashion. For example, you first open the database, then click on the Forms tab (which represents the Container) to display all available Forms (referred to as Documents), and finally select and open a specific Form (Document). Physically, this is the route you follow to access and work with any individual object in the database window. The only difference, when programming, is that we refer to these same elements using their corresponding class names in code.

Once we can reference the Employees form this way, we can display its property names along with their corresponding values. Additionally, we can create a custom property and add it to the collection of existing built-in properties. This allows us to store and retrieve custom values from the form 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, 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
   Next

Set doc = Nothing
Set db = Nothing

End Function

Press Ctrl+G to open the Immediate Window (also known as the debug window). Click anywhere within the program code and press F5 to run it. The property names and their values of the Employees document will be printed in the debug window.

During execution, the program might encounter errors while attempting to print certain property values. To avoid interruptions, we have included the statement On Error Resume Next, which allows the program to skip over the error and continue running.

After execution, review the output displayed in the Immediate Window.

Creating a Custom Property.

Next, we will create a custom property alongside the built-in properties previously displayed. This custom property will store the EmployeeID value for future reference.

Copy and paste the following code into the same standard (global) module where you added the earlier 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
doc.Properties.Refresh

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

End Function

Let us review the code before running it:

• The first statement Set db = CurrentDb establishes a reference to the currently active database.

• The Employees Form is treated as a Document object, which is a member of the Documents collection within the Forms container.

• After assigning a reference to the target form (document) to the doc variable, we use the CreateProperty() Method to define a new custom property. This method requires the property name, data type, and initial value, and returns a Property object, stored in the prop variable.

• At this stage, the custom property is created but not yet added to the form’s Properties collection.

• To do that, we call the Append method of the Properties collection and pass the prop object to it. This action adds the custom property to the collection.

• Finally, Refresh The method of the collection object is called to update the collection, ensuring the new property is recognized and accessible.

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

If you run the program again without changes, it will display an error message indicating that the custom property EmpCode already exists.

To verify that the property was created successfully:

• Run the first program we created earlier that displays the properties of the Employees form in the Immediate (debug) window.

• You will now see EmpCode listed among the built-in properties, along with its assigned initial value.

If you wish to create additional custom properties, simply:

1. Change the property name (e.g., from "EmpCode" to "DeptCode"),

2. Adjust the data type constant (such as dbInteger, dbLong, dbText, dbDate, dbDouble, or dbSingle),

3. And set the initial value for the property.

Then, run the program with the updated values. Each new custom property you define will be stored on the form and can be accessed or updated later through code.

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 we are currently working on King Robert’s record (EmployeeID = 7) and are about to close the form. Before closing, we need to store the current record’s EmployeeID (7) into the custom property EmpCode.

2. Later, when the form is reopened, we can read the saved EmployeeID value from the EmpCode property and use it to locate and navigate back to the same record, making it the current record on the form. This allows us to resume our work exactly where we left off.

We need to add two VBA procedures to the Employees Form’s module.

Please follow these steps:

1. Open the Employees Form in Design View.

2. Open the Form’s Code Module (Right-click on the form → "Build Event" → choose "Code Builder").

3. Delete any existing code that came with the form during import (if present).

4. Copy and paste the two VBA procedures below into the form’s module.

5. Save the form after pasting the code.

Here are the two procedures:

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
rst.Close

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

End Sub

✅ Time to Test the Program

Let’s verify if the project behaves as expected:

1. Open the Employees Form in Normal View.

2. Use the Record Navigator at the bottom to move to any record further down—just note the EmployeeID of the current record (for example, 7).

3. Close the form while still on that record.

🔁 Now for the real test:

4. Reopen the Employees Form.

If everything is working correctly, the form should automatically position itself on the same record (with the same EmployeeID) that you were working on before closing the form.

🧠 How It Works

In the Form_Load() Event procedure:

1. Retrieve the Saved Record Key:

   • The value stored in the custom property EmpCode (which holds the EmployeeID of the last active record) is read and assigned to the variable ECode.

2. Access the Underlying Recordset:

   • We use the form’s RecordsetClone object, which is an internal copy of the form’s data source. This is a parallel recordset that reflects the data displayed on the form.

   • This RecordsetClone is assigned to the object variable rst.

3. Locate the Saved Record:

   • Using the FindFirst method of the recordset, we search for the record where EmployeeID = ECode.

   • If found, we set the form’s Bookmark property to that of the matching record in the clone. This synchronizes the form’s current record with the one found in the RecordsetClone.

✅ This mechanism allows the form to reopen at the same record the user was last working on, making it easy to resume unfinished work.

📌 Understanding Bookmarks in Access Forms

Every time a form is opened with a Record Source (Table or Query), each record is automatically assigned a unique identifier called a Bookmark.

• There are two separate sets of Bookmarks:

  1. Form's own recordset – used to display and navigate records on the form.

  2. RecordsetClone – an internal, parallel copy of the form’s data used in code for searching, filtering, or comparison.

Even though they point to the same data, the Form RecordsetClone maintains its own independent Bookmark values. However, you can synchronize them by assigning the 'Recordsetclone' Bookmark to the Form's Bookmark (not the other way around) — a technique often used to locate and focus on a specific record from code.

We cannot search directly on the form’s underlying recordset. Instead, we must use the form’s RecordsetClone object. To locate the specific record, we use the FindFirst method of the RecordsetClone, like this:

vba
rst.FindFirst "EmployeeID = " & Ecode

After executing the search, we check whether the record was found using:

vba
If Not rst.NoMatch Then

This tests the success of the search operation. If a match is found, we retrieve the Bookmark of the located record from the RecordsetClone and assign it to the form’s Bookmark property. This action moves the form's current record pointer to the located record, effectively returning the user to the same record they were working on during the previous session.

DataSavingForm.zip

• Edit Data in Zoom-in Window
• Animated Floating Calendar