Combo Box value depends on other Combo box selection 1

[rukk]

hi
I have two combo boxes in my form. When the user selects in first combo box the second combo should have the corresponding values.
Eg:
First combo has 4 value list
1.Type
2.Channel
3.Status
4.Group
Second combo should show all the types that are available when user selects "Type" in first combo.

And i have written a code in First combo's Before Update event as follows
If (Combo1 = "Type&quot Then
Combo2.AddItem "EMAIL"
Combo2.AddItem "MAIL"
Combo2.AddItem "WEB"
Else
If (Combo1 = "Status&quot Then
Combo2.AddItem "TARGETED"
Combo2.AddItem "Aproved"

End If

But when i select "Type" in first combo i am getting all three in to second combo, but when i select "Status" its showing all three from Type and twp from Status. So all together five are there in combo2. So what i need is if i select Status it should show only two that i have, if i selct Type it should show only 3 that i have. Any help?

 

[BSman]

Set up a table "ValueList" with the columns Value1 and Value2. Value1 will contain the text you list as allowable values for combo1. Value2 will contain each allowable value associated with each combo1 value. For example, rows could be (based on your example):

Value1 Value2
Type EMAIL
Type MAIL
Type WEB
Status TARGETED
Status Approved

For your first combo box, use a source that is a grouping query (SELECT Value1 FROM ListValues GROUP BY Value1). This will list each acceptable Type only once.

For your second combo box, use a source that is a select query based on the value in the Combo1. (something like: SELECT Value2 FROM ListValues WHERE Value1 = cboCombo1).

For the AfterUpdate property of Combo1, put the code:
me.cboCombo2.Requery

This will also make it easy to modify the values.

As an alternative, you could even show both Value1 and Value2 for Combo1 and just save the reference ID (an added column in the ListValues table that would be an autonumber column). That way you could correct spellings, etc, of data in the lookup ListValue table and they would be reflected accurately in the future.

 

[kphu]

Your statement should be.

Combo1.value = "Type"
etc.


Another option that I've done is create tables for each of the possible data that the combo box's value. This way it'll allow you to edit and remove the values.

First combo has 4 value list
1.Type
2.Channel
3.Status
4.Group

2nd combo boxes shows the values that's associated to the 4 value lise in combo 1.

Example

For "Type" the list options are:
Email
Mail
Type

For "Status" the list options are:
Targeted
Approved
etc

So you create a table each for the possible values of "Type", "Status", "Channel", "Group"

On the 2nd combo box, on the on enter property the code should be.

Select Case Combo1.value
case "Type"
combo2.rowsource = "Name of table holding values for Type"
case "Status"
combo2.rowsource = "Name of table holding values for Select"
case "Channel"
combo2.rowsource = "Name of table holding values for Channel"
case "Group"
combo2.rowsource = "Name of table holding values for Group"

 

[kphu]

Your statement should be.

Combo1.value = "Type"
etc.


Another option that I've done is create tables for each of the possible data that the combo box's value. This way it'll allow you to edit and remove the values.

First combo has 4 value list
1.Type
2.Channel
3.Status
4.Group

2nd combo boxes shows the values that's associated to the 4 value lise in combo 1.

Example

For "Type" the list options are:
Email
Mail
Type

For "Status" the list options are:
Targeted
Approved
etc

So you create a table each for the possible values of "Type", "Status", "Channel", "Group"

On the 2nd combo box, on the on enter property the code should be.

Select Case Combo1.value
case "Type"
combo2.rowsource = "Name of table holding values for Type"
case "Status"
combo2.rowsource = "Name of table holding values for Select"
case "Channel"
combo2.rowsource = "Name of table holding values for Channel"
case "Group"
combo2.rowsource = "Name of table holding values for Group"



Currently seeking for position around Boston, MA. kenphu@yahoo.com

 

[rukk]

Thank you so much. I have one more problem.
I have created one new table with Value1 and Value2. And that combo box problem solved. But now i need to run the query based on that selection. What command do i need to run to display the result.
I tried to build a query in Access VBA like
SqlStmt="select name,number,id from table1 where "
SqlStmt=SqlStmt & Combo1.value & "="
SqlStmt=SqlStmt & Combo2.value

But which command i should use to run the Select Query, because i have used

Docmd.runsql(SqlStmt)

But this RunSql is for action queries. Please help?
If any other option than building this SqlStmt Please let me know.
Thanks

 

[BSman]

Why not just create a standard query in Access (using the Access query builder) and include the criteria that limits the records returned to those where the column in the table that corresponds to the value selected in Combo1 is equal to the value of Combo1 on the form and the column for Combo2 is equal to the value of Combo2. Or, if you made only one Combo box that listed both values, in place of being equal to the value of Combo2, that column would be equal to Combo1.Column(1). (Note that the columns returned for a multi-column combo box are numbered as 0, 1, 2, etc., rather than 1, 2, 3, etc.)

That query should be the source for your form. Once the user has selected the values for Combo1 and Combo2, get the form to requery (me.requery). There's no need to rebuild a query each time a user selects new criteria.

 

[rukk]

Actually, what i want to do is i will have two Combo boxes on a form and a "GO" command button. When the user selects the criteria and clicks the GO button it should run the query. Yea, i can write a query in Query builder and open that query from the code of VBA. But i have eight options in Combo1 and each have again 7 to 8 options for Combo2. So its like writting too many queries and opening them from code. Sorry if i misunderstood your reply or if i am not explaining good, if you don't mean to say writting so many queries, please give me a small example of how you want me to build. Thank you.

 

[BSman]

You only need one query, no matter how many optional values and combinations of values there are.

Using the Access Query Builder, create a query (I'll call it MyQuery) that is a select query that selects all columns from the source table.

In the grid row called "Criteria", right click in the box under the column that corresponds to the value in Combo1. Select Build. Go to Forms and select the current form in the bottom left area. In the middle area select the control Combo1, then select OK. Do the same for the column corresponding to Combo2, except select the control Combo2 (obviously!). Save the query.

I assumed that you were using the query as the data source for a form. If so, either open the form when the user clicks the GO button or, if it is the current form, force the requery of the source data for the form (as I mentioned earlier).

You're trying to make a complicated solution when it isn't necessary. Be lazy and re-use the one query, not write a whole bunch of them.

 

[rukk]

Thank you so much for your help and Time.
I have done as you said. The Columns for both combo boxes are same because the combo2 has the value and combo1 has the column. SO i have done the above process for both the Combo's. And i have assigned this query to the Command button. But i got a problem, when i selct in two of the above combos' and hit the command button no data. Just field names are coming. And i am retriving data from three diffrent tables, is that what's effecting.

Thanks

 

[BSman]

I'm not sure why you would set it up the way it appears you have, but based on your description Combo1 is supposed to specify the column which will be used for the value selected in Combo2. Here are some ideas:

Keeping the concept as you've described it, for the AfterUpdate property of Combo1 you can make a set of if then statements that will set the query source for Combo2 based on the value (column selected) of Combo1. So you'd have statements something like this:

If me.cboCombo1 = "FirstColumn" then
me.cboCombo2.RowSource = "qselDataFromFirstColumn"
me.requery
end if

You could put the "me.requery" statement after the if..then statements instead of in each statement. It's necessary to force Access to re-create the data for the Combo2 combo box.

An alternate method is to change your source table. Have two columns. I'll call them colCombo1 and colCombo2. colCombo1 would contain the data corresponding to what you currently have in Combo1, but repeated whatever number of times are necessary so there are enough rows in the table to contain all of the values of Combo2 that apply for the one value of Combo1. Something like this:

colCombo1 colCombo2
C1Val1 Val1
C1Val1 Val2
C1Val1 Val3
C1Val1 Val4
C1Val2 Val11
C1Val2 Val12
C1Val3 Val31
C1Val3 Val32
C1Val3 Val33

This way, Combo2 can have it's source from this table but with a query where colCombo1 = the value of Combo1 selected by the user.

 

[rukk]

yep, thank you so much it worked for me. Thank you once again.