Connecting an access database to a combobox using Visual Basic 6.0

Hi,

 I would like to know how to establish a connection between access and visual basic using a combobox in vb. I need to pull all states from my STATE table and then my second combobox needs to be populated with the counties that belong to that state.

Thanks
Andre
virgilarAsked:
Who is Participating?
 
ShauliConnect With a Mentor Commented:
assuming you have a database with two tables: table states with list of states and table counties with two columns = state and county, then the code below will do it. If your database is different, then post here how it is built in terms of tables and fields.


'======in form declaration area
Option Explicit
Dim myConn As ADODB.Connection
Dim myRec As ADODB.Recordset
Dim sqlString As String

'======load the counties to combo2
Private Sub Combo1_Click()
sqlString = "SELECT * FROM counties WHERE county='" & Combo1.Text & "' ORDER By county"
Combo2.Clear
myRec.Open sqlString, myConn, adOpenKeyset, adLockReadOnly
    With myRec
        Do Until .EOF
            Combo2.AddItem !county
            .MoveNext
        Loop
    End With
myRec.Close
End Sub

'=======form load event. open connection and load states to combo1
Private Sub Form_Load()

Set myConn = New ADODB.Connection
Set myRec = New ADODB.Recordset
Combo1.Clear
myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=C:\db1.mdb;" 'replace with the path and filename of your mdb database
sqlString = "SELECT state FROM states ORDER BY state"
myRec.Open sqlString, myConn, adOpenKeyset, adLockReadOnly
    With myRec
        Do Until .EOF
            Combo1.AddItem !State
            .MoveNext
        Loop
    End With
myRec.Close
End Sub

'========form unload event. close connection
Private Sub Form_Unload(Cancel As Integer)
myConn.Close
End Sub

S
0
 
ShauliCommented:
One correction :)

'======load the counties to combo2
Private Sub Combo1_Click() '                          VVVV
sqlString = "SELECT * FROM counties WHERE state='" & Combo1.Text & "' ORDER By county"
Combo2.Clear
myRec.Open sqlString, myConn, adOpenKeyset, adLockReadOnly
    With myRec
        Do Until .EOF
            Combo2.AddItem !county
            .MoveNext
        Loop
    End With
myRec.Close
End Sub

S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.