combo box in vb (vb6 access 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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 & “‘”

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote