Re: Combo Box - How to populate from a table

From: MikeD (nobody_at_nowhere.edu)
Date: 06/15/04 

Date: Mon, 14 Jun 2004 20:17:04 -0400

"Lee Crisell" <leecrisell@coheecrisell.com> wrote in message
news:EcOdndNJAe-pe1DdRVn-jw@mminternet.net...

> Thanks for your help. Since you do not use data binding to populate combo
> boxes, how do you do it? I assume you do it with code.

Yep. Add a reference to either the DAO or ADO object library. Create a
connection to the database, open a recordset and retrieve the data, and loop
through the records in the recordset. Here's some example code using ADO.
You need to add a reference to "Microsoft ActiveX Data Objects 2.x Library".
You'll also need to change the SQL code to reflect field and table names in
your database, as well as the path and filename of the .mdb file.

-----BEGIN CODE
Option Explicit

Private Sub Form_Load()

    Dim oConn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim sSQL As String

    Set oConn = New ADODB.Connection
    With oConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        'Path is hard-coded for example purposes only!
        .Open "c:\my documents\db1.mdb"
    End With

    sSQL = "SELECT ID, ComboboxText FROM Table1 ORDER BY ComboboxText"
    Set oRS = New ADODB.Recordset
    oRS.Open sSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText

    'You could also open the recordset this way, which opens the table
    'and retrieves all rows in the table (same as the above SQL but the
    'rows are not sorted)
' oRS.Open "Table1", oConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

    With oRS
        If Not (.BOF And .EOF) Then
            Do Until .EOF
                Combo1.AddItem .Fields("ComboboxText").Value
                Combo1.ItemData(Combo1.NewIndex) = .Fields("ID").Value
                .MoveNext
            Loop
        Else
            MsgBox "No records found"
        End If
    End With

    CloseRecordset oRS
    CloseConnection oConn

End Sub

Public Sub CloseConnection(oConn As ADODB.Connection)

    If Not oConn Is Nothing Then
        If (oConn.State And adStateOpen) = adStateOpen Then
            oConn.Close
        End If
        Set oConn = Nothing
    End If

End Sub

Public Sub CloseRecordset(oRS As ADODB.Recordset)

    If Not oRS Is Nothing Then
        If (oRS.State And adStateOpen) = adStateOpen Then
            oRS.Close
        End If
        Set oRS = Nothing
    End If

End Sub

-----END CODE

This might seem like more work and more code....and it is. But almost
everyone will tell you that it's 10 times better than using the data control
and bound controls. It gives you total and complete flexibility to do
exactly what you want or need (as opposed to data-binding, which can be very
restrictive).

Depending on your specific circumstances, you may want the oConn object
variable to be higher scope (module-level or possibly even global). In that
case, you probably wouldn't close the connection until your app closes.
There's debate as to whether it's best to open the connection and keep it
open for the life of the program, or to open and close the connection each
time you need to access the database. There's no definitive answer to this.
Which way is better depends on many factors.

You should never keep recordsets open any longer than what is necessary.

You also should know at least a little SQL to retrieve data and do inserts,
updates, and deletes. I know of nobody that writes "production code" that
uses ADO's properties and methods (like the Recordset object's Update
method) for this. It's best to use SQL, or if the DBMS (like SQL Server or
Oracle) supports them, stored procedures.

>
> My problem is that I want to allow the user to enter their own list and
then
> save it so it can be called again by the combo box. The same user will
have
> a different list in this combo box for the different companies that they
> will be accessing through the form.
>
> Does anyone use data from a column in a table to populate a combo box?

Sure. This is quite common.

> I usually program in Access VBA and it is very easy to use the contents of
a
> column in a table to populate a combo box. But I am at a loss in Visual
> Basic. (I'm using Visual Basic 6.0.)
>

Access and VB are two different products. There's no reason you should
expect them to "work" the same way or have the same set of features. The
combobox in Access is designed differently and has features specific to
databases and data access. The only thing VB and Access really have in
common is that they both use the same programming language engine, but there
are even enough differences in the language that not all your code can
simply be copied and pasted to the other (much of it can but not all of it).

Mike

Relevant Pages

  • Re: Simple Insert Into...
    ... it is in a control on the form; and is unbound (it's actually a combo ... box that is used as a dropdown list, values taken from a different table; SQL ... I need this value to assign a unique ID to the entire recordset being ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Combo Box - How to populate from a table
    ... > through the records in the recordset. ... > Private Sub Form_Load ... > Dim oConn As ADODB.Connection ... > 'You could also open the recordset this way, which opens the table ...
    (microsoft.public.vb.controls)
  • Re: RecordSet mit Access
    ... > Private Sub Form_Load ... > Sub AccessReport(ByRef Path As String, ByRef Report As String, ... > Dim oConn As ADODB.Connection ... wie du die weiteren wichtigen Eigenschaften des Recordset ...
    (microsoft.public.de.vb.datenbank)
  • Re: Problem bei DB-Schließen Ereignis
    ... > Um bei jede Art des Schließens der DB (über btn mit Code oder Menü ... > Private Sub Form_Unload ... > Obwohl die SQL sicher funktioniert (Debug.Print bringt die syntaktisch ... Das Recordset ist nicht aktualisierbar. ...
    (microsoft.public.de.access)
  • Re: Simple Insert Into...
    ... The technique you pointed out to declare a string variable in the procedure ... Sometimes it can be very helpful to copy the SQL statement from the Immediate ... Private Sub YourProcedureName() ... I have a button to click to append the form’s recordset ...
    (microsoft.public.access.modulesdaovba)