BEFORE UPDATE PREVENT DUPLICATE

[Michelle Michy]

Hi all.
I have the following code

Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

'*********************************
'Code sample courtesy of srfreeman
'*********************************

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value
    stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber
    If DCount("strStudentNumber", "tblStudentDetails", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Student Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

It helps prevent duplicate but does not take me to existing record. Instead I get the following error

Run-time error '3420'
Object invalid or no longer set.

I can't figure out where I did wrong. Please kindly help me.

 

[gemma-the-husky]

Can't you use the database to prevent this by adding a suitable unique key to the student details table?

I presume the RSC actions are failing, but really you need a way of achieving what you want without that construction, perhaps

Maybe you don't want to be manipulating the recordsetclone.at that point. I actually struggle to understand what you are doing that is even leading to a duplicate entry.

 

[arnelgp]

sorry but what is SID?
you should use strStudentNumber for your validation?
also, you can't go to the existing record since you are in Validation event.

Dim bm As Variant

Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)
'*********************************
'Code sample courtesy of srfreeman
'*********************************

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value
    stLinkCriteria = "[strStudentNumber]=" & "'" & [strStudentNumber] & "'"

    'Check StudentDetails table for duplicate StudentNumber
    rsc.FindFirst stLinkCriteria
    If Not rsc.NoMatch Then
        Cancel = True
        'Message box warning of duplication
        MsgBox "Warning Student Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'arnelgp
        'we can't go to the record right now
        'since we are in Validation event
        'just save the bookmark first
        bm = rsc.Bookmark
        Me.Undo
   
        Me.TimerInterval = 50
    End If

    Set rsc = Nothing
End Sub

'arnelgp
Private Sub Form_Timer()
'immediately kill the timer
Me.TimerInterval = 0
'goto the record
Me.Bookmark = bm

End Sub

 

[Michelle Michy]

sorry but what is SID?
you should use strStudentNumber for your validation?
also, you can't go to the existing record since you are in Validation event.

Dim bm As Variant

Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)
'*********************************
'Code sample courtesy of srfreeman
'*********************************

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.strStudentNumber.Value
    stLinkCriteria = "[strStudentNumber]=" & "'" & [strStudentNumber] & "'"

    'Check StudentDetails table for duplicate StudentNumber
    rsc.FindFirst stLinkCriteria
    If Not rsc.NoMatch Then
        Cancel = True
        'Message box warning of duplication
        MsgBox "Warning Student Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'arnelgp
        'we can't go to the record right now
        'since we are in Validation event
        'just save the bookmark first
        bm = rsc.Bookmark
        Me.Undo
  
        Me.TimerInterval = 50
    End If

    Set rsc = Nothing
End Sub

'arnelgp
Private Sub Form_Timer()
'immediately kill the timer
Me.TimerInterval = 0
'goto the record
Me.Bookmark = bm

End Sub

Run-time error '3159'

Not a valid bookmark

 

[arnelgp]

i dont hqve problem with the code.

 

[Michelle Michy]

i dont hqve problem with the code.

Please kindly send me that sample database. I might have missed something. Please

 

[arnelgp]

the form is datasheet, dsStudentDetails.