Cascading value lists

[Petros]

Hi all,

In the raw source of Cmbo1

SELECT DISTINCT tbl1.Text1 FROM tbl1 ORDER BY tbl1.Text1

Were tbl1 relates to the row source for Cmbo2

How do i add a SELECT DISTINCT to the raw source of Cmbo1 that refers to Cmbo3

SELECT DISTINCT tbl1.Text1 FROM tbl1 ORDER BY tbl1.Text1, SELECT DISTINCT tbl2.Text1 FROM tbl2 ORDER BY tbl2.Text1...?

Value in Cmbo1 should cascade 2 value lists..

Thanks!

 

[vbaInet]

You need to do it in code. Here's a good link on this:

http://baldyweb.com/CascadingCombo.htm

 

[John Big Booty]

To refer to a form control in a query use;

Forms!YourFormName!YourControlName

 

[Petros]

To refer to a form control in a query use;

Forms!YourFormName!YourControlName

I beleive i am using the same method.. I use the same code in the comboboxes after update event ...the only diffrence being that i want Cmbo1 to cascade 2 comboboxes.

 

[John Big Booty]

You will need to Requery your Combo 2 whenever combo 1 is changed as well.

 

[Petros]

here is the code on the after update event of Cmbo1

Me!cmbo2.RowSource = "Select tbl1.text2 " & _
"FROM tbl1 " & _
"WHERE tbl1.text1 = '" & cmbo1.text1 & "' " & _
"ORDER BY tbl1.text2;"

i dont understand how i can also add a second combo to be depending on cmbo 1...above code describes the "dependancy" of cmbo2 to cmbo1..but what about cmbo3 to cmbo1...?

...perhaps i am thinking wrong here or want to much

 

[vbaInet]

If the second and third combo have the same type of sql syntax, just requery one after the other like John Big Booty mentioned (but it has to be in a sequence).

Requery combo1
Requery combo2

 

[Petros]

ok..i wll try your suggestion, thanks!

 

[Petros]

Nope...

I see i do not explain my self clearly.

The value A in cmbo 1 should cascade a related valuelist in cmbo 2 and another related valuelist to cmbo 3

Example:

Cmbo 1. Country (USA). Cmbo 2. City (New York) Cmbo 3. Bourrogh (Bronx)

I managed Country and City but not the Bourrogh

 

[vbaInet]

As mentioned, if the sql syntax of the row source of each of the combo boxes are the same, then requery 2 then 3. If that doesn't work for you, then call the After Update events for combo 2 then combo 3. Ensure that they have the requery code.

 

[Petros]

Ok will try again...

 

[John Big Booty]

Combo 2 will need a reference to Combo 1 in it's row source, and will need to be requried any time combo 1 is changed.

Combo 3 will need a reference to combo 2 in it's row source and will need to be required any time combo 2 or combo 1 is changed.

 

[Petros]

I apologize for being such a pain..its late over “here”..or very early in the morning..

The value lists are 2 columned and i use a Single Row Source Table as described http://www.fontstuff.com/access/acctut10.htm

My previous example was wrong. The sql syntax of the row source of each of the combo boxes are not the same

In theory i want to add AND between the two syntaxes

Me!cmbo2.RowSource = "Select tbl1.text2 " & _
"FROM tbl1 " & _
"WHERE tbl1.text1 = '" & cmbo1.text1 & "' " & _
"ORDER BY tbl1.text2;"

AND

Me!cmbo3.RowSource = "Select tbl2.text2 " & _
"FROM tbl2 " & _
"WHERE tbl2.text1 = '" & cmbo1.text1 & "' " & _
"ORDER BY tbl2.text2;"

End sub

 

[vbaInet]

Even your sql syntax is slightly off. Combo1.text1 is not a valid vba code because text1 is not a method or property of a combo box. What you need is Combo1.Value

See if you understand this better (and I've highlighted bits that were corrected).

Combo1 after_update:

[COLOR=black][FONT=Verdana]Me[COLOR=Red][B].[/B][/COLOR]cmbo2.RowSource = "Select text2 " & _
"FROM tbl1 " & _
"WHERE text1 = '" & cmbo1[COLOR=Red][B].value[/B][/COLOR] & "' " & _
"ORDER BY text2;"
me.cmbo2.requery
call[/FONT][/COLOR] cmbo2_AfterUpdate

Combo2 after update:

[COLOR=black][FONT=Verdana]Me[COLOR=Red][B].[/B][/COLOR]cmbo3.RowSource = "Select tbl2.text2 " & _
"FROM tbl2 " & _
"WHERE tbl2.text1 = '" & cmbo1[COLOR=Red][B].value[/B][/COLOR] & "' " & _
"ORDER BY tbl2.text2;"[/FONT][/COLOR]
me.cmbo3.requery

I'm hoping that the field names you mentioned, text1 and text2, aren't what you've got on your db

And please Petros, avoid double posting:
http://www.access-programmers.co.uk/forums/showthread.php?t=199745

 

[Petros]

After a good night sleep i now see my errors in choice of method. I need to go back to basic and simple logic.

Thanks for your time and i will not double post any more.

 

[vbaInet]

No problemo! Just let us know how it goes.