Solved

Combobox selection driving another combobox

Posted on 2011-09-08
7
259 Views
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
    Else
        Combo2.Visible = True
    End If
0
Comment
Question by:JSSenior
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 84

Accepted Solution

by:
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
Else
  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.
0
 
LVL 77

Assisted Solution

by:peter57r
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 & "'"
 
0
 
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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:JSSenior
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?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36501986
Pete's solution should answer that...
http:#a36501858
0
 

Author Comment

by:JSSenior
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?



0
 
LVL 61

Assisted Solution

by:mbizup
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"
else
       me.YourCombo.rowsource = "1;2;3"
end if

Open in new window

     
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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