Hi all,
Getting close to having this project wrapped up thank heavens! I added a Prevent Duplicate Records Before Update (See Code Below) and it works real nicely, however, There are two issues I would like to see if I can get some help solving?
1. I can no longer edit the existing records. I am thinking there is maybe a line or two of code that can be put in the (Code Below) to allow this function to be disabled during edits? Or do I need to create a dupchk checkbox and create some function to CancelEvent if....
2. Is there a way on a bound form, to check if a duplicate exist before writing it to the table. Example, If I have in table a StateName field with Tennessee, then I hit add new record, Type in Tennessee123 it see this as an existing record as it all ready recorded it to my table that matches Tennessee. It really didnt see it as a different Tennessee, just wrote it as I typed. This may not be an issue when I actually learn how to do the unbound forms in a short while.
Thanks so much,
Dave
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SN As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SN = Me.TxtStateName.Value
stLinkCriteria = "[StateName]=" & "'" & SN & "'"
If DCount("StateName", "tbl_State", _
stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning State of " _
& SN & " is already in Database." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub