VBA - Avoid Duplicate selection (1 Viewer)

Hello,
I need some direction.
I have a table with 4 Components (1, 2, 3, 4)
Each Component can have different Deficits. I have Them in a combo box where the user can choose from each option. so CWC Component will have 1,2,3,4 option HWC will have the 1,2,3,4 option, etc. How can I restrict my form where if option 1 is already selected for CWC it cannot be selected for HWC, etc.., within the same record?
I hope you understand what I'm asking?

Thank you in advance for your help

PatAccess said:

Hello,
I need some direction.
I have a table with 4 Components (1, 2, 3, 4)
Each Component can have different Deficits. I have Them in a combo box where the user can choose from each option. so CWC Component will have 1,2,3,4 option HWC will have the 1,2,3,4 option, etc. How can I restrict my form where if option 1 is already selected for CWC it cannot be selected for HWC, etc.., within the same record?
I hope you understand what I'm asking?

Thank you in advance for your help

Click to expand...

Could you give us the names of the columns of your table, and explain how options relate to deficits ? It is not clear.

Best,
Jiri

Hello,

I have AHUComponentOrder - CWCComponentOrder - HWCComponentOrder - SFComponentOrder

Each of these are a combo Box with options (1,2,3,4), If the user chooses 1 under CWCComponentOrder, I want to restrict them so they can't use # 1 for the other ones. They'll have to use 2,3 or 4 with a Message Box saying "#1 has already been used"

I hope this is a little clearer.

Thank you for your help!

does sound like your data is not normalised so you may still have problems. Your issue is often called 'cascading combos) so google to find out more, but in principle:

if the rowsource to your first combo (called say cboComp1) is

SELECT ID, Component FROM someTable

the rowsource for your second combo (called say cboComp2) would be

SELECT ID, Component FROM someTable WHERE ID<>[cboComp1]

and the third

SELECT ID, Component FROM someTable WHERE ID<>[cboComp1] OR ID<>[cboComp2]

etc

then in the cboComp1 after update event put

cboComp2.Requery
cboComp3.Requery
etc

in the second one

cboComp3.Requery
etc