Microsoft KB Archive/109374

{|
 * width="100%"|

ACC1x: Sample Function to Check Whether an Entry Exists

 * }

Q109374

-

The information in this article applies to:


 * 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.

 Open the sample database NWIND.MDB. Create a new module. In the Declarations section, type the following:

Option Explicit  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  Save the module as Customer ID Test Module. Open the Customers form in Design view, and select the Customer ID text box.  Add the following expression to the BeforeUpdate event:

     =CheckPrimaryKey([Customer ID])  Switch to Form view and choose Go To from the Records Menu, then choose New.</li> Type ALWAO in the Customer ID text box. The message box will appear, and when you select OK your data entry will be undone.</li> Type ZZZZZ in the Customer ID text box. The message box will not appear, and Microsoft Access will accept the data entry.</li></ol>