VBA MS-Access For Loop Not Incrementing

Question

Sincerely in the dark. My Code:

Public Property Get rowCount() As Integer
rowCount = Counter
End Property

Public Property Let rowCount(ByRef inte As Integer)
 Counter = inte
 End Property

Private Sub Form_Timer() 'Timer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim Caption As Field, Form As Field, Count As Integer, holder As Integer, item As String
Dim strForms() As String

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("MainMenu", dbOpenDynaset)

ReDim strForms(1 To rs.RecordCount())
If rs.RecordCount <> 0 Then
  For c = 1 To rs.RecordCount() Step 1 '!!!THIS IS THE PROBLEM!!!
   MsgBox CStr(c)
   MsgBox rs("Caption")
   strForms(c) = rs("Caption")
   rs.MoveNext
   MsgBox rs("Caption")
  Next c
End If
rowCount = 1
holder = rowCount()
If holder <= rs.RecordCount() Then
 Me.Command10.Caption = strForms(holder)
 rowCount = holder + 1
Else
 rowCount = 1
 Me.Command10.Caption = strForms(holder)
End If

End Sub

I added all those message boxes in my effort to debug. All I need is that counter to go up. No idea why it is not. Why will this thing not increment?!

Answer 1

The best way is to use rs.MoveFirst, rs.MoveNext and rs.EOF to check for end of records. The following VBA will do what you want.

'Open up a recordset on our table
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("MyTable", dbOpenDynaset)

'Did we find any records?
If rs.RecordCount > 0 Then

    'Move to first record
    rs.MoveFirst

    'Iterate through each record
    Do

        'Do stuff with the currentrecord
        MsgBox ("Next record ID is: " + CStr(rs("ID")))

        'Move to next record
        rs.MoveNext

        'Exit when we hit the end of the recordset
    Loop While rs.EOF <> True

End If

'Close the recordset
rs.Close