Preventing duplicate entry

[Kashif Naqvi]

Need a bit of help in a VBA code that isn't quite working. I am a complete novice- Just using youtube to learn VBA

I am trying to prevent a duplicate entry when one is entering data in a form.
Problem is that the code only reads the first ID but doesn't seem to go down the entire column. Although the duplicate entry doesn't get saved as in the table its indexed to not allow duplicates, but the user has no idea and gets no warning that it was a duplicate entry and hence got rejected.

I am pasting the code below- hope it's understandable. I am not at all a programer- just self learning through youtube videos- now really stuck

Private Sub txtMRID_AfterUpdate()
Dim stLinkCriteria As String
stLinkCriteria = "(txtMRID) =" & "'" & Me.txtMRID.Value & "'"

If Me.txtMRID = DLookup("(MRID)", "tblPatient", stLinkCriteria) Then
MsgBox "This patient has already been entered" _
& vbCr & vbCr & "The e-Log form will now open to input surgical procedure", vbInformation, "Duplicate Entry"

Me.Undo

DoCmd.Close acForm, "frmPatient", acSaveNo
DoCmd.OpenForm "frmWorkLog"
DoCmd.GoToRecord , , acNewRec


End If

End Sub

Thanks a lot

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

Here are just a couple of tips for you:

1. Feel free to ask any question you like, however basic you may feel it is, or even if it has been answered before. Our expert members thrive on helping you out!

2. If you prefer a dark theme to the forums, just go to the bottom left of this forum and click "Default style". You will then see a selection of themes to choose from. I like Shades of Blue.

3. If you like any of the answers you get, feel free to click the "Like" link on the bottom right hand corner of the post. If you hover over the Like link, you can even choose the type of smiley.

Above all, hang around here, have fun, learn stuff and join in.

 

[arnelgp]

create a Unique index on MRID field.

 

[Uncle Gizmo]

Although the duplicate entry doesn't get saved as in the table its indexed to not allow duplicates, but the user has no idea and gets no warning that it was a duplicate entry and hence got rejected.

The approach I use is to write an SQL Statement that counts the occurrences of the data I am entering. If it already exists in the field, then tell the user it already exists.

My blog here may be of interest:-

Prevent Duplicates - Nifty Access

Prevent Duplicates When entering data into a subform, particularly when you select The data from a combobox, then you probably don't want the user to be able to select the same item twice. You may want to count the various entries at some stage and having duplicates would just make this...

www.niftyaccess.com