Before Update Event Help

[bklewis]

I found some code on the Internet that I thought would help me with duplicate entries in my database. I have it entered as a before update event in a form field. The warning box works when I enter and duplicate entry but the duplicate record is not pulled up for editing afterward. Any assistance with this is appreciated.

Private Sub Alliance_Code_BeforeUpdate(Cancel As Integer)
Dim CODE As String
Dim MatchCriteria As String


Dim rsc As DAO.Recordset


Set rsc = Me.RecordsetClone


CODE = Me.[Alliance Code].Value
MatchCriteria = "[Alliance Code]=" & "'" & CODE & "'"


'Check tbl_Alliance_Codes table for duplicate Alliance Code
If DCount("[Alliance Code]", "tbl_Alliance_Codes", _
MatchCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning This Alliance Code " _
& CODE & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original alliance code
rsc.FindFirst MatchCriteria
Me.Bookmark = rsc.Bookmark
End If


Set rsc = Nothing




End Sub

[June7]

The code is supposed to reject the duplicate and open the existing record? No error message? Step debug, follow the code as it executes, see where deviates from expected behavior, fix, repeat.

Try this instead:

Set rsc = Me.RecordsetClone
CODE = Me.[Alliance Code].Value
MatchCriteria = "[Alliance Code]=" & "'" & CODE & "'"
rsc.FindFirst MatchCriteria
If Not rsc.NoMatch Then
  'Undo duplicate entry
  Me.Undo
  'Message box warning of duplication
  MsgBox "Warning This Alliance Code " _
    & CODE & " has already been entered." _
    & vbCr & vbCr & "You will now been taken to the record.", _
    vbInformation, "Duplicate Information"
  'Go to record of original alliance code
  Me.Bookmark = rsc.Bookmark
  Cancel = True
End If

[RuralGuy]

I assume the "tbl_Alliance_Codes" or a query of it is the RecordSource of the current form. If so then a .FindFirst on the RecortdsetClone will serve both functions (the DCount and the .MoveFirst). Me.UnDo clears the entire form if that is what you want to accomploish. The code actually looks like it would probably work. Have you single stepped it to see what is happening? If you want to zip it up then someone could loo0k at it. Remove any sensitive data of course.

[bklewis]

The code is supposed to reject the duplicate and open the existing record? No error message? Step debug, follow the code as it executes, see where deviates from expected behavior, fix, repeat.

Try this instead:

Set rsc = Me.RecordsetClone
CODE = Me.[Alliance Code].Value
MatchCriteria = "[Alliance Code]=" & "'" & CODE & "'"
rsc.FindFirst MatchCriteria
If Not rsc.NoMatch Then
  'Undo duplicate entry
  Me.Undo
  'Message box warning of duplication
  MsgBox "Warning This Alliance Code " _
    & CODE & " has already been entered." _
    & vbCr & vbCr & "You will now been taken to the record.", _
    vbInformation, "Duplicate Information"
  'Go to record of original alliance code
  Me.Bookmark = rsc.Bookmark
  Cancel = True
End If

Yes. I want the code to reject the duplicate and open the form with the original record so that I can add the additional information. I would like this to happen as soon as I enter the alliance code and tab out of the first entry box for that record instead of waiting until I have completed the entire record. Someone else helped me with the step debug steps but to be honest with you I do not understand what the error message is telling me nor do I understand what to do to fix it. I am an extreme novice in Access and have to search for what ever I can on the Internet. This is the best help that I have received and it is appreciated. I have attached a watered down version of my file if you don't mind taking a look at it. Thank you very much!

[bklewis]

I assume the "tbl_Alliance_Codes" or a query of it is the RecordSource of the current form. If so then a .FindFirst on the RecortdsetClone will serve both functions (the DCount and the .MoveFirst). Me.UnDo clears the entire form if that is what you want to accomploish. The code actually looks like it would probably work. Have you single stepped it to see what is happening? If you want to zip it up then someone could loo0k at it. Remove any sensitive data of course.

Yes. tbl_Alliance_Codes is the RecordSource of the form that I was trying to use. As I mentioned to June7, I am a novice and don't really understand what the single step is telling me when the error occurs. I have attached my file and really appreciate your help!

[RuralGuy]

Just so you know, when the DataEntry property of a form is True the Form will *only* display NEW records.

[RuralGuy]

Here's one that works.

[bklewis]

Thank you very much for your help!

[RuralGuy]

Glad we could help.