Complicated Combo Box and List Problem

[campanellisj]

Hello all,
I have a two tables that I am working with, (tblMCP) contains the actual positions in my unit and (tblBasicData) with the soldiers basic info (name, rank etc..)
What I would like to do is assign the position from tblMCP to the soldier filling the position in tblBasicData, then have that position be unavalible to be selected again.
I am not sure if this is even possible and I am open to any suggestion that can be given to achieve the same results

 

[AndySuk]

Hi

It sounds like you could just not allow duplicates in the field in the underlying table (tblBasicData) that picks up the Position ID from your tblMCP. It's a little hard to know as you haven't said how your Combo and list boxes interact.

I'm assuming that you pick a soldier from your combo box and this soldier then has to be given a position within the unit and these positions are in the list which is based on tblMCP. When you write back to the tables then the value of the list (whch I hope is the ID or Primary Key of the position) is written as a foreign key into tblBasicData.

If that's the case then you could select the field in the design view of tblBasicData and select it to have no duplicates and Access should raise an error if you try and re-select that position.

Hopefully I haven't completely misunderstood and that may help but maybe fill in the details if I'm wrong.

Andy