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

Stevep4

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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top