ACC1x: How to Find a Record Using a Bound Control (1.x)

ID: Q105660


The information in this article applies to:


SUMMARY

This article demonstrates how to use a single bound control for both data entry and for finding records 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, the record containing that value will be displayed. If the value does not exist, you can continue entering data for the new 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 "Introduction to Programming" manual.


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:

  1. Open the sample database NWIND.MDB. Create a new module with the following declarations and functions:
    
          ' *******************************************************
          ' DECLARATIONS SECTION
          ' *******************************************************
          Option Explicit
          Dim Found
    
          Function FindBeforeUpdate ()
             Dim DS As Dynaset, C As Control
             Set C = Screen.ActiveControl
             Set DS = Screen.ActiveForm.Dynaset
    
             On Error Resume Next
    
             ' Try to find a record with a matching entry in
             ' the form's dynaset as a numeric field
             DS.FindFirst "[" & C.ControlName & "]=" & C
    
             ' If it was not a numeric field (error 3070) then
             ' try finding the record as a text field
             If Err = 3070 Then
                Err = 0
                DS.FindFirst "[" & C.ControlName & "]='" & C & "'"
             End If
    
             On Error GoTo 0
    
             ' If any unexpected error happened, present to user and exit
             If Err <> 0 Then
               MsgBox Error(Err), 48
               DoCmd CancelEvent
               Exit Function
             End If
    
             ' If we found a record, save the bookmark of the found record
             If DS.NoMatch Then
                  Found = Null
             Else
                  Found = DS.Bookmark
             End If
    
             ' If we found the record...
             ' .. Cancel the BeforeUpdate
             ' .. Undo changes made to the current record
             ' .. TAB to the next control to trigger the OnExit routine
             If Not IsNull(Found) Then
                DoCmd CancelEvent
                SendKeys "{ESC 2}{TAB}", False
             End If
          End Function
    
          Function FindOnExit ()
             ' If we found the record, cancel the OnExit event to
             ' keep us in the control and go find the record
             If Not IsNull(Found) And Found <> "" Then
                DoCmd CancelEvent
    
                ' Synchronize form record with found record
                Screen.ActiveForm.Bookmark = Found
    
                Found = Null
             End If
          End Function 


  2. Open the Categories form in Design view. Change the following properties for the Category ID field:
    
          BeforeUpdate: =FindBeforeUpdate()
          OnExit: =FindOnExit() 


  3. Switch to Form view. The first category that appears is the Beverages category. Type COND in the Category ID field and press TAB or ENTER. Microsoft Access will automatically find and present the Condiments category record.


  4. Type NEW in the Category ID field and then press TAB or ENTER. Since this key value does not exist, no record is presented and you can continue to enter data for the new record.


How the Sample Functions Work

The FindBeforeUpdate() 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 FindOnExit() function.

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

Next, a SendKeys action sends a TAB key to exit the field. This event triggers the FindOnExit() 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.


REFERENCES

Microsoft Access "User's Guide," version 1.0, pages 547-548

Microsoft Access "User's Guide," version 1.1, pages 551-552


Keywords          : kbusage GnlDe 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: March 27, 1999