Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Looping through records (A97/NT4.0 )

    THis is an easy one for all you wizards out there but I am a newbie and need a little basic help. I am trying to loop through a recordset using vba code in a module. The purpose of this is to check for an archieve flag. If it does not exist, append that record to another table. I it does exist I need to leave it alone. Then at the same time count all the records that are being appended to the table. I have the basic loop process down but am falling way short in trying to count the number of records. Can anyone help?

    Thanks
    Kevin
    Kevin

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Looping through records (A97/NT4.0 )

    Why not use an append query instead, using the flag as a selection criteria? This will give you the total at the end.

    But in your loop, you just need to have defined a counter in your code:
    Dim lngC as Long

    Then in your loop, every time you write to your archive table, just do:
    lngC = lngC + 1

    After you exit the loop, just put this to see the number:

    MsgBox ("No. of records archived = " & lngC, vbexclamation)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through records (A97/NT4.0 )

    Thanks Mark,
    I took your reply into consideration and developed queries that first append the information and then flag the archieve tag. How do I get the total from the query to display in the message box you described in the loop process. Would I add a count function in the query (if thats possible) and then just display that result or should I capure that some other way. As you can see, I'm kinda confused about that part. Thanks
    Kevin
    Kevin

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Looping through records (A97/NT4.0 )

    >>I took your reply into consideration and developed queries that first append the information and then flag the archieve tag. How do I get the total from the query to display in the message box you described in the loop process. <<

    Most times, an append query will tell you how many lines were appended (and how many weren't for various reasons). Other than that, you would have to do a DCount using the same criteria as the query, then display that value in the message box.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •