Tring to get VB code to automatically scroll through a table.
Results 1 to 6 of 6

Thread: Tring to get VB code to automatically scroll through a table.
Thanks Thanks: 0 Likes Likes: 0

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

    Default Tring to get VB code to automatically scroll through a table.

    Hi. I have VB code that looks at a table connected to a form and runs a macro ("ApdTabtoTotalTest"). I have it set up to go to the next record. It works, but the issue is that I have to manually run the code for each record. There are around 150 records. So I click a button and it runs. I then have to click again to move onto the next record. It only takes a minute or so, but I would like the code to automatically scroll through all the records in the table and run the macro.


    Code:
    Private Sub Form_CurrentC()
    On Error GoTo Err_Form_Current_Error
    DoCmd.GoToRecord , , acNext
     'advances you to next record
    DoCmd.RunMacro "ApdTabtoTotalTest" 'the commands that you want to run
    
    Exit Sub
    Err_Form_Current_Error: 'this does nothing and holds back the error
    End Sub

    I can't seem to get the loop function to work.

    Thanks
    Last edited by Stevep4; Aug 13th, 2019 at 04:29 PM.

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    425
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tring to get VB code to automatically scroll through a table.

    It looks like the code you have is for the oncurrent event rather than an event linked to a button. What you want can be achieved fairly easily, but its a strange approach to loop through a form and run code.... normally this would be achieved by looping through a recordset. If you give us more detail we may be able to give you a more efficient solution.

    if you really feel this is the way to go then either a do until loop or a line label would do it (like I say I wouldn't recommend either):

    Code:
    start:
    DoCmd.RunMacro "ApdTabtoTotalTest" 'the commands that you want to run
    If Recordset.AbsolutePosition = Recordset.RecordCount - 1 Then Exit Sub 'Check if you are at the end
    DoCmd.GoToRecord , , acNext
    GoTo start
    Last edited by stumac; Aug 14th, 2019 at 04:27 AM.

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

    Default Re: Tring to get VB code to automatically scroll through a table.

    Thanks for replying. I'm not a programmer and I usually just have to figure things out as I go. I'll try and give as much detail as possible. I have an Access form call MainForm with a Record Source that is a table called SectionTable. The SectionTable has a field called Tab. What I want the code to do is cycles through all the records in the tab field, there are around 150 of them, and run a macro. So in the MainForm there is a field called TabField. The tabfile is already connected to the Sectiontable and the tab field.. I want the code to run a macro with the first record in the tab field as a criteria. I then want the code to move to the next record in the tab field and run the macro again. I was able to get it to work with the original code, but I had to run the code every time. I'm totally open to any more efficient way of doing things.

    Thanks

  4. #4
    Board Regular
    Join Date
    Jul 2010
    Posts
    425
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tring to get VB code to automatically scroll through a table.

    No problem, most of us on here are the same as you, figuring things out through experience

    What is the macro you are running doing?

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

    Default Re: Tring to get VB code to automatically scroll through a table.

    The macro is running a single query. The query is looking at the MainForm and the field TabField within the MainForm. The Tabfield is populated with a piece of data. Let's say it says "Accessories." the query runs using Accessories as the criteria. I want the code to move to the next record in the SectionTable. Let's say Accessories is the first record, I want to move on to the second record, populate the field in the form, and run the query again and so on until there are no more records left. I know EOF (end of file) might be something I would use. Really, it doesn't seem that complicated, just can't figure out how to get it to work.

  6. #6
    Board Regular
    Join Date
    Jul 2010
    Posts
    425
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tring to get VB code to automatically scroll through a table.

    So the code I posted in #2 will do what you need.

    Depending on what your query is doing then I would question the need to run it 150 times. If you do need to do this then I would create a recordset.,, loop through it and run the query - independent of the form. If you post the SQL for your query I should be able to pull the code together for you.

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
  •