We help IT Professionals succeed at work.

using the .AddItem with a recordset that reads from an excel column to get unique values

Hello:
What am I doing wrong?  I am trying to feed a combobox in my spreadsheet which reads data from a column in another sheet and puts it into a recordset.  When I try to read from the recordset I get the error "invalid use of Null" and my recordset record count tells me that there are 21 records in my recordset.

Myrecordset.Open "Select Distinct [Business] from [TCodes$D1:D1000]", Myconnection, adOpenStatic

ActiveWorkbook.Sheets("Sheet2").Activate
With ActiveSheet.cboBusiness
   .Clear
   
   Do While Not Myrecordset.EOF
        .AddItem Myrecordset![Business] ''''''''''''''IT FAILS HERE
        Myrecordset.MoveNext
   Loop
End With

PLEASE HELP.

THANK YOU,
Comment
Watch Question

Author

Commented:
Correction to my Select statement.  It should be Sheet2:

Myrecordset.Open "Select Distinct [Business] from [Sheet2$D1:D1000]", Myconnection, adOpenStatic

ActiveWorkbook.Sheets("Sheet2").Activate
With ActiveSheet.cboBusiness
   .Clear
   
   Do While Not Myrecordset.EOF
        .AddItem Myrecordset![Business] ''''''''''''''IT FAILS HERE
        Myrecordset.MoveNext
   Loop
End With

Teh first thing I would try is to add a empty space like this:

.AddItem Myrecordset![Business] & ""

However, I would have to ask if the source of the dat is in the same sheet as the combo box you are populating?

Author

Commented:
It is in the same workbook on Sheet2.  The dropdown box is on Sheet1
>It is in the same workbook

That's not recommended. What does your connection string look like?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Due to memory leak issues with ADO and open workbooks, I would use a different approach - for example:
   Dim objDic            As Object
   Dim varData
   Dim lngRowCount       As Long
   Dim lngIndex          As Long

   Set objDic = CreateObject("Scripting.Dictionary")

   With Sheets("TCodes")
      lngRowCount = .Cells(.Rows.Count, "D").End(xlUp).Row
      varData = .Range("D2:D" & lngRowCount).Value
   End With
   For lngIndex = LBound(varData) To UBound(varData)
      If Len(varData(lngIndex, 1)) > 0 Then
         If Not objDic.exists(varData(lngIndex, 1)) Then
            objDic.Add varData(lngIndex, 1), varData(lngIndex, 1)
         End If
      End If
   Next lngIndex
   ActiveWorkbook.Sheets("Sheet2").cboBusiness.List = objDic.items

Open in new window

Author

Commented:
Trimming worked to get rid of extra spaces.  Thank you very much.