Combo box/Listbox Help

[gmatriix]

Hello All,

I think I need some vb help with a listbox. What I am trying to do is this:

I have a tbl called "SP Data" that I am working with. I have a column called "CName" and another called "SCAC." What I am trying to do is if the user chooses a "SCAC" in a combo box and clicks a button, it filters the "SP Data" tbl to show every "CName" that is listed with that "SCAC"

I can do this just fine by using a query....however on a form I have a combobox with the list of "SCAC"

I have a list box that would show the CNames associated with that SCAC. These would be grouped

 

[ajetrumpet]

Button Click:

me.listbox.rowsource = "SELECT [SP Data].CNames FROM [SP Data]
   WHERE [SP Data].SCAC = " & me.combobox

me.listbox.rowsourcetype = "Table/query"
   me.listbox.requery

 

[Moniker]

You'll do it in the listbox with a query as well. You set the RowSource property of the listbox to your query. You can make two boxes here. One drop-down combobox has your key value -- the SCAC value -- and that is connected to a listbox showing all the relevant CName values.

For example, you have a combobox named cboSCAC that's filled with all the SCAC values like this:

cboSCAC.RowSource = "SELECT SCAC FROM [SP Data]"

Then you base the listbox off the combobox. Let's name the listbox lstCName, and then it's RowSource is set like this:

lstCName.RowSource = "SELECT CName FROM [SP Data] WHERE SCAC = " & [cboSCAC]

Or, if SCAC is not numeric, but instead alphanumeric, like this:

lstCName.RowSource = "SELECT CName FROM [SP Data] WHERE SCAC = [COLOR="Red"]'[/COLOR]" & [cboSCAC] & "[COLOR="Red"]'[/COLOR]"

Finally, in the AfterUpdate event for the combobox, you'd requery the listbox:

Sub cboSCAC_AfterUpdate()

    lstCName.Requery
    lstCName.Refresh

End Sub

Note that you don't have to programmatically set the RowSource properties for the combo and list boxes. You can do that in the properties window for each control.

 

[gmatriix]

hello

thanks alot for your response......however

ajetrumpet - I tried yours and it errors out at the "WHERE" in the code

Private Sub Command25_Click()
Me.List38.RowSource = "SELECT [SP Data].CNames FROM [SP Data]"

WHERE [SP Data].SCAC = " & me.Combo36"

Me.List38.RowSourceType = "Listboxqry"
Me.List38.Requery
End Sub

---------------------------------------------------
Moniker - I tried yours as well and I get an error when I put the : cboSCAC.RowSource = "SELECT SCAC FROM [SP Data]" in the rowsource of the combo box....it is saying that it does not exist or is misspelled..

Any Ideas

 

[ajetrumpet]

Private Sub Command25_Click()
Me.List38.RowSource = "SELECT [SP Data].CNames FROM [SP Data]"

WHERE [SP Data].SCAC = " & me.Combo36"

Me.List38.RowSourceType = "Listboxqry"

You have not included the list separater correctly. If these are indeed two separate lines of code, you need to use this:

 " & _

as the separater between them, not just ending quotation marks

 

[Moniker]

Moniker - I tried yours as well and I get an error when I put the : cboSCAC.RowSource = "SELECT SCAC FROM [SP Data]" in the rowsource of the combo box....it is saying that it does not exist or is misspelled..

Any Ideas

Yes, it means I either didn't get the field name correct and/or the table name correct. However, I went with what you originally said are the names of your objects:

...I have a tbl called "SP Data" that I am working with. I have a column called "CName" and another called "SCAC."...

If those names are not correct, they'll need to be correct in the SQL. The structure for the RowSource is exactly the same as SQL, like this:

SELECT [FieldName] FROM [TableName]

Replace FieldName with the name of your field, and TableName with the name of your table.

 

[ajetrumpet]

OK GMatrixx,

would you like to tell me who is helping who here, please? There's too many people floating around here. Thanks!

 

[gmatriix]

ajetrumpet,

Please forgive my newbie-ism. I am still getting an error. This is what I have.

Private Sub Command25_Click()

Me.List38.RowSource = "SELECT [SP Data].CNames FROM [SP Data]" & _

WHERE [SP Data].SCAC = " & me.Combo36"

Me.List38.RowSourceType = "Listboxqry"
Me.List38.Requery
End Sub

duuuuuuuuhhhh!?#@*!

Can you help! ...

 

[Moniker]

Delete the bolded red quote:

Me.List38.RowSource = "SELECT [SP Data].CNames FROM [SP Data]" & _

WHERE [SP Data].SCAC = " & me.Combo36"

 

[gmatriix]

Hello Moniker,

I am getting a syntax error when I use this in the listbox

SELECT CName FROM [SP Data] WHERE SCAC = '" & [cboSCAC] & "'"

any ideas?

 

[Moniker]

What is the bound column for the listbox? If it's numeric, you need to lose the single quotes, like this:

SELECT CName FROM [SP Data] WHERE SCAC = " & [cboSCAC]

 

[gmatriix]

Moniker,

No, SCAC is alpha field with data like "FDEX"

I have SELECT CName FROM [SP Data] WHERE SCAC = '" & [cboSCAC] & "'" in the rowsource

bound column is 1

 

[Moniker]

And is column 0 (which is bound column 1) in that combobox the SCAC value or something else? What is the RowSource value for the combobox in question?

This is a little confusing because the column index is zero-bound while the bound column is one-bound. What this means is, if you have a listbox with two columns, it looks like this:

Column1 = ColumnIndex(0) = BoundColumn1
Column2 = ColumnIndex(1) = BoundColumn2

You can have only one bound column -- I'm just trying to show you how the two values are different in a confusing way, especially to newer programmers.

 

[gmatriix]

Moniker,

I really appreciate your help so I included a stripped version of what I am trying to do....please take a look and tell what you see.

Thanks

 

[Moniker]

You had no opening quotes, so you don't need closing quotes. Changed this:

SELECT ListboxQry.CName, ListboxQry.Description, ListboxQry.SCAC FROM ListboxQry WHERE [SCAC] & '"=[Combo2] & "'"

To this:

SELECT ListboxQry.CName, ListboxQry.Description, ListboxQry.SCAC FROM ListboxQry WHERE [SCAC]=[Combo2];

I also added code to the AfterUpdate of Combo2 and the FormLoad of SPfrm to make the listbox update correctly.

 

[gmatriix]

Moniker,

THIS is GREAT!!!.....Thanks for helping with that....I have one more question...

I also have a qry called [SP Performance]. It calculates the Ontime PU and Delivery for each [SCAC]. How do I get these fields to look up whats select from the drop down and list box together...

It would look up: (from that [SP Performance] query)

Ontime PU
Late PU
Ontime Del
Late Del

For instance...

On the combo box if I select FDEX and it displays the CNames associated with FDEX. Say I choose EUS-MUS and EUS-R in the listbox. It adds both of those result together.

Is this possible?