Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Form Bookmarks And Data Editing-3

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:

  1. Form Bookmarks And Data Editing
  2. 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.


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...


Blog Archive

Recent Posts