Dependent Combo Boxes in Continuous Form

[hominamad]

Hi,

I am trying to come up with a solution to the following scenario:

I have a continuous form on which two of the fields are "Transaction Type" and "Sub Transaction Type". When the user selects a Transaction Type, I dynamically change the contents of the sub type next to it and it works beautifully. The problem arises when I come back to view the form later. Access doesn't seem to be able to populate the second dropdown in my scenario. It would have to set the recordsource of that one on the fly and could conceivably be different for each row. Instead, it only populates with the intial row source, causing any sub types that are not part of that to show up as blank.

Does anyone have a solution or workaround to this issue? I would imagine this is a very common type of layout.

Thanks,

H

[hominamad]

Updated to add: I did some more digging around and found the solution where you overlay a textbox over the combo box. It seems to work ok. I'm still interested to know if there is a better way to accomplish this though.

[Jim Doherty]

Hi,

I am trying to come up with a solution to the following scenario:

I have a continuous form on which two of the fields are "Transaction Type" and "Sub Transaction Type". When the user selects a Transaction Type, I dynamically change the contents of the sub type next to it and it works beautifully. The problem arises when I come back to view the form later. Access doesn't seem to be able to populate the second dropdown in my scenario. It would have to set the recordsource of that one on the fly and could conceivably be different for each row. Instead, it only populates with the intial row source, causing any sub types that are not part of that to show up as blank.

Does anyone have a solution or workaround to this issue? I would imagine this is a very common type of layout.

Thanks,

H

Yes it is... you simply set the rowsource of the second dropdown (sub type) to the field in the table (whatever table that is) that you require to see in other words without any restrictive criteria as the default rowsource. (in other words actually typed into the rowsource by default not set at runtime by reference to any other control if you get me)

ie it needs to be this: Row Source = "Select MyField From MyTable;"

The dropdown should then correctly display the value it holds provided you should have a one column count outputting in the sub type dropdown.

The dynamic alteration you speak about in setting its rowsource on the fly should occur when the second sub type control receives the focus where you then are altering its rowsource in code to display only values in a list commensurate with values that match the transaction type selected from the first dropdown.

You should have something like this in the On_GotFocus event of the sub type dropdown

If IsNull(Me!TransactionTypeDropdownName) then
Me!TransactionTypeDropdownName.setfocus
exit sub
end if
'the above end if block merely prevents the cursor entering the sub type field
' and places the cursor instead in the transaction type so in other words
'the user cannot advance and must select a transaction type before sub 'type
 
Me!SubType.RowSource = "Select MyField From My Table Where TransactionType='" & Forms!YourFormName!YourTransactionTypeDropdownName & "'"

Hope this helps

Regards

Jim

[hominamad]

Thanks Jim. I will try this out.