Originally Posted by
Feddy
Right, but i dont want a query to come up every time i insert a field, i want it to give a dumb error if i have already used that tag in the past.
also yes i know i messed up by using the '#' but with all of the references that already exist in my database, i would rather not rename it.
You could try this. I use it to prevent duplicate customer names (replace my references with yours)
Code:
'Check to see if record exists
Dim FNAME As String
Dim LNAME As String
Dim namecriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.recordsetclone
FNAME = Me.[First Name].Value
LNAME = Me.[Last Name].Value
Namecriteria = "[First Name]=""" & FNAME & _
""" AND [Last Name]=""" & LNAME & """"
'Check Customer Data Table table for duplicate
If dcount("*", "customer data", namecriteria) > 0 Then
'Message box warning of duplication
Select Case msgbox("Customer name already exists!" & vbnewline & _
"Click 'Yes' to view the existing customer record," & vbnewline & _
"Click 'No' to add a duplicate customer record," & vbnewline & _
"Click 'Cancel' to discard all changes.", vbyesnocancel + vbexclamation)
Case vbyes
'Go to record of original record'
Docmd.openform "customerdata", acnormal, , namecriteria
'Me.Undo
Rsc.findfirst namecriteria
'Me.Bookmark = rsc.Bookmark
Case vbno
'Allow save
Cancel = False
Forms!Customerdata.firstname.setfocus
Case vbcancel
'Stop the save and undo the form
Me.Undo
Msgbox "Add a New Customer or Select the Existing Customer From the 'Lookup Customer’ Function."
Forms!Frmcustomers.firstname.setfocus
End Select
End If
Set rsc = Nothing
End Sub