Populate VB combobox from Access table 1

[StlMacMan]

I have a VB6 cbo that I want to fill with data from a field in an Access DB. I get the connection and the first field displays in the box, but I want the whole column to show up as the drop-down items so that one can be selected, then when saved, populate and/or update another Access table. Can anyone help me cause the whole column to display instead of only the first record? TIA--Ed
Edit/Delete Message

 

[afryer]

Hi,

Check into using the DBCombo control as this allows you to display items based on a query.

Andrew

 

[AndyLee100]

Can you post some code?

 

[BobRodes]

If you want to use a simple combo box, you can populate it as follows:

'this code assumes an existing combo box myCombo, and an open recordset rs.
myCombo.clear 'if you are repeating the process, this will empty the combo box.
with rs
   do until .eof
      myCombo.AddItem !myField
      .MoveNext
   loop
end with

This would be considered "manual population" as opposed to data binding.

HTH

Bob

 

[StlMacMan]

Thanks, Bob.

I was able to find a way to fill my 3 combo boxes with data from three Access tables so that the drop downs showed the whole field as the list elements. Like this:

Private Sub CboFunder_Dropdown()
Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon
ssql = "Select Funder From Funding_Source Order by Funder"
rs.Open ssql, Con

Do Until rs.EOF
CboFunder.AddItem rs("Funder")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing
End Sub

Here's what I'm up to. I want to track contracts by selecting site, program, and funder from 3 individual Access tables that are in a single db. I have been able to connect to the db, and produce a field list for each of the three boxes from which the user will select one each. What I can't figure out is how to save the selected data from the three to a fourth comprehensive Access table in the same db where the other three tables are located. All the info I can find only updates the record source from which the lists were drawn and I am trying to get that info added to a fourth table that will hold all contract data that is put together in a comprehensive list for all the sites, programs, and funding streams. I am (obviously, I'm sure) new to VB and have bit off a large chunk--not too much I hope. Can you tell me how to pass the info from the three boxes on the form to a different table in the same db? I'd appreciate any help. --Ed

 

[Swi]

If you want to take the selected text from the comboboxes and save them to another table you could just use your connection object and use an INSERT INTO statement.

Ex. -

conn.Execute "INSERT INTO Table1 (Test1, Test2, Test3) VALUES ('" _
& cboDept.Text & "','" & cboFname.Text & "','" & _
cboLname.Text & "')"

** Table1 is your table name you want to save the info to.
** Test1, Test2 and Test3 are the field names to save the info to.
** cboDept, cboFname and cboLname would be your combobox data that you want to save.

Swi

 

[StlMacMan]

Thanks, Swi. I played around a bit w/ your suggestion and got it to work! I really appreciate the help.--Ed

 

[BobRodes]

Swi beat me to it. Glad we could help.

Bob