DLookup & Duplicate Entry

[itprog]

I would like to add a record to my table, tblReviews. I select a value from the combobox to fill in the name, location, and date for a person. Before I fill in those text boxes, I would like to make sure a duplicate entry does not exist for this person. On the BeforeUpdate property of the combobox I call a macro: mcrDupEntry. I have the following DLookup statement in my macro. If it is true, a message box will appear warning that there is a duplicate record.

DLookUp("[CNO]","tblReviews","[CNO]='" & [Forms]![frmReviews]![cboCNO].[column](0) & "'")

I would like to either cancel the event or go to the pre-existing record. When I choose a value from my combobox, the message box will appear if I have a duplicate value, but the values are still placed in the text boxes. The message box also keeps appearing no matter what button I click on to clear my form. I also get a validation error message that tells me a field or record is invalid. I'm not sure what I'm doing wrong. Is it the DLookup statement or my macro that are mis-behaving?

 

[itprog]

After searching through some postings on duplicate records, I found some references to using code in the Before Update event to detect duplicates. I tried the code below, but receive a compile error when it hits the line:

Dim rsc as DAO.Recordset.

I am new to using VBA, could someone help me with what may be wrong.

Private Sub cboCNO_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.cboCNO.Column(0)
stLinkCriteria = "[CNO]=" & "'" & SID & "'"

'check tblReviews table for duplicate CNO
If DCount("CNO", "tblReviews", stLinkCriteria) > 0 Then
'Undo Duplicate Entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Patient CNO" & SID & " has already been entered." _
& vbCr & vbCr & "You will now be taken to the record.", _ vbInformation, "Duplicate Record"
'Go to record of original patient
sc.FindFirrst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

 

[thart21]

itprog,

Have you selected the DAO (3.6 Object Library) under "References" in the VBA tools menu?

 

[itprog]

Thank you. That and a couple minor changes to the code fixed my problem.