Populating ComboBox with an existing Access query

[christrawick]

I have a VB form with a ComboBox that I want to have populated with an existing Access query result set. This is probably an easy question, but I'm working my way through VB, and I haven't been able to make it work at all. I'm a relative newbie, so be gentle with me.

Thanks in advance.

Chris

 

[zemp]

The code below opens a connection to an Access 2000 database named test that is located in the same folder as the application.

Dim Conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With Conn
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb ;Persist Security Info=False"
.Open
End With

The next section opens and sets a command object to use an existing query in access. An example of adding parameters is included. Remove the paramter if not needed or repeat the two lines if more than one parameter is required.

With cmd
Set .ActiveConnection = Conn
.CommandText = "Query1" 'Name of the access query
.CommandType = adCmdStoredProc
'Add a parameter if needed
.Parameters.Append .CreateParameter("Param", adInteger, adParamInput, 6)
.Parameters("Param&quot.Value = variable
End With
rs.Open cmd, , adOpenDynamic, adLockOptimistic
rs.movefirst

Loop through the recordset and fill the combo box. 'FieldName' is the name of the field being used to fill the combo box.

Do
Combo1.additem rs![Fieldname]
rs.movenext
loop until rs.eof

Hope this helps. Thanks and Good Luck!

zemp

 

[christrawick]

Zemp -

Thanks a million for your assistance. Will this manner of accessing the query result set interfere with an existing ADO connection I have to the Access database (for navigating through a table)?

 

[christrawick]

Zemp,

Again, apologies for the simplistic question, but where would the code you passed along, go? FormLoad I assume?

Newbie grattitude
Chris

 

[bjd4jc]

You could put zemp's code anywhere you wish. If you need the combo box to fill when the form is loaded the I would use the Load event...

As far as interfering with your existing ADO code,,, you should be able to merge zemp's code with your ADO connections and just use the connections that you already have instead of creating new ones.

 

[zemp]

Just like bjd4jc said.

Thanks and Good Luck!

zemp

 

[christrawick]

zemp,

Your code worked great, thanks. Question; I placed it within the FormLoad event, and it works fine. But when the form loads, the combo box entry defaults to blank, rather than displaying each records field value within the Access database. The combo box does populate with the results from ther Access query, but on each record, it doesn't default to the present field value that is currently in the Access table.

I want the end users to be able to change the Combo Box value for each record if they need to, but navigating through the records via VB should show what the present value for that field is.