Preventing Duplicate Entries

[FormerJarHead]

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:

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.

[RuralGuy]

I added code tags to your code along with some indenting. Put a MsgBox right here:

stLinkCriteria = "[BookingNumber] = " & SID
MsgBox stLinkCriteria

...and see what you have.

[FormerJarHead]

Thanks RuralGuy...I placed the MsgBox right after SID...Now a msgbox opens - [BookingNumber]=1234567 with an OK button. Then the MsgBox opens indicating there is a duplicate, and then the MsgBox opens "There should be a record." So know change other than the first MsgBox...

[RuralGuy]

The "BookingNumber" field *is* a numerical field, right? What is the field type? Long, Short, Single, Double?

[FormerJarHead]

The FE and BE of the database are both Long Integer, Number field...

[RuralGuy]

Hmm...RecordsetClone is really a property of a DAO Recordset. As a test I would try replacing:
rsc.FindFirst stLinkCriteria
'always check the NoMatch property after a Find
If rsc.NoMatch Then

With:

Me.RecordsetClone.FindFirst stLinkCriteria
'always check the NoMatch property after a Find
If Me.RecordsetClone.NoMatch Then

...and see if anything changes.

[RuralGuy]

Do you have *both* ADO and DAO libraries loaded in your References?

[FormerJarHead]

Tried changing
Me.RecordsetClone.FindFirst stLinkCriteria
'always check the NoMatch property after a Find
If Me.RecordsetClone.NoMatch Then

No change. Same thing happens as before the change. In looking at the references, I'm using Access 2010 so the ADO/DAO has been replaced with MS Office 14.0 Access database engine Object and it is ticked.

[ssanfu]

PMFJI.......

Just curious about the problem.
What happens with these modifications? (Not sure about the field names)

Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Dim SID As Long

    Dim sSQL As String   '**** added
    Dim RC As Long  '**** added

    '     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

  '**** added

       'using a recordset
        sSQL = "SELECT BookingNumber FROM tblClientInfo WHERE BookingNumber = " & SID
        Set rsc = CurrentDb.OpenRecordset(sSQL)
        If rsc.BOF And rsc.EOF Then
            MsgBox SID & " not found"
        Else
            rsc.MoveLast
            RC = rsc.RecordCount
            MsgBox "[BookingNumber] = " & rsc("BookingNumber")
        End If
        rsc.Close
  '**** added

        '        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

[FormerJarHead]

It doesn't like the Switchboard I updated yesterday. I made a copy before I made the changes. I works as it should. I put both copies on my desktop and started copying and pasting and trying the function after each paste. Everything went good until I added the new tblswitchboard items. Once I did that and tested, the form would not go to the previously entered record.

Will continue testing and redesigning the SB. If I run into other issues I will get back. In the meantime I'll finish up and if all works after everything has been corrected I'll mark this as...solved?

[RuralGuy]

I thought this was in Access?

[FormerJarHead]

It is...I just wanted to share the switchboard items. There is really nothing out of the ordinary but for whatever reason it does not like this sb. I recreated and it is up and running with no problems. If the db wasn't so big, I'd send it to you to mess with. I've inherited most of them that I have tasked with maintaining...I'm sure these could stand to be spruced up...or rebuilt!

[RuralGuy]

Thanks for the update and glad to hear you got things working again.