ACC: How to Use a Bound Control to Find a Record (95/97)

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how you can use a single, bound control for both finding records and for 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 is 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 Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

The Microsoft Access online Help system demonstrates how to use the Combo Box Wizard to find records in your database by selecting a key value from a list. (See "Value List," and then "Finding a Record by Selecting a Value from a List" in the Microsoft Access Help system.) This method is limited in that it is designed to be used with a control not based on any table or query (unbound control). 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.

To use a single bound control for both finding records and for entering data in a form, follow these steps:

1. Open the sample database Northwind.mdb.



2. 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 is found, save the found record's bookmark.
         If RS.NoMatch Then
            Found = Null
         Else
            Found = RS.Bookmark
         End If

         ' If the record is 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 is found, cancel the OnExit routine to stay
         ' in the control and go find the record.
         If Not IsNull(Found) And Len(Found) <> 0 Then
            DoCmd.CancelEvent

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

            Found = Null
         End If
      End Function 

3. Open the Customers form in Design view, and set the properties for the Customer ID control as follows:




   BeforeUpdate: =Find_BeforeUpdate(Form)
   OnExit: =Find_OnExit() 

4. View the Customers form in Form view. In the CustomerID control, type "AROUT" (without the quotation marks), and then press ENTER. Note that Microsoft Access finds and displays the Around The Horn customer.



5. In the Customer ID control, type "POPSI" (without the quotation marks), and then press ENTER. Because this key value does not exist, you can continue entering data for the record.

How the Sample Functions Work

REFERENCES

For more information about this topic in Microsoft Access version 2.0, please see the following article in the Microsoft Knowledge Base:

Q115189 ACC2: How to Find a Record Using a Bound Control

Additional query words:

Keywords : kbusage FmsEvnt FmsHowto
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto