linking your combo boxes to a list box via a command button

[Dharmesh]

Hello

I need to select information from different combo boxes. Using a command button, i would then like the information transferred onto a list box.

I have so far managed to link all my combo boxes so the result comes out on a list box. if one combo box is left blank, i get no information in the list box. is there a simple way of doing this.

Thanks

Dharmesh

 

[Road_Warrior]

If Not IsNull([cboNameHere]) Then
Code to add to list box here
End If

 

[Dharmesh]

Thanks for this, but where would I put the code. Do i have to add this to the row source properties.

Dharmesh

 

[Road_Warrior]

Sorry I assumed you were passing combo values to list box with code. If not my answer will be of no help. How are you doing it?

 

[Dharmesh]

on the combo boxes i am selecting the row source and then using the code "Me.List28.Requery" on the after update on the properties.

on the list box on row source i have the expression:

Main.[Effective amount], Main.[ID code] FROM Main WHERE (((Main.[Agency scope])=forms!formtest!combo26) And ((Main.[AF product group])=forms!formtest!combo36));

how would i do this by code then.

 

[Road_Warrior]

OK I guess the first question before I can help you code this is what should appear in the List Box if one of the Combos is blank or should there always be a value there and you just need to ensure there is before you populate the list box?

 

[Dharmesh]

if one of the combo boxes is blank i still want to be able to see everthing in the list box, that relates to the selection made in combo boxes.

ie. if i select one country in a combo box, i would like to see all its products and prices if the product combo box was left blank.

Dharmesh

 

[Road_Warrior]

This IS NOT tested but should be very close to what you need
Assumes at least one of your combos will always have data

Private Sub Command107_Click()
Dim strRowSource As String
If IsNull([Combo26]) Then
strRowSource = "Main.[Effective amount], Main.[ID code] FROM Main WHERE ((Main.[AF product group])=forms!formtest!combo36);"
Else
If IsNull([Combo36]) Then
strRowSource = "Main.[Effective amount], Main.[ID code] FROM Main WHERE ((Main.[Agency scope])=forms!formtest!combo26);"
Else
strRowSource = "Main.[Effective amount], Main.[ID code] FROM Main WHERE (((Main.[Agency scope])=forms!formtest!combo26) And ((Main.[AF product group])=forms!formtest!combo36));"
End If
End If
Me.List28.RowSource = strRowSource
End Sub

 

[Dharmesh]

Would you have an example of this on a form.

Dharmesh

 

[Road_Warrior]

No I do not, hense NOT TESTED. Just add a button to formtest and place the code on the on_click event. BTW I believe You'll need to add SELECT to the string as in
"SELECT Main.[Effective amount], Main.[ID code] FROM Main WHERE ((Main.[AF product group])=forms!formtest!combo36);"