Prevent Duplicate Records, Query Before Creating New Records

Question

Setup: I am creating an ms access database to record the results of experiments that will be done by myself and others. I have created a form and a subform; the main form contains fields about the equipment setup and the subform contains the experiments with that equipment setup. The link master and link child are the SettingID.

Problem: Even though the fields in the main [settings] form are limited to combo-box selections, there are still numerous setting combinations. Therefore when the user is entering information into the form, they may not realize that a record with the same settings already exist and since the SettingID is an autonumber it will appear to be a unique record when in reality it isn't.

Question: I would like to run a query on the BeforeInsert event to check if a record with the same settings exist before creating this record. If the record doesn't exist, then proceed as normal; if it does exist, then alert the user and bring them to that record. I am at a loss as to how to accomplish this. What I have tried so far to do this isn't worth mentioning but I am continuing to try and figure something out until someone answers this post.

Answer 1

I believe you'll want two things, depending on how user-friendly you want the interface.

Unique Index

Add a multi-column unique index on the columns in the Settings table in which their combination of values you don't ever want to be duplicated in another row. Can read how in the link provided by KFleschner in the comments to your original post, or check out the second answer of this question which has a screenshot to go along with the steps: Can we create multicolumn unique indexes on MS access databases?. This will disallow duplicates in the Settings table.

For instance, if your settings and experiments were for computer rigs and you had a Settings table with the following columns:

SettingID, RAM_GB, CPU_GHz

Then your primary key would be (SettingID) and your multi-column unique index would be on (RAM_GB, CPU_GHz), because you only want one record with the same RAM capacity and CPU speed.

In database language, your primary key, SettingID, will be what is known as a surrogate key. And the new multi-column unique index will be what is known as a natural key. Both can be used to identify a unique row, but the primary key (the surrogate key) is what is used in any foreign key relationships, such as SettingID in your Experiments table.

This by itself will prevent the duplicate issue, since it will be enforced at the database level. However, it won't automatically make your form jump to the record with a matching natural key. Instead Access will alert the user with a message along the lines of you've entered a record that violated an index. But nothing more. The user will have to cancel out of the new record and go find the matching one himself.

Before Update Event

The Before Insert event triggers upon the first character of input for a new record. See http://msdn.microsoft.com/en-us/library/office/ff835397.aspx for details. This is too soon, you want the Before Update event. And add code to the event like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Set rst = Me.RecordsetClone
    rst.FindFirst "[SettingID] <> " & Me.SettingID & " AND [RAM_GB] = " & Me.RAM_GB & " AND [CPU_GHz] = " & Me.CPU_GHz
    If Not rst.NoMatch Then
        Cancel = True
        If MsgBox("Setting already exist; goto existing record?", vbYesNo) = vbYes Then
            Me.Undo
            DoCmd.SearchForRecord , , acFirst, "[SettingID] = " & rst("SettingID")
        End If
    End If
    rst.Close
End Sub

This code assume a few things:

1. [RAM_GB] and [CPU_GHz] are the columns of your multi-column unique index.
2. [SettingID] is the column in your primary key.

Tweak the column names and such to your needs and then you'll also have a way to prompt and auto-navigate to the existing record.