Using ADO Query Results to Fill ComboBox

[cstimart]

When using ADO to extract data to excel, is it possible to use 'CopyFromRecordset' to fill a Combo/Listbox?

Or is it easiest to extract the data to a worksheet and fill the box from there?

 

[RoryA]

Yes.

 

[cstimart]

OK...so I have this portion of code...

    arrValues = rst.GetRows()
        With Customer.cboxCustomer
            .List = arrValues + "*"
            .List = Application.Transpose(arrValues)
        End With

How can I add '*' to the list so that instead of picking the Customer Name for my future query, I can select * for ALL Customers?

 

[Norie]

You can add something like All.

.List = Application.Transpose(arrValues) ' add recordset
 
.AddItem "All", 0 'add All at the top of the list.

When it comes to creating the SQL (which I think you might be doing later) you can just check if All has been selected.

If it has then don't add any criteria.

strSQL = "SELECT * FROM TABLE1"
 
If Combobox1.Value <>"All" Then
       ' add criteria
 
      strSQL = strSQL & " WHERE Field1 ='" & Combobox1.Value &"'"
End If

 

[cstimart]

Thank You!

 

[cstimart]

OK...all of a sudden, I am getting a type-mismatch error. I believe the issue is that arrValues is not being populated.

What am I missing?

Dim arrValues
.
.
.
    Set rst = New ADODB.Recordset
        rst.Open "SELECT [Name] FROM [Data Connection TBL] GROUP BY [Name];", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
        arrValues = rst.GetRows()
'Fill ComboBox
'---------------------------------------------------------------------------------------------------------------------------------------
        With Indemn_Customer.cboxCustomer
            .List = arrValues
           [COLOR=red].List = Application.Transpose(arrValues)[/COLOR]
            .AddItem "All Customers", 0 'add All at the top of the list.
        End With

 

[RoryA]

Try this (I suspect you are only getting one result):

Dim arrValues
.
.
.
    Set rst = New ADODB.Recordset
        rst.Open "SELECT [Name] FROM [Data Connection TBL] GROUP BY [Name];", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
   If not rst.EOF then
        arrValues = rst.GetRows()
'Fill ComboBox
'---------------------------------------------------------------------------------------------------------------------------------------
        With Indemn_Customer.cboxCustomer
            If IsArray(arrvalues) Then
           .List = Application.Transpose(arrValues)
           else
            .List = Array(arrValues)
           End If
            .AddItem "All Customers", 0 'add All at the top of the list.
        End With
   End If

 

[snb_]

sub snb()
 with  New ADODB.Recordset
  .Open "SELECT [Name] FROM [Data Connection TBL] GROUP BY [Name];", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
  if typename(.GetRows)= "string" then 
   Indemn_Customer.cboxCustomer.list=split("All Customers|" & .getrows,"|")
  else
   Indemn_Customer.cboxCustomer.List =split("All Customers|" & join(Application.Transpose(.getrows),"|"),"|")
  end if
 end with
end sub

 

[cstimart]

Snb, I had to add the "End With" and have the following...

 With New ADODB.Recordset
  .Open "SELECT [Name] FROM [Data Connection TBL] GROUP BY [Name] ORDER BY [Name];", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If TypeName(.GetRows) = "string" Then
        Indemn_Customer.cboxCustomer.List = Split("All Customers|" & .GetRows, "|")
     Else
        Indemn_Customer.cboxCustomer.List = Split("All Customers|" & Join(Application.Transpose(.GetRows), "|"), "|")
    End If
End With

I get an error: "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

 

[cstimart]

Try this (I suspect you are only getting one result):

Dim arrValues
.
.
.
    Set rst = New ADODB.Recordset
        rst.Open "SELECT [Name] FROM [Data Connection TBL] GROUP BY [Name];", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
   If not rst.EOF then
        arrValues = rst.GetRows()
'Fill ComboBox
'---------------------------------------------------------------------------------------------------------------------------------------
        With Indemn_Customer.cboxCustomer
            If IsArray(arrvalues) Then
           .List = Application.Transpose(arrValues)
           else
            .List = Array(arrValues)
           End If
            .AddItem "All Customers", 0 'add All at the top of the list.
        End With
   End If

rorya, I got the Type mismatch error as indicated above.

A "watch" on arrvalues indicates: Variant/Variant(0 to 0, 0 to 858)

Without this revision, if I removed ".List = Application.Transpose(arrValues)", then my "list" only included "All Customers".

 

[Norie]

What's in arrValues when you get the error?

Is it just one value, one record, or multiple records?