Solved

Connecting an access database to a combobox using Visual Basic 6.0

Posted on 2004-04-29
5
168 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:virgilar
  • 2
5 Comments
 
LVL 6

Expert Comment

by:LeeHenry
ID: 10954574
0
 
LVL 19

Accepted Solution

by:
Shauli earned 125 total points
ID: 10954739
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
 
LVL 19

Expert Comment

by:Shauli
ID: 10954828
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question