How do you populate an array from a database??

[jabroni57]

Ok, I have a database that has AssocNames and AssocIDs in it. I want to go to the database and put the AssocNames in a combobox and put the AssocIDs in an array. Then when someone clicks on a Name in the combobox, I want the corresponding ID to show up in a label. Below is what I have, the combobox gets populated just fine, but Im having problems with my array. It gives me errors saying something like 'Array expected' and it highlights the line where I have 'Redim Preserve....' Help!

' NOT SURE IF ITS DECLARED RIGHT
Dim arrAssociates As String

Private Sub Populate_assoclist()
Dim srSQL As String
Dim s As Integer
Dim strAssocName As String
Dim strAssocID As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
srSQL = "SELECT * FROM Associates"
With rs
.Source = srSQL
.ActiveConnection = conn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
rs.MoveFirst
Do Until rs.EOF
strAssocName = !AssocName
strAssocID = !AssocID
cboAssociates.AddItem !AssocName
'IT HIGHLIGHTS THIS NEXT LINE'
ReDim Preserve arrAssociates(s)
arrAssociates(s) = !AssocID
rs.MoveNext
s = s + 1
Loop
.Close
End With

End Sub

Private Sub cboAssociates_Change()
t = cboAssociates.ListIndex
If (t = -1) Then
MsgBox "No name selected", vbInformation
Else
lblAssociates.Text = arrAssociates(t)
End If
End Sub

[Ecniv]

Can I make a suggestion. Or Two.

1) Use naming conventions on your variables - helps when decoding/debugging.
2) Open the recordset as static (read only) and use the recordcount to redim the array. Something like :

adoRst.Open
lngMaxRecs = adoRst.RecordCount
Redim Ary(lngMaxRecs)
For lngCurrRec = 0 to (lngMaxRecs-1)
  '---- do processing here
  Ary(lngCurrRec)=adorst("field")
  adoRst.MoveNext
Next

Vince