Cause of Duplicate Records?

[sierra7]

I have found that I have duplicates in my Products table. I am using the DLookup() function to find a ProductID by a product 'criteria' and only create a new one if can not find a match e.g.

varX=dlookup("[ProductID]","ProductsTableName", strCriteria)
if isnull(varX) then
      'Proceed to add new record and create new ProductID (autonumber)
else
      'Use ProductID currently held in varX
endif

There are about 14,000 rows in the Products table, but I have found 40 are duplicates. The table started off with about half the number of records 7 years ago and there are 5 to 10 users accessing it.

The 'criteria' is a compound of 9 fields, seven of which are foreign keys populated by selecting from combo-boxes. The other two fields are text selected from combo boxes but not limited to the list. These don't seem to be the cause of the problem i.e. the duplicate records match each other perfectly on the 'criteria' fields. (They would not be duplicates if they were different !!)

With hindsight, I should perhaps have created a unique index on theses nine fields but that seemed rather cumbersome; the code should work; it's desireable but not critical to prevent duplication of products.

I've searched previous postings for a clue as to what is going wrong but most comment is about about finding duplicates and removing them. But why do they occur in the first place?

[nico5038]

When you have no unique index defined on the unique combination of fields, then this can happen.
It's almost impossible to trace where this does occur without first defining the unique index and get an Access warning when trying to insert such a duplicate.

Best to start with a groupby query on the unique combination of fields and add a Count for another field. By testing for >1 the count will return the 40 dupes and will allow you to join with the original table to see all dupes. Remove the ones leaving the correct ones and add the unique index on the unique combination of fields!

Nic;o)

[sierra7]

Thanks for your reply Nico.

Yes, I found the dups much as you suggested but by using the Wizard! then I converted the query to 'make table' so I could favour one value over another, and replace the unwanted values in all the other tables that refered to ProductID.

It's years since I built the system, but I imaging that I didn't use a unique index because it must manage both Standard parts and Miscellaneous parts (or 'specials') on Purchase Orders and Sales Orders. The Standard parts should be unique and are easy to structure but the miscellaneous parts, no so.

No, I relied on the code and was now wondering if there are any issues with the dLookup() function being unreliable. The present system is in '97' and I am going through the code upgrading to '2003',(having to explicity state DAO etc). We had already hit the problem of charts not displaying correct data (See KB317155) and I was partly wondering if this may have been another issue with running '97' under XL.

An alternative is that I may be seeing a difference between Me.Fieldname and Me![FieldName], which would have consequences in the 'criteria' of my dLookup(). Generally, the names of all my controls are the same as the bound data within them . . . but this is probably a topic for another thread.

[nico5038]

I haven't experienced DLOOKUP() errors yet, but perhaps using fields with leading or trailing spaces could have an undesired side effect like this...

Nic;o)