Duplicate records in a form

[Todd]

I've got a form that lists equipment. The form has many
records and within the form I've got a subform that you
can click open where you can enter companies that have the
equipment you are viewing on the main form. I've got a
combo box in the subform where you select which company
has that equipment so there is no manual entry for
selecting a company, only lists from the combo box.

My question is how can I make the subform not allow the
same company from being entered twice in the subform for
that specific record in the main form. I don't want to
have the same company entered twice on the same piece of
equipment. Help!

 

[Michel Walsh]

HI,


Preferable to do it at the table level (and would generate an error in
the onError event of the form/subform): get the table in design, have the
index sheet (modeless dialog) visible, add an index name, in the same line,
add the first column, then, in the next line of the index sheet, without
adding an index name, add the second column. That creates an index based on
the two fields you would have supplied. In the index property, at the bottom
of the index sheet, specify that the index should not allow duplicated
values. Since the index is based on two fields, to have a duplicate, both
fields are considered, as you want. You won't be able to add duplicated
couple anymore, from anywhere ( not just under your form ), unless you
remove that index (or change its definition, etc.)


Hoping it may help,
Vanderghast, Access MVP

 

[Todd]

Just checking but by doing this will I still be able to
use any of the subform combo box selections again on other
records in the main form?

 

[Michel Walsh]

Hi,

Probably, but you won't be able to save a "record" bound to values that
would create a duplicate entry in the unique index.


Vanderghast, Access MVP