ACC1x: Sample Function to Check Whether an Entry Exists

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article demonstrates a sample function called CheckPrimaryKey() that you can use to check whether an entry in a text box already exists in the form's underlying table.

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

In the following example, the CheckPrimaryKey() function checks whether the entry in the Customer ID field on the Customers form already exists in the Customers table. The function is attached to the BeforeUpdate event of the Customer ID text box on the form.

NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore when re- creating this code in Access Basic.

1. Open the sample database NWIND.MDB. Create a new module.

2. In the Declarations section, type the following:

         Option Explicit
   

3. Enter the following code in the module:

         Function CheckPrimaryKey(MyCustID)
   

            Dim MyDB As Database, MyDyna As Dynaset
   
         ' Create a dynaset of Customer IDs from the Customers table. The
         ' function may encounter an empty dynaset. If so, the function will
         ' fail attempting to move to the first record of the dynaset, and
         ' execute the error handling routine labeled MyCustErrTrap.
   
            Set MyDB = CurrentDB()
            Set MyDyna = MyDB.CreateDynaset("SELECT [Customer ID] FROM _
               Customers WHERE [Customer ID] = '" & MyCustID & "';")
   
            On Error GoTo MyCustErrTrap:
            MyDyna.MoveFirst
   
         ' The Customer ID entered in the form is compared to the Customer IDs
         ' in the dynaset. If there is a match, display a message saying so,
         ' then cancel the event of updating the text box, and send the Escape
         ' (ESC) keystroke to undo the data entry. If there is no match, leave
         ' the Customer ID in the text box and select the next control on the
         ' form.
   
            If MyDyna![Customer ID] = MyCustID Then
               MsgBox "You've entered an existing Customer ID, please enter _
                  a new one"
               DoCmd CancelEvent
               SendKeys "{ESC}"
            End If
   
         ' This is the Error trap routine.
         MyCustErrTrap:
   
            Exit Function
   
         End Function
   
   

4. Save the module as Customer ID Test Module.

5. Open the Customers form in Design view, and select the Customer ID text box.

6. Add the following expression to the BeforeUpdate event:

         =CheckPrimaryKey([Customer ID])
   

7. Switch to Form view and choose Go To from the Records Menu, then choose New.

8. Type "ALWAO" (without quotation marks) in the Customer ID text box. The message box will appear, and when you select OK your data entry will be undone.

9. Type "ZZZZZ" (without quotation marks) in the Customer ID text box. The message box will not appear, and Microsoft Access will accept the data entry.

REFERENCES