Combobox selection driving another combobox

Posted on 2011-09-08
Last Modified: 2012-05-12
Hi im using the following code which should basically only make Combo2 visible if Combo1's selection is not "insurance".  This code is on the AfterUpdate but does not work can someone point out where im going wrong?  Cheers    

Also could I add some code which drives the values that are available in Combo2's dropdown based on the User Selection in Combo1.

    If Combo1.value = "Insurance" Then
        Combo2.Visible = False
        Combo2.Visible = True
    End If
Question by:JSSenior
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 36501856
In most cases, you must refer to the correct Column in your combo's rowsource. For example:

If Combo1.Column(1) = "Insurance" Then
  Combo2.Visible = False
  Combo2.Visible = True
End If

BTW, a quicker way to write this is:

Combo2.Visible = (Combo1.Column(1) = "Insurance")

Note too that the Column collection of a Combo is zero-based, so Column(1) is the SECOND column in the combo's Rowsource.
LVL 77

Assisted Solution

peter57r earned 167 total points
ID: 36501858
You need to check the number of columns in combo1- if you have created it using the wizard then you might have a hidden ID column which is the true value of the combo.

To get combo2 to reflect the value in combo1, you need to set the rowsource of combo2.

On way to do this is to use the combo1 afterupdate event and do something like this for a number value for combo1...

Me.combo2.rowsource = "Select field1, field2 from table where fieldx = " & me.combo1

or  for a text value for combo1
Me.combo2.rowsource = "Select field1, field2 from table where fieldx = '" & me.combo1 & "'"
LVL 84
ID: 36501863
Forgot to mention:

Most UI experts agree that hiding/showing controls based on user selections is confusing and disconcerting to users. You are generally far better off enabling or disabling controls in those instancees. The exception to this would be if a control should only be shown to specific "groups" - for example, members of a Managers Group should be able to see ButtonA, but no one else should see that button. In those cases it's fine to hid controls.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.


Author Comment

ID: 36501940
Great that works, thank you.  How about the second part of the question can I also drive the list of values that will be available in the dropdown on Combo2 depending on the value in Combo1?
LVL 61

Expert Comment

ID: 36501986
Pete's solution should answer that...

Author Comment

ID: 36502030
Hi points value increased as my question had two parts.

As there is only a few options could I just list them in the VBA code?

LVL 61

Assisted Solution

mbizup earned 166 total points
ID: 36502148
You would have to make the rowsource type Value List:

me.YourCombo.RowSourceType= "Value List"
If somecondition = true then
       me.YourCombo.rowsource = "a;b;c"
       me.YourCombo.rowsource = "1;2;3"
end if

Open in new window


Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question