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

[Stevep4]

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.

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

 

[stumac]

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):

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

 

[Stevep4]

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]

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]

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]

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.