Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combo box in vb (vb6 access 97)

    i'm making an app in vb6 with an access 97 back end i want to have a combo box on the form and when someone clicks in the combo box the form is updated to the combo selection
    i can do this in access but don't know how in vb
    any help greatly appreciated

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combo box in vb (vb6 access 97)

    I don't. I refuse to do stuff like that in VB because it's so much harder than in Access. The functionality for populating the comboboxes has to be built in code in VB and you have to create your own secondary indexes.

    If you feel you must, there are several very good books on building database applications with VB.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box in vb (vb6 access 97)

    Jerry,
    If you still want to do this in VB here are a few things that might give you some ideas. This example populates the combo box with CompanyName from the Customers table of Northwind. When a user clicks on a name, a couple of textboxes will be populated with data for that customer:
    In general declarations of the form: Dim cnNorthWind as ADODB.Connection
    Then load the combobox when the form loads, limiting the recordset to one field
    Private Sub Form_Load()
    ' Establish a connection to the Access database
    Set cnNorthwind = New ADODB.Connection
    With cnNorthwind
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:Program FilesMicrosoft Visual StudioVB98Nwind.mdb;Persist Security Info=False"
    .Open
    End With
    '
    Dim rsCustomers As ADODB.Recordset
    Set rsCustomers = New ADODB.Recordset
    rsCustomers.ActiveConnection = cnNorthwind
    rsCustomers.Open "Select CompanyName from Customers"
    Do Until rsCustomers.EOF
    cboNames.AddItem rsCustomers!CompanyName
    rsCustomers.MoveNext
    Loop
    ' might as well free up the memory
    rsCustomers.Close
    Set rsCustomers = Nothing
    End Sub

    In the ComboBox's Click event, go get the data matching the selected item; this example keeps it simple by querying the same table, but of course you could build a more complex SQL statement with joins and stuff - use Access QBE to do it and paste the SQL view from Access into your code.
    Private Sub cboNames_Click()
    'Purpose: Retrieve values for the selected customer
    Dim strSQL As String
    Set rsCustInfo = New ADODB.Recordset
    ' Use the connection already opened in Form's Load event
    rsCustInfo.ActiveConnection = cnNorthwind

    ' Choose the appropriate cursor and locks for your project
    rsCustInfo.CursorType = adOpenStatic
    rsCustInfo.LockType = adLockOptimistic

    'Note: The concatenation here is not the most robust! Needs improvement
    ' The SQL statement will have syntax errors if there are embedded quotes in cboNames.text

    strSQL = "Select City, ContactName FROM Customers WHERE CompanyName = '" & cboNames.Text & "'"
    rsCustInfo.Open strSQL

    ' If you want to bind the fields to controls on the form
    Set txtCity.DataSource = rsCustInfo
    txtCity.DataField = "City"
    Set txtContact.DataSource = rsCustInfo
    txtContact.DataField = "ContactName"
    End Sub
    Note: If you only want to show the data and not allow changes use this instead of the datasource and datafield properties.
    txtCity.Text = rsCustInfo!City
    txtContact.Text = rsCustInfo!ContactName

    There's lots more to consider; you'll need to add code to do updates of the recordset if needed, and decide the best way to handle connections and queries. If there aren't tons of records you might want to retrieve all the data in the Load event such as:
    rsCustomers.Open "Select CompanyName, ContactName, City from Customers"
    You'd still populate the combobox from the CompanyName field; then in the combobox click event you can just apply a filter instead of running another query against the database:
    rsCustomers.Filter = "CompanyName = '" & cboNames.Text & "'"

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box in vb (vb6 access 97)

    Just wanted to add that I am not at all disagreeing with Charlotte's suggestion of a good VB Database book to learn about all the issues involved when manipulating databases through VB. There are lots of details that Access handles so well that need to be addressed with code in VB. But sometimes it is kind of fun to do things the hard way. <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combo box in vb (vb6 access 97)

    On the other hand, if you want an outstanding book and doing it with VB, take a look at "Database Access with Visual Basic 6", by Jeffrey P. McManus. His is the only book I've ever found that described the use of classes and the reasons for them in practical, business-oriented ways that the uninitiated could understand. I intend to recommend it to my boss, who can't see any purpose to class modules in Access. McManus's chapter on classes is outstanding and most of his stuff translates fairly easily to Access as well The rest of it gets down to the details of using VB to front-end a database clearly and concisely.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •