MS Access - check sub-form before entry for duplicate

Question

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, i'm pretty unexperienced when it comes to Access VB.

thank you

Answer 1

it doesn't do anything - just sits there

Just checking, since you said you're inexperienced with Access ... the form update event is not triggered until the record save is attempted. That may not happen automatically as soon as the user enters data into all the fields. However, you can trigger the update by navigating to a different record in the subform, or by a method such as choosing Records->Save Record from Access' (2003) main menu.

I don't see anything wrong with your BeforeUpdate procedure. Still I would convert it use the DCount() function instead of opening an ADO recordset. (See Access' help topic for DCount)

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strCriteria As String
    strCriteria = "subscriptionNo=" & Me.subscriptionNo & " AND journal=" & Me.Journal & _
        " AND volume=" & Me.volume & " AND issue=" & Me.issue
    Debug.Print strCriteria
    If Dcount("subscriptionNo", "CustomerAnswerD", strCriteria) > 0 Then
        MsgBox ("Already entered")
        Cancel = True
        Me.Undo
    End If
End Sub

That assumes your table's subscriptionNo, journal, volume, and issue fields are all numeric data types. If any of them are text type, you will need to enclose the values in quotes within strCriteria.

I added Debug.Print strCriteria so you can view the completed string expression in the Immediate Window. You can also troubleshoot that completed string by copying it and pasting it into SQL View of a new query as the WHERE clause.

Also, consider adding a unique index on subscriptionNo, journal, volume, and issue to your CustomerAnswerD table design. That way you can enforce uniqueness without relying solely on your form to do it. The index will also give you faster performance with the DCount function, or your original recordset SELECT statement.

If you keep your original recordset approach, close the recordset and set the object variable = Nothing before exiting the procedure.