change the content of a Combi Box.......

[rodmc]

...........based on the input of previous combi

Hi Guys this is what Im trying to do. I have a combi box with a list of staff names, depending on what is entered in the staff names combi I want to change the next combi which will contain Activities that the staff will perform.

Each staff member performs different activities. So the content of the Activities combi has to change for each staff member.

Any help would be greatly appreciated

thx

rodmc

 

[gemma-the-husky]

search for cascading combo boxes. that is the technique you want.

 

[Kryst51]

Google "Cascading Combo Boxes" and see what you come up with. Ask back here if you don't understand what you are reading, you can even limit your search to this site if you use the advanced search options.

 

[vbaInet]

See if this link helps:

http://baldyweb.com/CascadingCombo.htm

 

[rodmc]

cheers guys, that looks pretty straight forward

Thanks Guys!

 

[boblarson]

Oh, and in case it hadn't hit you with the responses - it is Combo box not combi. (just making sure you are on the same page as everyone).

 

[vbaInet]

It's kinda like a combi (aka COMBInation) box though. A mixture of a listbox, textbox and button

 

[boblarson]

It's kinda like a combi (aka COMBInation) box though. A mixture of a listbox, textbox and button

Reminds me too much of that MEN AT WORK song "Down Under" which has the lyric, "Traveling in a fried-out Combi, on a Hippie trail - head full of zombie."

 

[vbaInet]

Haha!! I had to YouTube it to remind myself

 

[rodmc]

combo it is then

So Ive got it working BUT, heres whats happened, Ive followed baldyweb's example but its thrown up a small problem for me. Baldyweb has a city for each state, but what If you were to add multiple cities for each state? Using that code you would see in the first combo, for example

Combo1
CA
CA
CA
NV
NV

Combo2
Los Angeles
San Francisco
San Diego
Las Vegas
Reno

Do you see where Im going with this? I only wnat to see CA once in the first combo. My first thoughts are split cities and states into different tables and re write the code accordingly, am I moving in the right direction?

 

[vbaInet]

All you need is the DISTINCT keyword in the row source of combo1.

See here:

http://www.techonthenet.com/sql/distinct.php

 

[rodmc]

All you need is the DISTINCT keyword in the row source of combo1.

See here:

http://www.techonthenet.com/sql/distinct.php

doh! staring me straight in the face as always"

thx vbaInet

 

[vbaInet]

You're welcome!

 

[rodmc]

Sorry vbaInet, getting a little confused, would I have to nest the SQL? as the SELECT statement would need to include SELECT DISTINCT State where as in Baldyweb he has selected SELECT City as that is what is being pulled thorugh for the content of the combo box?

 

[gemma-the-husky]

out of interest - is this a visual query

if so, right click the design pane, sleect properties, and then set "unique values" to TRUE


this is what it using "distinct" is doing - have a look at the sql and see.

you can do pretty well anything (except union queries) directly in a visual query - and its just far far easier.

 

[boblarson]

combo it is then

So Ive got it working BUT, heres whats happened, Ive followed baldyweb's example but its thrown up a small problem for me. Baldyweb has a city for each state, but what If you were to add multiple cities for each state? Using that code you would see in the first combo, for example

Combo1
CA
CA
CA
NV
NV

Combo2
Los Angeles
San Francisco
San Diego
Las Vegas
Reno

Do you see where Im going with this? I only wnat to see CA once in the first combo. My first thoughts are split cities and states into different tables and re write the code accordingly, am I moving in the right direction?

You didn't look close enough because Paul has a GROUP BY on the state combo's row source which then brings back ONE state and then you select the city from the state selected.

 

[rodmc]

out of interest - is this a visual query

if so, right click the design pane, sleect properties, and then set "unique values" to TRUE


this is what it using "distinct" is doing - have a look at the sql and see.

you can do pretty well anything (except union queries) directly in a visual query - and its just far far easier.

not sure I know what a visual query is, but I have checked out the combo box's properties and I cant see "Unique Values"

The query is embedded in the VBA and looks like this:

Private Sub cboService_AfterUpdate()
    Dim strSource As String
    
    strSource = "Select Treatment " & _
                "FROM Service " & _
                "WHERE Service = '" * Me.cboService & "' ORDER BY Treatment"
    Me.cboTrtmnt1.RowSource = strSource
    Me.cboTrtmnt1.RowSource = vbNullString
    
End Sub

 

[rodmc]

You didn't look close enough because Paul has a GROUP BY on the state combo's row source which then brings back ONE state and then you select the city from the state selected.

Is Paul Baldyweb?

I'll give that a try Bob!

 

[boblarson]

Yes, Paul is pbaldy and he has the site baldyweb.

 

[rodmc]

well got it working guys, had a few niggly problems but managed to get them sorted out over the weekend

Thx guys!