Check Sub-Form for duplicate before entry

[schase02]

Hi folks,

I've got a subform (customersAnswersSub) inside of a main form (customersAnswers). Upon someone entering a new customerAnswersSub entry - I wanted it to check for duplicates first.

It has to check across 4 different fields to match first.

This is what I've got so far.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rsGlobals As ADODB.Recordset
Dim sql
Set rsGlobals = New ADODB.Recordset
sql = "Select * From CustomerAnswerD where subscriptionNo=" & Me.subscriptionNo & " AND journal=" & Me.Journal & " AND volume=" & Me.volume & " AND issue=" & Me.issue
rsGlobals.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
If Not rsGlobals.BOF And Not rsGlobals.EOF Then
MsgBox ("Already entered")
Cancel = True
Me.Undo
End If
End Sub

it doesn't do anything - just sits there. when I close the form it'll pop up a - id already exists box.

Any idea?

thank you