Programming & Security

Posted: 08 Nov 2009 08:24 AM PST

This is the continuation of our discussion on the usage of Form Bookmarks to revisit the records which we have already visited earlier. The links to the earlier Articles are given below:

Form Bookmarks And Data Editing

Form Bookmarks And Data Editing-2

The discussion on Bookmarks is not complete without touching the subject of Searching and Finding records and showing it as Current Record on Form using RecordsetClone.

In our earlier examples we have used the Find Control (Ctrl+F or Edit - ->Find…) to search for a record and when found save its Bookmark in an Array Variable in Memory for later use.

This time we will use a different method to find the record and bring it on the Form with the use of Bookmark of the RecordsetClone of the Form.

For this method we will use an Unbound Text Box to enter the search key value and a Command Button to click and find the record. To keep the VBA Code simple this time we will use the Customers Table rather than Order Details table, because Order Details Table have several records with the same OrderIDs.

If we use Order Details table then we have to combine the ProductID with OrderID to form a unique value to retrieve a specific record among several records with the same OrderIDs. This method we have already used in the earlier example to select and display all the records we have retrieved and edited.

As I have pointed out in the earlier Articles, when we open a Form with a Table, Query or SQL Statement each record on the Form is marked by MS-Access with a unique identification tag known as Bookmark. We can create a copy of this Recordset into memory (RecordsetClone) and work with it independently. Using the RecordsetClone, with the Form Bookmark attached to each record, we can find the required record with VBA Code using the search Key Value. Once we find the target record in memory we can read that record's Bookmark and insert it into the Form's Bookmark Property to make that record current on the Form.

But remember, you cannot read the Form's Bookmark Property Value and insert it into the RecordsetClone to find the same record in memory.

On the User's point of View all she has to do is to enter the Search Key Value (CustomerID) into the Unbound Text Box and click the Command Button next to it to find that record and bring it up on the Form.

Look at the sample VBA Code given below that runs on the Command Button Click (with the Name cmdFind) after setting the Search Key Value (CustomerID) in an Unbound Text Box with the name xFind.

Private Sub cmdFind_Click()
Dim m_Find, rst As Recordset

m_Find = Me![xFind]

Set rst = Me.RecordsetClone

rst.FindFirst "CustomerID = '" & [m_Find] & "'"

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

End Sub

The line that reads Set rst = Me.RecordsetClone copies the Form's Recordset into the Recordset Object Variable rst in Memory and the next line runs the FindFirst method of the Recordset Object to search and find the record with the given CustomerID Value.

In the next three lines we are testing whether the rst.FindFirst method was successful in finding the record or not. If found then the Bookmark of the current record in the Recordset is transferred to the Bookmark Property of the Form to make that record Current on the Form.

I have already published an Article on this method few months back with the Title: Animating Label on Search Success. You may visit that Page to copy the complete VBA Code of the Sub-Routine given above and try them out.

You must import the Customers Table and Customers Form from C:\Program Files\Microsoft Office\Office11\Northwind.mdb sample Database and modify the Form to add a Text Box and a Command Button at the Footer of the Form.

When the rst.FindFirst method finds the record and makes it current; a Label at the bottom of the Unbound Text Box will flash few times with a message indicating that the search operation was successful and that record is made Current on the Form. If the search operation failed then the Label will flash few times with the message: Sorry, not found.

This method added to the above program gives the User a quick indication whether the search was successful or not. To go to the Page to try out the Program Click here.