ComboBox,Rowsource using queries but into code I am stuck?

[hrg]

Hello there


I have a problem where i have 6 queries & three combo boxes. When selecting the first combo box (in a form) you have three options. Clicking on one of these three options will determine the information shown in the other two combo boxes. As i am using 3 queries for combo box 2 and three queries for combobox 3 i need the right query to show its results in the combobox depending on the selection on combo box 1. To accomplish this i am using an on click event and if else statements
For example

Onclick event for when i have selected an option in combo box 1 :-


Combo box two should show

if ComboBox1 = option1 then
ComboBox2 = SELECT[QUERY1].[QUERYFIELD] from query1;
else
If ComboBox1 = option2 Then
ComboBox2 = SELECT[Query2].[queryfield] from query2;
Else
ComboBox2 = SELECT[Query3].[queryfield] from query3;




As you can see i am using what should be rowsource but into code due to the three queries which all hold different data.

Please can anyone help me i am stuck.

Thanks

 

[Shortie]

In the afterupdate event of ComboBox1

Select Case ComboBox1
Case Option1
ComboBox2.RowSource = SQLString1
ComboBox3.RowSource = SQLString4

Case Option2
ComboBox2.RowSource = SQLString2
ComboBox3.RowSource = SQLString5

Case Else
ComboBox2.RowSource = SQLString3
ComboBox3.RowSource = SQLString6

End Select

Combo2.Requery
Combo3.requery

DoCmd.GoToControl "ComboBox2"

 

[JoanneM]

Hello there

I hope I understand.

To do this

Create a query to source the 2nd combo box
in the field you would like to filter enter the following on to the criteria line

formname represents the name of the form your controls are on and cmbo1name represents the name of your combo box

forms![formname]![cmbo1name]

in the after update on combo 1
type

me!combo2name.requery

combo2name being replaced with your combo box2 name

Create a query to source your 3rd combo box and repeat the process above

except using combo2name
and put on the after update of combo2name

me!combo3name.requery

the requeries will source/refresh your combo boxes.

hope this helps

Jo

 

[scking]

I believe Shorty is right-on except Option1, Option2, and Option3 are undefined. I usually use defined constants at the module or local level which are name to clearly represent their values. I recommend avoiding using the Option1 ... concept. It should be avoided because option button controls are named that by default.

Const VALUE1 As String = "First Combo Box Text Item"
Const VALUE2 As String = "First Combo Box Text Item"
Const VALUE3 As String = "First Combo Box Text Item"

Select Case ComboBox1
Case VALUE1
ComboBox2.RowSource = SQLString1
ComboBox3.RowSource = SQLString4

Case VALUE2
ComboBox2.RowSource = SQLString2
ComboBox3.RowSource = SQLString5

Case VALUE3
ComboBox2.RowSource = SQLString3
ComboBox3.RowSource = SQLString6

Case Else
MsgBox "Undefined Combobox Selection"

End Select
Growth follows a healthy professional curiosity

 

[hrg]

Hi there thanks scking, JoanneM shortie. I was using the recordsource instead of rowsource thank you very very much for yourhelp it is most appreciated.

Thank you very much once again.