combo-box table source change 1

[ehsguy77]

I'd like to be able to change the table source of a combo box based on a value entered in another combo box on the same form. I'll call the main combo box Category and the dependent combo box SubCategory. I need to call a different SubCategory list based on the Category I choose. What would be the best way to go about this?

 

[SiJP]

On update of the first combo

ComboToChange.Recordsource = FirstCombo.Value
ComboToChange.Requery

Aircode so check syntax I think..

------------------------
Hit any User to continue

 

[RickSpr]

You may also want to check out my FAQ: faq702-681

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ehsguy77]

Thanks, RickSpr. That's actually what I did. The requery portion will help, though.

 

[jdegeorge]

Rick

I tried the code in your FAQ and it isn't working.

With ".value", it asks me to enter the parameter.

Without ".value", it won't let me set the SQL condition to the control name (i.e. ctlGroup). It does work when I set the SQL condition to the field name (i.e. Group).

Did I do something wrong?

Jim DeGeorge

 

[dRahme]

Hi, Two Tables:


Private Sub cboSubCategory_Enter()

Dim strSql As String

Select Case Me.cboCategory


Case Is = 1

strSql = "select * from table1"

Case Is = 2

strSql = "select * from table2"

End Select

Me.cboSubCategory.RowSource = strSql


End Sub

Hope that helps.

 

[jdegeorge]

dRahme

Thanks, but that's not what I've got. There are 2 related tables:

tblGroup
GroupID   GroupName
1	  OSG
2	  Systems
3	  NFCC
5	  FCM
6	  FPI
8	  Client

tblDivision
DivisionID   Division           GroupID
1            Client Services    1
2            Compliance         1
3            CPO                1
4            Institutional Ops  1
5            Margin             1
6            Systems            2
7            NFCC               3
8            FCM                5
9            FPI                6
10           Client             8

cboGroup is based on the first table and cboDivision is based on the 2nd. However, depending on the group selected in cboGroup, I want to limit the options available for selection in cboDivision.

For example, if "OSG" is selected in cboGroup, I only want to show the first 5 items in cboDivision.

If "FCM" is selected in cboGroup, I only want to display item #8 in cboDivision.

How can this be accomplished? I was trying this select statment:

SELECT tblDivision.DivisionID, tblDivision.Division, tblDivision.GroupID
FROM tblDivision
WHERE (((tblDivision.GroupID)=[forms]![frmGapInput]![cboGroup]));

It's not working 100%. If I select OSG in cboGroup, it correctly displays only the first 5 items for cboDivision, but if I change "OSG" to "FCM" in the same add/edit session, cboDivison somehow remembers "OSG" and still shows the wrong options.

I'm at a loss.


Jim DeGeorge

 

[PHV]

In the AfterUpdate event procedure of cboGroup:
cboDivision.RowSource = "SELECT DivisionID,Division,GroupID FROM tblDivision WHERE GroupID=" & cboGroup


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jdegeorge]

PHV

Yet again you come to the rescue! I tried something like that in the GROUP after update property, but still had the DIVISION control source hard coded.

With the DIVISION record source only defined in the GROUP after update property, it'll always be correct.

Enjoy the star!

Jim DeGeorge