bllittle wrote on 04/07/2011 17:39 ET :
Here is the final code that works if anyone is interested. This is
where I have a worksheet with some maintenance information that I hide
from the client called AutomationInfo. For the range being referenced
in this code, I have the name of the Worksheet where the combobox is,
the combobox name and DAO recordset SQL information.

code
Sub cbwLoadCombos()
Dim recTemp As DAO.Recordset
Dim lngRecords, lngFields, lngRows, lngColumns As Long
Dim strTargetWorksheet, strTargetCmb, strRecSQL, strWorkbookName,
strWorkbookLoc, strFullLocValue As String
Dim cmbTemp As ComboBox
Dim objTemp As OLEObject
Dim i1, i2 As Integer
Dim wkbCmbAutomate As Workbook
Dim wksCmbAutomate As Worksheet
ReDim strDropDownMaintenance(1 To 3, 1) As String
Dim rngCmbDataRange As Range

With Application
Set wkbCmbAutomate = .ActiveWorkbook
.ScreenUpdating = False
End With
'Get the information about the combobox controls embedded on each
worksheet
With wkbCmbAutomate
Set rngCmbDataRange
= .Worksheets("AutomationInfo").Range("F1")
i1 = 1
Do While Not (rngCmbDataRange.Cells(i1, 1).Value = "")
Set rngCmbDataRange = rngCmbDataRange.Resize(i1, 1)
i1 = i1 + 1
Loop
i2 = rngCmbDataRange.Rows.Count

Set rngCmbDataRange = rngCmbDataRange.Resize(i2, 3)
ReDim strDropDownMaintenance(3, 1 To i2)

For i1 = 2 To i2
With rngCmbDataRange
For f = 1 To 3
strDropDownMaintenance(f, i1) = .Cells(i1,
f).Value
Next f
End With
Next i1
'Get information about where the workbook currently is and set the
datasource
strWorkbookName = .Name
strWorkbookLoc = .Path
strFullLocValue = strWorkbookLoc & "" & strWorkbookName
Set dbHRawData = OpenDatabase(strFullLocValue, False, False,
"Excel 8.0") ';HDR=Yes;

'Move through objects worksheet by worksheet
For i1 = 2 To i2
strTargetWorksheet = strDropDownMaintenance(1, i1)
Do While strTargetWorksheet = strDropDownMaintenance(1,
i1)
With .Worksheets(strTargetWorksheet)
strTargetCmb = strDropDownMaintenance(2, i1)
Set objTemp = .OLEObjects(strTargetCmb)
Set cmbTemp = objTemp.Object
With dbHRawData
strRecSQL = strDropDownMaintenance(3, i1)
Set recTemp = .OpenRecordset(strRecSQL,
Type:=dbOpenDynaset)
End With
With recTemp
.MoveFirst
.MoveLast
lngRecords = .RecordCount
lngFields = .Fields.Count
.MoveFirst
End With
With cmbTemp
.ColumnCount = lngFields
.Column = recTemp.GetRows(lngRecords)
End With
End With
i1 = i1 + 1
If i1 i2 Then
Exit Do
End If
Loop
Next i1
End With
'clean up object variables
Set recTemp = Nothing
Set objTemp = Nothing
Set wksCmbAutomate = Nothing
Set wkbCmbAutomate = Nothing
Set dbHRawData = Nothing
Application.ScreenUpdating = True
End Sub

end code

The last issue I had to work out was not actually related to recordset
objects. I had been setting all the objects first, then moving on to
the recordset objects, then populating the list by using the .column
property. I didn't realize that by moving from worksheet to worksheet,
the objects I'd assigned to variables were losing scope. So, now I
move through the items by moving from worksheet to worksheet, doing
everything right there. I think it is probably a little less efficient
(addressing the database object multiple times instead of once), but
works.

I need some help. I have 8 combo boxes and getting an error on the row count.
What would i need to change?