VBA afterupdate to find and goto duplicate values

Hi, i've been trying a lot to get this one problem with my database i built (i'm a student, so learning Access and databases too) that i cannot seem to get right.



The situation is as follows:

Someone fills in a new patient into the database, and the 'chipsoftnummer' which is the number in another database. That number is unique, so i want to have it where if someone fills in a number that already exists in the database the afterupdate event will open that record in the form.



Here's the code i put in the input textbox update field:



----------------------------------------------------------

private Sub chipsoftnummer_AfterUpdate()

Dim NewCHIP As Integer

Dim stLinkCriteria As Integer

Dim custNo As Integer

'Assign the entered customer number to a variable NewCHIP

NewCHIP = Me.chipsoftnummer.Value

stLinkCriteria = "[chipsoftnummer] = " & "'" & NewCHIP & "'"

If Me.chipsoftnummer = DLookup("[chipsoftnummer]", "tbl_patient", stLinkCriteria) Then

MsgBox "Value" & NewCHIP & "exists", vbInformation, "Duplicate information"

Me.Undo

patNo = DLookup("[patientNR]", "tbl_patient", stLinkCriteria)

Me.DataEntry = False

DoCmd.FindRecord patNo, , , , , acCurrent

End If

End Sub



------------------------------------------------------------------



Table: Patient

Relevant fields:

PatientNR (primary key, automatic)

chipsoftnummer (Long integer, indexed no duplicates)



It doesn't work, simple things like hello world do work so VBA is enabled.



I've attached the corresponding part of the database (took out all non-relevant fields and tabs) if anyone wants to check it on that.



I hope someone can help solve it!

Attached Files

vbaproblem.accdb (412.0 KB)

VBA afterupdate to find and goto duplicate values