Access 2000/2003 – Validate existing Data, VB Code

I use this to validate data I had entered in a number field in a small tracking DB I recently worked on. It came in very handy.  Make sure you replace the table and DB name with the names you are using for your DB and copy the rest as it is in the module:

Private Sub AccNumberBox_BeforeUpdate(Cancel As Integer)
On Error GoTo ACC_Tbl_Err
Dim CurDB As Database, ACC_Tbl As Recordset, SQLStmt As String

Set CurDB = CurrentDb
SQLStmt = “Select AccNumber FROM ACC_Tbl WHERE AccNumber = “”” & Me!AccNumber & “”””

Set ACC_Tbl = CurDB.OpenRecordset(SQLStmt)

If Not ACC_Tbl.EOF Then
MsgBox “This Batch Number has been entered. Please enter unique value.”, vbCritical, “Duplicate Entry”
Cancel = True
End If
ACC_Tbl.Close
Set ACC_Tbl = Nothing
Exit Sub

ACC_Tbl_Err:
MsgBox “Error is ” & Err.Description & ” Entering new Batch Number”, vbCritical, “”
Exit Sub
End Sub