Prevent duplication of records in the entry form

[Gigitty]

Thanks guys!! that fixed the error. Now if I want to take the user to the record that already exists as part of this process?

 

[arnelgp]

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.RecordsetClone
    .FindFirst "UMRN = '" & Me!UMRN & "'"
    Cancel = (.NoMatch = False)
    If Cancel Then
        MsgBox "UMRN already exists"
        Me.UMRN.Undo
        Me.Bookmark = .Bookmark
    End If
End With
End Sub

 

[Gigitty]

Thanks for your prompt reply Arnel. Placed the code in but absolutely nothing happens when trying to trigger the code..

 

[arnelgp]

ooh, you have UMRN_BeforeUpdate.

copy the code from the Form's BeforeUpdate and paste it in UMRN_BeforeUpdate
replacing All the code (of UMRN_BeforeUpdate).

delete the Form's BeforeUpdate event.

 

[Gigitty]

So now it's having an issue with the Index I created on that field. Ok to remove the Index??

 

[arnelgp]

Replace Me.UMRN.Undo
With Me.Undo

 

[Gigitty]

Thanks So Much Arnel!! Syntax sensitivities always bring me undone.

Thankyou to everyone for your assistance.
Gigitty

 

[accessonly11]

dear access family
i want some more feature.
this code is working fine

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "OrderDetails", "ProductID = " & Me.ProductID & " AND OrderID = " & Me.OrderID) > 0 Then
    MsgBox "This Product already entered"
    Cancel = True
    Me.ProductID.SetFocus
    Exit Sub
End If

End Sub

i want that after entring duplicate entry, and after msg box ok button clicked, the current entry cancelled and and previous entry of same product set focused.

View attachment 102857
how it could be possible in vba.

 

[accessonly11]

dear access family
i want some more feature.
this code is working fine

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "OrderDetails", "ProductID = " & Me.ProductID & " AND OrderID = " & Me.OrderID) > 0 Then
    MsgBox "This Product already entered"
    Cancel = True
    Me.ProductID.SetFocus
    Exit Sub
End If

End Sub

i want that after entring duplicate entry, and after msg box ok button clicked, the current entry cancelled and and previous entry of same product set focused.

View attachment 102857
how it could be possible in vba.

 

[Gasman]

Look at FindFirst

Recordset.FindFirst method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[arnelgp]

here check the code in this demo.
i added code to the ProductID BeforeUpdate event.

 

[Gasman]

@arnelgp Why the timer please?

 

[arnelgp]

it's a Validation code (BeforeUpdate), so you cannot just leave the control/record it is validating (or setFocus on another control).
you need to complete the validation first.

 

[Gasman]

it's a Validation code (BeforeUpdate), so you cannot just leave the control/record it is validating (or setFocus on another control).
you need to complete the validation first.

Thank you

 

[Pat Hartman]

@accessonly11 Please do not hijack old threads to ask new questions. Start a NEW thread for each new question. If you want to refer to an existing thread, post a link.

The solution posted by @arnelgp is incomplete. There is no validation in the form's BeforeUpdate event so you can add rows without a ProductID.

 

[accessonly11]

@accessonly11 Please do not hijack old threads to ask new questions. Start a NEW thread for each new question. If you want to refer to an existing thread, post a link.

The solution posted by @arnelgp is incomplete. There is no validation in the form's BeforeUpdate event so you can add rows without a ProductID.

Thanks, i was not aware,

it's a Validation code (BeforeUpdate), so you cannot just leave the control/record it is validating (or setFocus on another control).
you need to complete the validation first.

and thanks to arnelgp and all of you, now this function is working after putting code given by arnelgp

 

[Pat Hartman]

Did arnel fix the problem I mentioned? Or do you just not care that the validation is incomplete?