Using a recordset as a rowsource for a combo box

[Orion45]

I have a combo box whose rowsource needs to change based on a selection from the previous form. Since my application isn't using linked tables I need to do this with an ADO recordset. Does anyone know of a way to assign a combo box's rowsource property to a recordset?
I appreciate the help.

 

[Orion45]

I found some code to do this but it is limited to a string length of 2048 characters. For my application I need something to accomidate a bigger recordset. In the mean time this might be useful to someone.

Dim rsGetLogins As ADODB.Recordset
Dim strFill As String
'Populates the drop-down box
Set rsGetLogins = New ADODB.Recordset
rsGetLogins.Open "SELECT name FROM syslogins ORDER BY name", conDev  'conDev is a connection string in a global function
strFill = (rsGetLogins.GetString(adClipString, ColumnDelimeter:=";", RowDelimeter:=";"))
    With [cboRolesLogins]
        .RowSourceType = "Value List"
        .RowSource = strFill
        .Requery
    End With

 

[wemeier]

You could also put the contents of what would go in the ADO.OpenRecordset method as the RowSource property of the combo box. Use "Table" still as the RowSourceType.

 

[cmmrfrds]

Do you know what is causing the limit of 2048. Is it the string data type? Did you try changing the data type to variant - not sure if it would make a difference.

I was going to suggest either the GetString or the GetRows but both would run into the 2048 limit. If you can figure out what is causing the limit this is the best solution. If not, maybe a callback function will work but requires a bit of coding.

If the limit is due to RowSourceType = "Value List" where a value list cannot exceed 2048 then that is a pain. Let me know what you find out since I am interested in the solution.

 

[Orion45]

it is definitely the value list that cannot exceed 2048. I tried using a variant and it didn't make a difference. I'll have to try a callback function. I was trying to avoid going that route. Thanks for your input.

 

[Orion45]

Well, I found an easier way to do it but it requires an ActiveX combo box control. This allows you to use the AddItem method just like in VB 6. This also works suprisingly fast. Here's the code:

Dim rsGetLogins As ADODB.Recordset
Dim i As Long
'Populates the drop-down box
Set rsGetLogins = New ADODB.Recordset
rsGetLogins.Open "SELECT name FROM syslogins ORDER BY name", conDev
cboRolesLogins.Object.Clear
For i = 1 To (rsGetLogins.RecordCount)
    cboRolesLogins.Object.AddItem rsGetLogins.Fields("name")
    rsGetLogins.MoveNext
Next i

 

[cmmrfrds]

Thank you, the code looks straight forward and less than involved in a callback funtion.