Unlinked Combo Box Using Array

[Sech]

I am working on a database where the front and back end need to remain unlinked from each other to limit the bandwidth as much as possible across a server. However this means I need to connect into the back end using code and set up arrays of table data. I wish to run an SQL statement to create a list of IDs and names, put these values into an array and then populate a combo box with the array.

I can create the array of values from the SQL statement fine however when I try to put the array in the combo box only the final array line is displayed in the drop down list, not all lines. The code I am using is as follows:

'Set up array
ReDim aCombo(2, 0)

'Add recordset lines to the array
Do While Not rsTemp.EOF
ReDim Preserve aCombo(2, lRecordCount)
aCombo(1, lRecordCount) = rsTemp(0)
aCombo(2, lRecordCount) = rsTemp(1)
lRecordCount = UBound(aCombo, 2) + 1
rsTemp.MoveNext
Loop

'Set combo box rowsource to the array
cboTemp.RowSource = aCombo(2, 2)

Does anyone know what I am doing wrong and how to add all the array values to the drop down list? Or is there an alternative way of doing this?

 

[Ed2020]

Try:

cboTemp.RowSource = Join(aCombo(),",")

You need to set your rowsource type to "List"

Ed Metcalfe.

Please do not feed the trolls.....

 

[Remou]

Why not just make the table behind rsTemp the rowsource? And if this does not suit, there is no need to build an array, as Ed2020 implies, just separate the values with semi-colons (. If you need two columns, as you seem to do, you can get this by setting Column Count (Format Tab) to 2.

 

[Sech]

Thanks for the fast replies! Ed, the Join command doesn't seem to be recognised in my Access (97 version). Any ideas?

 

[Ed2020]

Sech,

Aha, I'm afraid the Join function is only availabe from Access 2000 and onwards.

Try this instead:

Public Function MyJoin(ByRef arrToJoin() As Variant, ByVal strSeparator As String) As String
Dim pntr As Integer
Dim strTemp As String

For pntr = 0 To UBound(arrToJoin)
strTemp = strTemp & strSeparator & arrToJoin(pntr)
Next pntr

MyJoin = Left(strTemp, Len(strTemp) - Len(strSeparator))
End Function

Ed Metcalfe.

Please do not feed the trolls.....

 

[Remou]

Why build an array if you just join it after it is built?

 

[Ed2020]

You may have to change:

ByRef arrToJoin() As Variant

To:

ByRef arrToJoin() As String

Ed Metcalfe.

Please do not feed the trolls.....

 

[MajP]

Normally this is done using a call back function which is a very flexible way to fill a list or combo. However, the function has to be used very exactly.

http://www.mvps.org/access/forms/frm0049.htm

If you dig around in the help file under row source you will find some examples. The big advantage (especially with A97) is that you will not bump up against the value list limit (I think it is 2400 characters including your seperators).