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

Monday, November 9, 2009

Form Bookmarks And Data Editing-3

Continued on Form Bookmarks.

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

The discussion on Bookmarks would be incomplete without exploring how to search for and locate records using the RecordsetClone property.

In our earlier examples, we used the Find control (Ctrl+F or Edit → Find...) to locate a record and, once found, stored its Bookmark in an array variable for later use.

This time, we’ll take a different approach — we’ll find the record and bring it directly into view on the Form by using the Bookmark of the form’s RecordsetClone.

For this method, we will use an Unbound Text Box to enter the search key value and a Command Button click to 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.

When we use the Order Details table, we need to establish a relationship between the ProductID and OrderID to form a unique Key to find a specific record among several records within the same OrderID. This method was already used in the earlier example, which displays all the records we have retrieved and edited.

Review of Bookmarks.

As mentioned in earlier articles, when a Form is opened with a Table, Query, or SQL statement as its Record Source, each record displayed on the Form is assigned a unique identification tag by MS Access known as a Bookmark.

We can create a copy of this recordset in memory—called a RecordsetClone—and work with it independently of the Form. Using this RecordsetClone, and with the Form’s Bookmark attached to each record, we can locate any desired record through VBA code using a specific search key value. Once the target record is found in memory, its Bookmark can be read and assigned to the Form’s Bookmark property, making that record the current one displayed on the Form.

However, it’s important to note that this relationship works only in one direction: you cannot read the Form’s Bookmark property value and use it to locate the same record within the RecordsetClone.

The Ease of Usage.

From the User's Point of View, 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 ago, titled: Animating Label on Search Success. You may visit that Page to copy the complete VBA Code of the subroutine given above and try it 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.

The 'rst.FindFirst' statement finds the record and becomes 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 the record is 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 visit the Page and try it out, click here.