Checking for duplicate entries

  • Thread starter Thread starter Susanne
  • Start date Start date
S

Susanne

I have a subform whose display is set as continuous forms. Is there an easy
way to check these values for duplicate entries? I figured it is easier to
check after all entries are made since running code for every line (up to 48
entries) would be a waist.

I want to return a message box so the user needs to check back at the
entered data.

Thanks!
 
It's best to prevent dupiicates being added rather than check for them
afterwards.
This is normally done by using a unique index in the relevant table.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Thank you. I "get" that and maybe that is exactly what I need and I'm over
analyzing and thinking it won't work. I may be overlooking something else
too that is just as simple.

I have a main form with the subform of continuous forms. I want to be able
to have them selected more than once across the main records, but only once
per EACH individual main record.

Not what I'm doing, but an example:
Suit1: piece1, piece 2, piece 4, ... (and not piece1, 2, or 4 again)
Suit2: piece 1, piece 3, piece 4, ... (not piece1, 3, or 4 again)
SuitN: piece 3, piece 4, piece 5, ... (not piece 3, 4, or 5 again)

I'm hope I'm not missing something simple. Do I need to create 2 primary
keys in the related table? One on the SuitNumber and one on the SuitPiece?
 
Not what I'm doing, but an example:
Suit1: piece1, piece 2, piece 4, ... (and not piece1, 2, or 4 again)
Suit2: piece 1, piece 3, piece 4, ... (not piece1, 3, or 4 again)
SuitN: piece 3, piece 4, piece 5, ... (not piece 3, 4, or 5 again)

I'm hope I'm not missing something simple. Do I need to create 2 primary
keys in the related table? One on the SuitNumber and one on the SuitPiece?
Click to expand...

The piece you're missing is that a table can have only one Primary Key - but
that key can consist of one field, or two fields, or even ten fields.

If you open the table in design view and ctrl-click the Suit field and the
Piece field (so that they're both highlighted) and then click the Key icon,
you will get a joint, two-field primary key. Either field can be duplicated,
but you won't be able to enter a new record that is a duplicate for the
combination.

If you don't want this to be your table's Primary Key you can use the Indexes
tool to create a unique two (or ten!) field index. Click the lightning-bolt
icon on the table design toolbar, put an index name (UniqueSuitPiece say) in
the left hand column, and SuitNumber in the right; on the next row leave the
first column blank and put PieceNumber in the second. Check the "Unique"
checkbox and save the table.
 
Back
Top