Populate a Listbox from a Definied Combobox Value

I'm working on a computer parts program and I'm trying to populate a list box from a value that the user can select in my combo box. I have the combo box values set to general categories such as 'Hard Drive, Sound Card, Video Card, etc' and upon selecting one of these such as 'Hard Drive' it will filter the parts in my Inventory MS Access table to display only the Hard Drives that are there. I can't for the life of me figure out how to do this or find some info on it anywhere.

I've populated text boxes when a selection is made from a combo box but i can't get seem to figure out how to populate a list box. I use a ADODC control and use the following code to do that...

Private Sub dcmbProductType_Change()
Dim ProdTypeComb As String

'Set filter
datProductType.Recordset.Filter = "ProductType = '" & ProdComb & "'"

End Sub


Any help would be most appreciate

Thanks

use the onclick event of the combo box, along with the text property of the same. In the click event, use the list.cls method to clear the list before requerying the database to pull in the new list. Make sure that you use some kind of module or global level string to capture the current value of the combo box so that you can compare to find out whether it has actually changed. I would recommend this approach over using an integer to signify the listindex of the combo box as you might decide to add/remove list items, and in some cases, the same index could specify two separate items

Do you have any idea of how to code this? I'm incredibly new to this, only been using VB for a month and have done a lot of code re-use. I'm just basically trying to find the Parts in the inventory table that correspond to the values in my combo box and display them in my list box. thats it. i've tried all manner of controls and select statements and i can't get it to work. what i have been able to do is in the ADODC control for the RecordSource property i set it to use a SQL statement like this

SELECT ComputerPartName FROM ComputerInventory WHERE (ProductType = 'Hard Drive')


my DataList box which is set to that control and its ListField and BoundColumn set to the ComputerPartName then displays only the hard drives in the table. thats fine but i don't want to have that 'Hard Drive' in the SQL statement. I want it to be the value of what I choose from my plain combo box of preset parts which i set in the list property.

in the click event of my combo box i have this

ProName = cmbProductType.Text

i have ProName in a Module as this

Public ProName As String

and when i put ProName in place of 'Hard Drive' in the SQL statement in the RecordSource of the ADODC control i get a error saying "No value given for one or more required parameters"

this is driving me up the wall

Any thoughts on why this isn't working?

Thanks!

sure.. I can do it, but not right now... I'll post some code when I get home from work

this is what i tried, what it does now is it displays the computer parts into the list box alright but its displaying all the parts, not just the one that is selected from the combo box


Private Sub cmbProductType_Click()

List1.Clear

Dim ProdComb As String
Dim VarProduct As String
Dim prod_db As String
Dim ProdStatement As String
Dim pdconn As ADODB.Connection
Dim prs As ADODB.Recordset


VarProduct = cmbProductType.text
txtTestVar.text = VarProduct

prod_db = App.Path
If Right$(db_file, 1) <> "\" Then prod_db = prod_db & "\"
prod_db = prod_db & "GCT5.mdb"

Set pdconn = New ADODB.Connection
pdconn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & prod_db & ";" & _
"Persist Security Info=False"
pdconn.Open

ProdStatement = "SELECT ComputerPartName, ComputerProductType FROM ComputerInventory ORDER BY ComputerProductType"
'WHERE ComputerProductType = 'VarProduct'

'prs.Filter = "ComputerProductType = VarProduct"

Set prs = pdconn.Execute(ProdStatement, , adCmdText)

Do Until prs.EOF
List1.AddItem prs!ComputerPartName
prs.MoveNext
Loop

prs.Close
pdconn.Close



End Sub


I commented out the WHERE clause cause it was giving me an error and it the list box then populated all the names obviously

I tried using a filter but it gave me an error saying that the "Object variable or with block varible is not set"

i'm close...

ok, if you're using the where statement, then you shouldn't need the filter. Try moving the where clause in front of the Order By clause, and using the LIKE 'VarName' expression, instead of =

ok this is what i changed the SQL statement to

ProdStatement = "SELECT ComputerPartName, ComputerProductType FROM ComputerInventory WHERE ComputerProductType LIKE 'VarProduct' ORDER BY ComputerProductType"

now the list box doesn't get populated with anything. it runs and no errors but theres nothing in the list box

i thought that if i took out the WHERE clause I could use a filter instead but it gave me that error

do two things, #1 - change the statement in your load list code to List1.AddItem prs("ComputerPartName"), and then #2 - put a breakpoint right before that statement and follow the processing through the loop... make sure it is processing through the recordset - I'll keep looking to see if I can find anything else

ok, i switched out the List1 thing but it still doesn't populate the list.

I took out the WHERE clause altogether and the list box gets populated and i see it going through the loop cause i got msgbox prompt after every population.

my statement is now this

ProdStatement = "SELECT ComputerPartName, ComputerProductType FROM ComputerInventory ORDER BY ComputerProductType"


and my list population is

Do Until prs.EOF

List1.AddItem prs("ComputerPartName")

MsgBox "hello"
prs.MoveNext
Loop


if i put the filter back in it gives me this error

Arguements are of the wrong type, are out of acceptable range, or are in conflict with one another

the filter is this

prs.Filter = "ComputerProductType = cmbProductType.Text"

that would be because your filter statement is based on a string literal - that means that when you say


it is actually trying to find records with "cmbProductType.Text", and not the value contained in that object. Try it this way instead..

That worked! Thanks so much for your help