Problem with code to prevent duplicate entries

[ResearchRN]

I'm a research nurse in neurosurgery. I have a database of brain tumor cases. I wanted to add some code to the medical record number field in my demographics form to (1) alert the user if a medical record number they are trying to enter is already in the database and then (2) take them to that record. Here's the code:

Private Sub mrNum_BeforeUpdate(Cancel As Integer)
Dim mrn As String
Dim LinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
mrn = Me.mrNum.Value
LinkCriteria = "[mrNum]=" & "'" & mrn & "'"
'Check tblDemographics for duplicate Medical Record Number
If DCount("mrNum", "tblDemographics", _
LinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning that medical record number has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original record
rsc.FindFirst stLinkCriteria
 
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub

The problem is in line 18 "rsc.FindFirst stLinkCriteria". I get a Run-time error 3077 "Syntax error (missing operator) in expression." I've tried everything I know but I can't figure out what I'm doing wrong. Thanks.

[pbaldy]

Your variable is named LinkCriteria, not stLinkCriteria.

[ResearchRN]

Your variable is named LinkCriteria, not stLinkCriteria.

Doh! Just goes to show, it's the little things. Thanks very much

[pbaldy]

You didn't fix it, you added to the problem. The variable you set a value to is:

LinkCriteria = "[mrNum]=" & "'" & mrn & "'"

Then you use stLinkCriteria, which would have no value (unless you're setting it someplace else). Add this line and see what gets output to the Immediate window:

Debug.Print stLinkCriteria

[ResearchRN]

You didn't fix it, you added to the problem. The variable you set a value to is:

LinkCriteria = "[mrNum]=" & "'" & mrn & "'"

Then you use stLinkCriteria, which would have no value (unless you're setting it someplace else). Add this line and see what gets output to the Immediate window:

Debug.Print stLinkCriteria

Thanks, I did fix that that line as well. Also added the "Debug" line as you suggested. Works perfectly.

Since this is my first thread, how do you mark a thread as solved?

Thanks again.

[pbaldy]

Glad it worked. You can take out the Debug line, as it's only used for debugging problems. You can mark a thread solved using "Thread Tools" up at the top of the thread.