[2005] SQL Query combobox.

[mjbrown20]

Hello,

This really can't be too difficult, but for some reason I am having a hard time.

I have an Access database that I want to include in my project. It has columns Manufacturer, Model, Description, Price.

I simply want to use SELECT DISTINCT to populate mfrComboBox.

And then populate modelCombobox with SELECT from [table] WHERE [manufacturer] LIKE mfrComboBox.selecteditem.

So far, I have tried databinding, tableadapters, and an sqldataadapter. Not sure where I'm going wrong with those.

I have done this in Excel with the code below. The only difference now, is that I want to have the database as part of the project, rather than specifying the path to the database. What's really frustrating is that I have already accomplished this, and for some reason can seem to get it in VB2005.

Any help would be greatly appreciated. Thank you.

(excel VBA code that accomplishes the same thing)

Sub Populate() 
    Dim CnString As String 
    Dim Cn As ADODB.Connection 
    Dim Rs As ADODB.Recordset 
     
    CnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=C:\Folder\Workbook.xls;Extended Properties=Excel 8.0" 
     
    Set Cn = New ADODB.Connection 
    Cn.Open CnString 
    Set Rs = New ADODB.Recordset 
     
    Rs.Open "SELECT DISTINCT [Column] FROM [Sheet] ORDER BY [Column]", _ 
    Cn, adOpenStatic, adLockOptimistic 
     
    With Rs 
        Do While Not .EOF 
             
            UserForm1.ComboBox1.AddItem Rs!Column 
            .MoveNext 
             
        Loop 
        .Close 
    End With 
End Sub 
 
 
Private Sub ComboBox1_Change() 
    Dim CnString As String 
    Dim Cn As ADODB.Connection 
    Dim Rs As ADODB.Recordset 
    Dim CBS As Variant 
     
    CBS = UserForm1.ComboBox1.Value 
    UserForm1.ComboBox2.Clear 
     
    CnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=C:\Folder\Workbook.xls;Extended Properties=Excel 8.0" 
     
    Set Cn = New ADODB.Connection 
    Cn.Open CnString 
    Set Rs = New ADODB.Recordset 
     
    Rs.Open "SELECT * FROM [Sheet] WHERE [Column]=" & CBS & _ 
    "ORDER BY [Column]", Cn, adOpenStatic, adLockOptimistic 
     
    With Rs 
        Do While Not .EOF 
             
            UserForm1.ComboBox2.AddItem Rs!Column 
            .MoveNext 
             
        Loop 
        .Close 
         
    End With 
End Sub