Results 1 to 3 of 3

Thread: Tighten up vb code for Access Database
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Tighten up vb code for Access Database

    I have code in my Access Database that does the following:

    In a form (MainForm) that has a record source from a table called SectionTable. The below code is looking at the first record in SectionTable, running a macro that is using the first record from the table, then moving on to the next record in the table and running the macro again. The only way I could get this to work is to repeat the below lines for the 150 records involved. I would like some code that will loop through all 150 records and then stop at the end. Loopuntilend? is there any code that can just repeat the two actions until the end of the source table?

    Code:
    Function RunTotalTestLoop()
    On Error GoTo Err_Form_Current_Error
    DoCmd.GoToRecord , , acNext
    DoCmd.RunMacro "ApdTabtoTotalTest" 'the commands that you want to run
    DoCmd.GoToRecord , , acNext
    DoCmd.RunMacro "ApdTabtoTotalTest" 'the commands that you want to run
    DoCmd.GoToRecord , , acNext
    DoCmd.RunMacro "ApdTabtoTotalTest" 'the commands that you want to run


    Thanks
    Last edited by Stevep4; Oct 8th, 2019 at 04:41 PM.

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    665
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tighten up vb code for Access Database

    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,719
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tighten up vb code for Access Database

    you could use a clone, but I think all you need is a counter based on the form recordset count and I don't be believe you need to clone the recordset for that. Maybe

    Code:
    Function RunTotalTestLoop()
    Dim cntr as Long, i as Long
    
    Docmd.GoToRecord,,acFirst
    
    For i = 1 to Me.Recordset.RecordCount
       DoCmd.RunMacro "ApdTabtoTotalTest"
       DoCmd.GoToRecord,,acNext
    Next i
    
    End Function
    What is missing is an error handler of some sort because acNext will cause an error when you are at the last record. It could be as simple as On Error Resume Next after For i = ... but a full error handler would be better.
    Wondering what the macro does...
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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