Code for cascading combo box

[Zara D]

Hello,

My issue is the following:

In the Entry form of the attached data base, I am trying to set up the Contract and Contract Sub-type combo boxes based on the following logic:

If Procure21 = "Yes", the only option for Contract should be "JCT", and for Contract Sub-type the only option should be "Design and Build".

However, if Procure21 = "No", there should be two option for Contract - "NEC ECC" and "JCT". Then, depending on the selected value (NEC ECC or JCT respectively), separate lists for each of these should be generated in the Contract Sub-type.

In the attached file the dependency between the Contract and Contract Sub-type combo boxes is set correctly based on queries on linked tables.

I have wasted so many days trying to make the final link to the Procure 21 combo box but I am struggling. Could you please please help??

Thank you!

[ranman256]

youd have a table of the yes,no options:
yes, JCT
no, NEC ECC
no, JCT

and options for the chosen Contracts:
JCT, design & build
etc


cbo1, Proc21, gets picked,
the other combos use Proc21, in their critiera.
then refresh the other combos.

subcontract qry sql would look like:
select subContract from table where [contract]=forms!myForm!procedur21

but you must refresh after the pick:

sub Procure21_Afterupdate()
    subConract.requery
end sub

The same way youd refresh City combo, after picking state combo

sub cboState_Afterupdate()
    cboCity.requery
end sub

the city combo query sql:
select city from table where [state]=forms!myForm!cboState

[ssanfu]

Take a look at this...

Here's a link to all kinds of information and examples for Comboboxes


Your design is not the way I would create my dB, but I have my own quirks.....
I removed all of the spaces in names, renamed the PK/FK fields and removed the macros - replaced with code...

Tear this apart...... Have fun!