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

Stevep4

New Member
Joined
Aug 28, 2015
Messages
31
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.


Rich (BB 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:

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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:

Stevep4

New Member
Joined
Aug 28, 2015
Messages
31
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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?
 

Stevep4

New Member
Joined
Aug 28, 2015
Messages
31
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.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,900
Messages
5,471,378
Members
406,759
Latest member
jackflint

This Week's Hot Topics

Top