Results 1 to 4 of 4
  1. #1
    stephen c is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    30

    Loop question

    Hello, I am trying to learn a little about looping in VBA. As a first exercise, I have a form that covers a table "consolidatorlisttable". In that table, there is a checkbox called portalsend. I have tried to loop through the records, count the instances where portalsend is checked to be TRUE, and display that in a checkbox called display. It isn't working.

    There are 3 records in there. If I check the box in one record, the result is 3. Ugh. Any guidance as to my mistakes would be appreciated!

    -------------------------------------

    Private Sub Command79_Click()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM consolidatorlisttable")
    Dim i As Integer


    i = 0


    rs.MoveFirst


    Do While Not rs.EOF
    If portalsend = True Then
    i = i + 1
    End If

    rs.MoveNext
    Loop


    display = i


    Requery


    rs.Close
    Set rs = Nothing
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you expect the "display = i" and "Requery" lines to accomplish?

    Need the rs! qualifier in front of portalsend field reference otherwise it's just an undeclared variable and so defaults to variant type although the output should then be 0, not sure why you get 3.

    This works for me:

    Code:
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM consolidatorlisttable;")
        Dim i As Integer
        i = 0
        rs.MoveFirst
        Do While Not rs.EOF
            If rs!portalsend = True Then i = i + 1
            rs.MoveNext
        Loop
        Debug.Print i
        rs.Close
        Set rs = Nothing
    Should use CODE tags when posting code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am trying to learn a little about looping in VBA
    While you're at it, I suggest when working with record sets that you get into the habit of dealing with the possibility that there are no records returned to it, otherwise, attempting to invoke a move operation on a set with no records will generate an error. Here's a good source
    http://allenbrowne.com/ser-29.html
    Last edited by Micron; 04-30-2017 at 03:24 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    stephen c is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    30
    Thank you, June7. That did it. The display=1 was because I had a textbox named display (bad name, I now realize) that I wanted the count to show up in. This was hastily thrown in there, to see if what I had written worked (obviously didn't). It's all good now. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  2. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  3. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  4. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  5. Novice Question..Dowhile Loop
    By redbull in forum Programming
    Replies: 2
    Last Post: 03-08-2012, 03:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums