Prevent duplicate values in multiple fields

[Guest]

I want to prevent duplicate records being inadvertently created, such as same
date, time, name, total hours, - these being critical fields in a larger
table. I run a "find duplicates every month" and remove the duplicates but
I'd rather it didn't happen in the first place. I tried using the unique
grouping of these fields in the "index" option in the table, but it didn't
work, even putting that in I could create a new record copying the data from
the previous record and the database allowed it. Also, how can I make a
pop-up warning message, if a duplicate is inadvertently attempted? Many
thanks

 

[Douglas J. Steele]

Creating a unique index was the correct thing to do. Are you sure you
created it correctly?

The problem, of course, could be if you're storing the total hours as a
single or double field. Due to floating point round-off error, one value of
32.3 might not be exactly the same as another value of 32.3 (32.29999 vs.
32.300001)

If that's the case, you may need to use techniques from Numerical Methods,
and rather than check whether x = y, use Abs(x-y) < 0.001 (or whatever
threshold you think is appropriate). You could put logic in your form's
BeforeUpdate event to use this technique to check for matching records.

 

[Guest]

No I don't think that's the problem, I must be doing something else wrong.
The table has lots of lookups to other related tables, some of which I
include in the "unique" index thingy. There are then additional queries
setting certain parameters and the main form, which is used to fill in the
required data, needs to only have one record for each separate "activity".
Because sometimes the "activity" is done again, because someone has not
checked if it has already been entered, that is where the duplication occurs.
Which is why I have to run the "find duplicates query" regularly to clear
them out.

In the unique index setup, I give the name "no duplicates" for the index
name, then choose the relevant fields from the drop down list. There are
other indexes showing in the overal index box, but they are not part of this
"unique index".

Do you have any other suggestions? I would really appreciate it.

 

[Douglas J. Steele]

If you can run the Find Duplicates Query, and it identifies the duplicates,
then you're likely correct that the problem isn't floating point round-off
error. However, I can't believe that you have an unique index on the same
fields used by the Find Duplicates Query.

Try running the following code to see the details of your indexes:

Sub ListIndexes(TableName As String)
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
For Each idxCurr In tdfCurr.Indexes
Debug.Print "Index " & idxCurr.Name & _
" (" & IIf(idxCurr.Unique, "Unique", "Not unique") & ")"
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Debug.Print
Next idxCurr

Set fldCurr = Nothing
Set idxCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing

End Sub

 

[Guest]

I don't know how to do "code". I create my databases using tables queries
and expressions in queries and design forms and reports from them. However,
somehow (I'm not not sure how) I have managed to get the index to work
HURRAH! However, now I need to create a msgbox in a macro to alert when a
form has data being entered incorrectly as per the unique index (duplicate
data on the fields). I know how to write simple macros, but I can't find a
way of setting a condition to refer to the unique index. Is there some other
way of creating a warning message when these fields contain duplicate data
(all the unique indexed fields, that is), so that they can be stopped before
filling out the whole form (otherwise the default error message pops up when
loads of data has been entered without saying where the problem actually is).

Many thanks,

 

[Douglas J. Steele]

There's no way to refer to an index in a macro that I'm aware of.

 

[Guest]

I've managed to fix the Unique Index and now need to attach a suitable
message to it to alert others if they inadvertently add duplicate data to a
new record. I've posted a new question in the hope someone can help. If you
know the answer, I would be grateful to learn how to write a macro msgbox
based on a unique index which pops up after the last of the unique fields
have been entered (rather than the less than helpful default message which
pops up at the end of filling in all the fields in the form.

Thanks.