Check for Duplicate Values

Check for Duplicate Values...

Place the below in your modUtilities or in a new Module, remembering not to name the Module the same as the Function, and save. For more uses for this Function see My Blog.

For Numeric Data Types...

Public Function IDOccurs(strTable As String, lngID As Long) As Integer
On Error GoTo ErrHandler
'From http://www.access-diva.com/
 
     Dim rst As DAO.Recordset
     Dim db As DAO.Database
     Dim myQuery As String
 
          myQuery = "SELECT * FROM " & strTable & " WHERE YourTableID = " & lngID & ";"
 
     Set db = CurrentDb()
     Set rst = db.OpenRecordset(myQuery, dbOpenSnapshot)
 
          rst.MoveFirst
          rst.MoveLast
 
     IDOccurs = rst.RecordCount
 
Complete:
     Set rst = Nothing
     db.Close
     Set db = Nothing
  Exit Function
 
ErrHandler:
     MsgBox ("Error:" & Err.Description)
     IDOccurs = True
     Resume Complete
 
End Function

Public Function IDOccurrences(strTable As String, strField, strID As String) As Integer
On Error GoTo Errhandler
'From http://www.access-diva.com/
 
     Dim rst As DAO.Recordset
     Dim db As DAO.Database
     Dim myQuery As String
 
          myQuery = "SELECT * FROM " & strTable & " WHERE " & strField & " = '" & strID & "';"
 
     Set db = CurrentDb()
     Set rst = db.OpenRecordset(myQuery, dbOpenSnapshot)
 
         rst.MoveLast
         rst.MoveFirst
 
     IDOccurrences = rst.RecordCount
 
Complete:
     Set rst = Nothing
     db.Close
     Set db = Nothing
  Exit Function
 
ErrHandler:
     MsgBox ("Error:" & Err.Description)
     IDOccurrences = True
     Resume Complete
 
End Function