CODEWITHSUNDEEP
ms-accessvbams-access-2010
user2763639
user2763639

Reputation: 9

access vba to create array

I have a loop with array and want to create [mesAmis] as "Alpha, Beta, Charlie, Delta Echo, Foxtrot, Golf". However, it returns the friends' names along with 19 commas.

Since my loop reads from my recordset [tblFriendsList], we don't know how many friends we have. Is there anyway I can rewrite my codes below so I don't have to set it as 19 but using a record count? I have tried a lof of times but can't work out the bunches of "monAmie". Thanks in advance! 

Dim monAmie(0 To 19) As String, lngPosition As Long
Dim mesAmis As String
If Not (rs2.EOF And rs2.BOF) Then
 rs2.MoveFirst 'Unnecessary in this case, but still a good habit
 ' LOOPING STARTS
 Do Until rs2.EOF = True        
 intCounter = intCounter + 1        
 monAmie(intCounter) = rs2!TaskDetailedInfo
 rs2.MoveNext
 Loop
 ' LOOPING ENDS
 mesAmis = Join(monAmie, ", ")
 MsgBox ("intCounter: " & intCounter)
 MsgBox ("mesAmis: " & vbCrLf & mesAmis)
' mesAmis is created as :
'Alpha, Beta, Charlie, Delta, Echo, Foxtrot, Golf
 Me.ShowMeAllYourFriends = mesAmis 'display the friends in this textbox
Else
 MsgBox "There are no records in the recordset."
End If

Upvotes: 0

Views: 1916

Answers (1)

VBlades
VBlades

Reputation: 2251

You can try this. Dim your array like this (instead of 0 to 19):

Dim monAmie() As String

And change the relevant part of your code to this:

If Not (rs2.EOF And rs2.BOF) Then
    'Must move to the end of the recordset to get accurate RecordCount.
    rs2.MoveLast
    'Redimension array to number of records in recordset.
    Redim monAmie(rs2.RecordCount)

    rs2.MoveFirst 'Unnecessary in this case, but still a good habit
....

This should redimension your array to the exact size of the recordset.

Upvotes: 1

Related Questions