Populate ComboBox with Field Name

[LysaTigger]

Hi,
I am connecting to an access database through ADODB. The connection is working and I can retrieve data from the recordset.

Now I would like to be able to view the field names from the database that I am connecting to in a combobox in a form.

Here is the code I have so far.

Dim adoConn As ADODB.Connection
Dim adoRst As ADODB.Recordset
Dim fld As ADODB.Field

'Open a DB Connection
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=L:\COMMON\GIS\Avian Influenza\Databases\PHEMS_TestDB.mdb;"

'Open a recordset
Set adoRst = New ADODB.Recordset
adoRst.Open "tblEventSummary", adoConn, adOpenDynamic, adLockOptimistic

adoRst.Close
adoConn.Close
Set adoRst = Nothing
Set adoConn = Nothing

The combox name is cmbUniqueID.

Thanks.
Lysa

 

[bboffin]

The Fields collection of the ADO RecordSet provides access to the field names.

Try something like:

Dim lField as ADODB.Field
For Each lField In adoRst.Fields
lstCombo.Add(lField.Name) ' this syntax may be wrong as I haven't checked it with the help file!
Next

Bob Boffin

 

[PHV]

A starting point:
For i = 0 To adoRst.Fields.Count - 1
MsgBox "Field" & (i + 1) & " : " & adoRst.Fields(i).Name
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LysaTigger]

Hi Bob Boffin,
I keep on getting the error 424 "object required" with the code you gave me.

Thanks,
Lysa

 

[BoxHead]

Probably a stupid question because I'm not familiar with the ADODB connections.

Can't you select "Field List" in the rowsource type of the combo box properties and the table or query in the rowsource?

John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

I answered my own stupid question and couldn't get it to work.

this does.

myStr = ""
For inti = 0 To adoRst.Fields.Count - 1
myStr = myStr & adoRst.Fields(inti).Name & ", "
Next inti
myStr = Left(myStr , Len(myStr) - 2)
cbo1.RowSource = myStr 
cbo1.RowSourceType = "Value List"
cbo1.Requery

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[LysaTigger]

Hi John,
Thanks for the code, but I keep on getting the error 424 "object required" at the line cmbUniqueID.RowSource = myStr

Right now I have inserted your code in the module with the ADO connection (seen in the first posting). Is this the right place to place the code?

Thanks Again,
Lysa

 

[BoxHead]

Sorry, Lysa. I forgot about the 'where does the code question'.

I had placed it after

adoRst.Open "tblEventSummary", adoConn, adOpenDynamic, adLockOptimistic

and before

adoRst.Close


Could you try throwing in a msgbox right before the line that generates the error?

MsgBox myStr

This should verify that the Field names are being read (if you see the Field names in the msgbox) and that would tell us that the Object error is related to the form.

I was able to generate that same error by changing the name of the combobox on my form from cbo1 to cbo10. Compiling doesn't catch the difference so double-check that the ComboBox names match.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[LysaTigger]

Hi John,

I inserted the MsgBox MsStr code and it displayed a message box with the field names of my recordset. I also changed the name of my combo box just in case it was that....but still got the same error. Could it have to do with the properties of the form or the combo box?

Thanks Again, your help is much appreciated.
Lysa

 

[BoxHead]

Lysa, is the form already open when this code runs?

John

Use what you have,
Learn what you can,
Create what you need.

 

[LysaTigger]

Hi John,
I have run the code with the form opened and closed and get the error.
Is there an additional reference library that I have to add, maybe?

Lysa

 

[BoxHead]

Hi Lysa,

I think you're right that it's a reference issue, but I'd think that if it was a library reference, you wouldn't be able to populate the string, myStr.

If you can run the code with the form closed, then I'm guessing the code is not in the form module itself. When I built my little test, I put the code on a command button on the same form as the combo box. Access will assume that a control you reference in code is on the current form. If this code runs from an outside module, you have to use references to tell Access where to look for it.

If that's the problem, you would have to refer to the combo box by its fully referenced name while the form is open.

I think the following will work.

Forms![frmName]![cmbUniqueId].RowsourceType = "Field List" 
Forms![frmName]![cmbUniqueId].Rowsource = "myStr"

Replace frmName with the name of your form and let me know how it goes.


John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Lysa,

Just wondering, have you got this working yet?

John

Use what you have,
Learn what you can,
Create what you need.

 

[LysaTigger]

Hi John,

Thanks a lot for your help. I ended up using this code.

For i = 0 To adoRst.Fields.Count - 1
cmbUniqueID.AddItem (adoRst.Fields.Item(i).Name)
Next

I put in in the Form_Load() in the form with the combo boxes.

Thanks again,
Lysa