Add record only if doesn't exist in Access 200

Question

I have a table, let's say it stores staff names (this isn't the exact situation, but the concept is the same), and the primary key is set to an autonumber. When adding a a new member of staff I want to check to see if that name exists in the database before it is added, and maybe give an error if it already exists. How can I do this from the normal add form for the table?

I tried creating a query for it but that won't work because the form is based on the table and can't use a query as the control source. I saw some examples online saying how to do something like this with VB code, but I couldn't get it to work as it wasn't a simple example and some lines were left out.

Is there any simple way in which this can be done?

Answer 1

The best solution is to get the user to enter a few characters of the first and last name and show a continuous form of all the individuals based on those search criteria. Also display relevant information such as middle name, if available, phone number and address to weed out potential duplicates. Then if a duplicate isn't found then they can add the person.

There will always be two John Smith's or Jane Jones in every town.

I read of a situation where two women with the identical first, middle and last names and birth dates were in a hospital at the saem tiem. Truly scary that.

Answer 2

This is called an 'UPSERT' in the SQL world. The ISO/ANSI SQL-99 Standard defines a MERGE syntax which was recently added to SQL Server 2008 with proprietary extensions to the Standard. Happily this is the way the SQL world is going, following the trail blazed by MySQL.

Sadly, the Access Database Engine is an entirely different story. Even the simple UPDATE does not support the SQL-92 scalar subquery syntax, instead has its own is proprietary with arbitrary (unpredictable? certainly undocumented) results. The Windows team scuppered the SQL Server's attempts to fix this in Jet 4.0. Even now that the Access team has its own format for ACE they seem uninterested in making changes to the SQL syntax. So they chances of the product embracing a Standard SQL-99 -- or even their own alternative -- construct is very remote :(

One obvious workaround, assuming performance is not an issue (as always, needs to be tested), is to do the INSERT, ignoring any key failure errors, then immediately after do the UPDATE. Even if you are of the (IMO highly dubious) 'autonumber PK on every table' persuasion, you should have an unique key on your natural key, so all should be fine.

Answer 3

I'll put my vote in on the side of using an unbound form to collect the required fields and presenting possible duplicates. Here's an example from a recent app:

_{(source: dfenton.com)}

(I edited real people's names out and put in fake stuff, and my graphics program's anti-aliasing is different from ClearType's, hence the weirdness)

The idea here is that the user puts data in any of the four fields (no requirement for all of them) and clicks the ADD button. The first time, it populates the possible matches. Then the user has to decide whether one of the matches is the intended person or not, and either click ADD again (to add it, even if it's a duplicate), or click the button at the bottom to go to the selected customer.

The colored indicators are intended to convey how close the match is. In this case, the email address put in is an exact match for the first person listed, and exact match on email by itself is considered an exact match. Also, in this particular app, the client wants to minimize having multiple people entered at the same company (it's the nature of their business), so an exact match on Organization is counted as a partial match.

In addition to that, there's matching using Soundex, Soundex2 and Simil, along with substrings and substrings combined with Soundex/Soundex2/Simil. In this case, the second entry is the duplicate, but Soundex and Soundex2 don't catch it, while Simil returns 67% similarity, and I've set the sensitivity to greater than 50%, so "Wightman" shows up as a close match with "Whiteman". Last of all. I'm not sure why the last two are in the list, but there's obviously some reason for it (probably Simil and initials).

I run the names, the company and the email through scoring routines and then use the combination to calculate a final score. I store Soundex and Soundex2 values in each person record. Simil, of course, has to be calculated on the fly, but it works out OK because the Jet/ACE query optimizer knows to restrict on the other fields and thus calls Simil for a much-reduced data set (this is actually the first app I've used Simil for, and it is working great so far).

It takes a bit of a pause to load the possible matches, but is not inordinately slow (the app this version is taken from has about 8K existing records that are being tested against). I created this design for an app that had 250K records in the person table, and it worked just fine when the back end was still Jet, and still works just great after the back end was upsized to SQL Server several years ago.

Answer 4

In the table design view, you could make the Name column Indexed with No Duplicates

Then Access itself will reject the entry. I think it will however use up one of autonumbers before rejecting the input.

Answer 5

You're dealing with the issue of pre-qualifying records before inserting them into the database. Simple and absolute rules that you will never violate (like never, ever, ever allowing to records with the same name) can be dealt with through database constraints — in this case creating an index on the column in question with AllowDuplicates set to No.

However, in the real world pre-qualification is generally more complex. You may need to simply warn the user of a possible duplicate, but allow them to add the record anyway. And you may need to check other tables for certain conditions or to collect information for more than one table at a time.

In these cases you need to write your interface so it is not directly bound to the table (in Access terms, create a form with the record source empty), collect the information in various controls, perform your checks in code (often using DCOUNT and DLOOKUP) and then issue a series of INSERT and UPDATE statements in code using DoCmd.RunSQL.

You can occasionally use some tricks to get around having to code this in the front end, but sooner rather than later you'll encounter cases that require this level of coding.

Answer 6

Thanks for all the info here. It is great info and I would use it as I am self-taught.

The easiest way I found as alluded to here, is to use an index on all the fields I want (with No Duplicates). The trick here is really to use multiple indexes (this basically allows a compound index, or a "virtual" index which is made up of more than one field).

The method can be found here: http://en.allexperts.com/q/Using-MS-Access-1440/Creating-Unique-Value-check.htm, but I would repeat it in the event that the link gets removed.

From Access Help: Prevent duplicate values from being entered into a combination of fields

Create a multiple-field index using the fields you want to prohibit duplicate values for. Leave the Indexes window open when you have finished defining the index.

How?

1. Open the table in Design view.
2. Click Indexes on the toolbar.
3. In the first blank row in the Index Name column, type a name for the index. You can name the index after one of the index fields, or use another name.
4. In the Field Name column, click the arrow and select the first field for the index.
5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index. The default sort order is Ascending. Select Descending in the Sort Order column of the Indexes window to sort the corresponding field's data in descending order.
6. In the upper portion of the Indexes window, click the new index name.
7. In the lower portion of the Indexes window, click the Unique property box, and then click Yes.

You should now be able to not enter records which have the same values in the indexes. I got problems where I could still enter values if one of the indexed fields has a space (there is an option to check/ignore null values when you set up the index) but it didn't work for me, but the solution worked because I won't have null values anyway.