Help and Support
 

powered byLive Search

ACC2: How to Find a Record Using a Bound Control

Retired KB ArticleThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
View products that this article applies to.
Article ID:115189
Last Review:July 8, 2002
Revision:1.0
This article was previously published under Q115189
Moderate: Requires basic macro, coding, and interoperability skills.
On This Page

SUMMARY

This article demonstrates how to use a single bound control both for finding records and entering data in a form.

You can use this technique to find whether the value entered in a field already exists in another record. If the value does exist in another record, the record containing that value will be displayed. If the value does not exist in another record, you can continue entering data for the current record.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.

Back to the top

MORE INFORMATION

The Microsoft Access "User's Guide" demonstrates a method that you can use to find records in your database by selecting a key value from a list. (See "Finding a Record by Selecting a Value from a List.") This method is limited in that it requires two controls. One control is an unbound control that is used to select the value to look for, and the other control is a bound control in which data entry for that value occurs.

The following example demonstrates how to use a single, bound control to achieve the same functionality.

NOTE: The field to which the control is bound cannot be a required field and it cannot have a validation rule that allows null values. Make sure that the field's Required property is set to No and that its ValidationRule property does not allow null values.
1.Open the sample database NWIND.MDB. Create a new module with the following declarations and functions: 
      ' *******************************************************
      ' DECLARATIONS SECTION
      ' *******************************************************
      Option Explicit
      Dim Found

      Function Find_BeforeUpdate (F As Form)
         Dim RS As Recordset, C As Control
         Set C = Screen.ActiveControl
         Set RS = F.RecordsetClone

         On Error Goto Err_Find_BeforeUpdate

         ' Try to find a record with a matching value.
         Select Case RS.Fields(C.ControlSource).Type
            ' Find using numeric data type key value?
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
            DB_DOUBLE, DB_BYTE
               RS.FindFirst "[" & C.ControlSource & "]=" & C
            ' Find using date data type key value?
            Case DB_DATE
               RS.FindFirst "[" & C.ControlSource & "]=#" & C & "#"
            ' Find using text data type key value?
            Case DB_TEXT
               RS.FindFirst "[" & C.ControlSource & "] = """ & C & """"
            Case Else
               MsgBox "ERROR: Invalid data type for '" & C.Name & "'!"
               DoCmd CancelEvent
               Exit Function
         End Select

         ' If a record was found, save the found record's bookmark.
         If RS.NoMatch Then
            Found = Null
         Else
            Found = RS.Bookmark
         End If

         ' If the record was found...
         ' ...cancel the BeforeUpdate event
         ' ...undo changes made to the current record
         ' ...and TAB to the next control to trigger the OnExit routine.
         If Not IsNull(Found) Then
            DoCmd CancelEvent
            SendKeys "{ESC 2}{TAB}", False
         End If

         Exit Function

      Err_Find_BeforeUpdate:
         MsgBox "ERROR: Err " & Err & ": " & Error$, 48
         DoCmd CancelEvent
         Exit Function

      End Function

      Function Find_OnExit ()
         ' If the record was found, cancel the OnExit routine to stay
         ' in the control and go find the record.
         If Not IsNull(Found) And Found <> "" Then
            DoCmd CancelEvent

            ' Synchronize the form record with the found record.
            Screen.ActiveForm.Bookmark = Found

            Found = Null
         End If
      End Function
2.Open the Customers form in Design view. Change the following properties for the Customer ID field: 
      BeforeUpdate: =Find_BeforeUpdate(Form)
      OnExit: =Find_OnExit()
3.View the form in Form view. In the Customer ID field, type "AROUT" (without quotation marks) and then press ENTER. Microsoft Access will find and display the Around The Horn customer.
4.In the Customer ID field, type "POPSI" (without quotation marks) and then press ENTER. Since this key value does not exist, you can continue entering data for the record.

Back to the top

How the Sample Functions Work

The Find_BeforeUpdate() function uses the FindFirst method to search the dynaset the form is based on to see if the value entered in the control exists in the table.

If the value does not exist, the global variable Found is set to NULL and the function exits. If the value does exist, the global variable Found is set to the bookmark of the found record to be used by the Find_OnExit() function.

Before the found record can be presented, the BeforeUpdate event must be canceled, and a SendKeys action must send two ESC keys to undo changes to the current record.

Next, the SendKeys action sends a TAB key to exit the field. This event triggers the Find_OnExit() function, which checks to see if the find was successful. If it was, the CancelEvent action is run to prevent exiting the control, and then the form record is synchronized with the found record by setting its bookmark equal to the bookmark of the found record. Found is then reset back to NULL.

Back to the top

REFERENCES

Microsoft Access "User's Guide," version 2.0, Chapter 25, "Using Macros and Forms," pages 651-652

Back to the top

APPLIES TO
•Microsoft Access 2.0 Standard Edition

Back to the top

Keywords: 
kbhowto kbusage KB115189

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.