SAVING DATA ON FORMS NOT IN TABLE

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 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 earlier session, to continue our unfinished work from there. To do this, we must save the key information of 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.

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 Program during run time. Besides these Properties there are others maintained by MS-Access and some of these are visible to us, like Name (always shown on 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 on the database window itself by right clicking on the object and selecting Rename/Properties options from the shortcut menu. 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 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 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 relationships, when we relate them to the Objects that we are already familiar with, you will grasp it in no time. Let us look at them from the Database Object onwards.

1. Database we already know, let us call it as 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 as 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.


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, Scripts (Macros) 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 in the middle of the Database and Document references. Or you may write:


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


After the ctr variable is assigned with the Forms Container Object.


5. 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 opens 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.

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
   Next

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 error. To side step that problem we have added a On Error Resume Next statement to continue executing the program ignoring the Error. Examine the output in the debug window.

Next step, we will create a Custom Property, along side 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

doc.Properties.Refresh

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

End Function

Let us examine the code before running them. 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 along side 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.

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

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

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

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 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 for the Form based records and another set for the RecordsetClone and both works 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 become current on the Form.

---

Edit Data in Zoom-in Window
Animated Floating Calendar