Access - Using DLookup to prevent duplicate entries across two tables only prevents entry of top value

Question

New here - fairly advanced excel user now trying to get to grips with Access.

I'm building a database for schools data based on a linked table containing most of the schools information which is managed by another team (Contact Data Linked) and a table for additional lines that I need (Additional Contact Data). The two tables are appended using AutoExec (UNION query followed by create table) to give a full list (AllSchoolsData).

The additional lines table will be updated by users through a form, and I want to prevent them being able to duplicate one of the fields (DfE) so that this remains unique in AllSchoolsData.

I have been attempting to use DLookup and have got this far:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim NewDfE As Variant
Dim stLinkCriteria As Variant

NewDfE = Me.DfE.Value
stLinkCriteria = [DfE] = NewDfE
If Me.DfE = DLookup("[DfE]", "[Contact Data Linked]", stLinkCriteria) Then
    MsgBox "This DfE number has already been allocated." _
        & vbCr & vbCr & "Please enter a unique DfE number.", vbInformation, "Duplicate DfE Number"
Me.Undo
End If
End Sub

This seems to work perfectly for the record listed at the top of "Contact Data Linked", but does not prevent entry of any of the other DfE numbers.

The code is based on solutions I found in a youtube tutorial https://www.youtube.com/watch?v=XXLXo4As61Y and in a few forums e.g. https://www.experts-exchange.com/questions/21522439/Dlookup-to-prevent-duplicate-entries.html

Any advice would be warmly appreciated!

Answer 1

You're setting your stLinkCriteria to a boolean, the resultant comparison of DfE and NewDFE. You should set it to a string that compares the two, and let the database engine process the comparison

stLinkCriteria = "[DfE] = " & NewDfE 'If [DfE] is numeric

Or

stLinkCriteria = "[DfE] = '" & NewDfE & "'" 'If [DfE] is a string

Or, even better, use parameters in your DLookUp:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim stLinkCriteria As Variant

    TempVars!NewDFE= Me.DfE.Value
    If Me.DfE = DLookup("[DfE]", "[Contact Data Linked]", "DfE = TempVars!NewDfE") Then
        MsgBox "This DfE number has already been allocated." _
            & vbCr & vbCr & "Please enter a unique DfE number.", vbInformation, "Duplicate DfE Number"
        Cancel = True
    End If
    TempVars.Remove "NewDfE"
End Sub

Further recommendations are: use Cancel = True instead of Me.Undo. Me.Undo rolls back all changes, while Cancel = True only makes you unable to submit the update (allowing people to fix the error, and then save the changes).