Access技术问询：DLookup跨两表防重复仅拦截首个值问题

Hey Matt, great job setting up that school database with linked tables and AutoExec—you’re already thinking like an Access pro! Let’s sort out that duplicate detection issue where DLookup only blocks the first duplicate entry.

Why DLookup Isn’t Cutting It #

DLookup is designed to return a single matching value, not count all matches. So if your check only scans one table first, or if duplicates are spread across both your linked and additional tables, it’ll miss entries after the first match. That’s why you’re seeing it only block the first duplicate.

Better Solutions to Block Duplicates #

Here are a couple of reliable ways to fix this:

1. Use a Combined Query + DCount Instead of DLookup

First, create a union query that pulls the unique identifier (like School ID or Name) from both your tables. Let’s call this qryAllUniqueSchools:

SELECT SchoolID, SchoolName FROM [Contact Data Linked]
UNION SELECT SchoolID, SchoolName FROM [Additional Contact Data];

The UNION operator automatically removes duplicates, so this query will list every unique school from both sources.

Then, use DCount (which counts matching records) in your form’s BeforeUpdate event to check if the entry already exists:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Replace SchoolID with your actual unique field name
    Dim checkCriteria As String
    checkCriteria = "[SchoolID] = '" & Me.SchoolID.Value & "'"
    
    ' Check if the school exists in either table via our union query
    If DCount("*", "qryAllUniqueSchools", checkCriteria) > 0 Then
        MsgBox "This school already exists in either the linked contact data or your additional table—no duplicates allowed!", vbExclamation
        Cancel = True ' Stop the save from happening
        Me.SchoolID.SetFocus ' Send the user back to correct the entry
    End If
End Sub

DCount will return a number greater than 0 if any match exists across both tables, so it’ll catch all duplicates, not just the first one.

2. Add a Unique Index (Bonus Step)

For extra protection, you can add a unique index to the SchoolID (or your unique field) in your Additional Contact Data table. This will prevent duplicates at the table level, even if the form check fails:

• Open your Additional Contact Data table in Design View
• Select the field you want to enforce uniqueness on
• In the Field Properties pane, set Indexed to Yes (No Duplicates)

Why This Works #

By combining both tables into a single union query, you’re checking against every existing school in one go. DCount is perfect here because it’s built to count matches, not just grab the first one. The BeforeUpdate event ensures the check happens before the record is saved, so you can stop duplicates before they get into your table.

Give these steps a try—they should fix that partial duplicate blocking issue you’re seeing!

内容的提问来源于stack exchange，提问作者Matt87