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...
For String Data Types...
If IDOccurs("tblYourTable", Me.txtID.Text) > 0 blah, blah, blah...
If IDOccurrences("tblYourTable", "ytYourFieldName", Me.txtYourField) = 1 blah, blah, blah...
Use in an If Statement like...
Use in an If Statement like...
The difficult I do immediately, the impossible takes a little bit longer.
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
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
fMouseOverCurrent()
fHighlightRequiredControls()
Check if Table Exists
fAmortization()
Insert (or Remove) Blank Line
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.