Filter ComboBox Data [2010 Express]

I have started a form application using visual studio 2010 express, vb programming. I'm using Access as my database.

My goal is to have ComboBox1 display a ProjectID and ComboBox2 display a TestNumber. Once the Test Number is selected I want my textboxes in the form to display fields from the row that the selected Test Number is in.

I have 2 tables set up right now "Projects" and "TestData". The "Projects" table has a [ProjectID] field, the remaining fields are just misc. project data. The "TestData" table also has the [ProjectID] field, and then the [TestNumber] field, then the remaining fields are misc. data.

Filling [ProjectID] in textbox1 is easy, in the properties I select DataSource from the Projects table, display [ProjectID] field. Where I'm having troubles is ComboBox2 displaying the TestNumber from the other "TestData" table. If I select that field, it fills the ComboBox with EVERY TestNumber there is. So for Project 1 there is a test 1... over 500 projects, that combobox lists 500 Test 1's, no way to tell what project it belongs to...

How do I make ComboBox2 only display Test Numbers that pertain to the Project number selected in ComboBox1?

Using this code I can get it to work:

Dim ProjectNo, SQLString As String
Dim dtTableData As New DataTable()
Dim dbDataAdapter As OleDbDataAdapter
Dim ConnectionString As String = ("Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = Database.MDB")
ProjectNo = ComboBox1.Text
SQLString = ("Select * FROM TestData WHERE ProjectID = " & "'" & ProjectNo & "'" & "")
dbDataAdapter = New OleDbDataAdapter(SQLString, ConnectionString)
dbDataAdapter.Fill(dtTableData)
ComboBox2.DataSource = dtTableData


I also imported system.data.oledb

Problem with this code is it will only filter data in ComboBox 2 but when you make your selection from ComboBox2 it won't activate the row so none of my databinding textboxes fill anymore. Before the code/query is ran it fills up the textboxes perfectly after every selection I just have no idea what project that report number belongs to because the whole field is displayed in the dropdown. I can fill the form with ComboBoxes and they display the corresponding row data because I use a "datasource" instead of textbox "databindings" but I don't want to use 20 comboboxes as my entry form...

I'm new and all my coding has come from reading countless forums and watching youtube videos. Any help would be greatly appreciated as this one little step has drove me nuts!

Reply With Quote

Sounds like you need to GENERATE a key for each test.

So if I had a unique key field set up, say autonumber 1 through 1000 and I linked the combobox2 datasource to the display the key field, woulnd't it just list a bunch of key ID numbers like report numbers already do, or would it only show the key id numbers associated with the project number in ComboBox1?

Is there a way to set up a relationship between the project numbers in both tables so it would it automatically filter the data? I'm sure this is something incredibly simple I just can't see around the corner :)

Not autonumber. Test-number along with the Project ID. Combine both as keys in one table, and link the key of one table to the other.

When table#1 asks for Project# it could show all the tests (being linked by project#) and their Test#'s or whatever else you wanted.

Then, you could BIND them to each grids

It took me a while to figure out what you were talking about but you were right. It was all in the database setup. When you build relationships, the child table will be listed within that parent table after all the parent fields, THATS where you choose the datasource, I was trying to choose a field from each table. Got it to work perfectly w/o even writing one line of code, just building the right relationship and binding it to the correct datasource. Thanks for pointing me in the right direction dglienna!

http://msdn.microsoft.com/en-us/vbasic/ff718212

Video's are a little long, but man there would be no more help forums if everyone took the time to watch them... Glad I just watched them, helped me out tremendously.