Hi All...
My saga continues... Yesterday I did some updates to our Chaplains database, I added a form and changed the Switchboard design a bit. The db is set up so that if a duplicate booking number is entered, a message box appears and redirects the user to the previous entry...(I found the code online) and it has worked just fine for several years. I can't imagine adding a form or tweaking a sb would affect this feature so something else is causing it so I'm looking to the experts for some help. Here's the code:
Code:
Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim SID As Long
Set rsc = Me.RecordsetClone
SID = Me.BookingNumber
stLinkCriteria = "[BookingNumber] = " & SID
'Check tblClient table for duplicate BookingNumber
If DCount("*", "tblClientInfo", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
Cancel = True
'Message box warning of duplication
MsgBox "Warning Booking Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Booking Number
rsc.FindFirst stLinkCriteria
'always check the NoMatch property after a Find
If rsc.NoMatch Then
' record not found
MsgBox "There should be a record"
Else
'go to the record
Me.Bookmark = rsc.Bookmark
End If
End If
Set rsc = Nothing
End Sub
What happens is this - The msgbox does what it should but when it should redirect to the existing record, the second msgbox opens...and I agree with it...there should be a record, and there is...it's just not getting me there. Can anyone help with this?
Thanks so much.