Form Bookmarks And Data Editing-3 ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued on Form Bookmarks.

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:

The discussion on Bookmarks is not complete without touching on the subject of Searching and Finding records and showing them as current-record on the 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 to the Form with the use of the 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 the Order Details table because the Order Details Table has several records with the same OrderIDs.

If we use the 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 is to select and display all the records we have retrieved and edited.

Review of Bookmarks.

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.

The Ease of Usage.

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

There is an article on this method posted a 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 a 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 a few times with the message: Sorry, not found.

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