By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Dependent Combo Boxes in Continuous Form

P: 6
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
Oct 17 '07 #1
Share this Question
Share on Google+
3 Replies


P: 6
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.
Oct 17 '07 #2

Jim Doherty
Expert 100+
P: 897
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

Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me!TransactionTypeDropdownName) then
  2. Me!TransactionTypeDropdownName.setfocus
  3. exit sub
  4. end if
  5. 'the above end if block merely prevents the cursor entering the sub type field
  6. ' and places the cursor instead in the transaction type so in other words
  7. 'the user cannot advance and must select a transaction type before sub 'type
  8.  
  9. Me!SubType.RowSource = "Select MyField From My Table Where TransactionType='" & Forms!YourFormName!YourTransactionTypeDropdownName & "'"
Hope this helps

Regards

Jim
Oct 17 '07 #3

P: 6
Thanks Jim. I will try this out.
Oct 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.