Checking for duplicate entries

[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!

 

[Dorian]

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".

 

[Susanne]

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?

 

[John W. Vinson]

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?

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.