Loop and End of record

[Anhjan]

Please help, I'm new to VBA and I can't seem to figure this out...

I'm trying to run a looper to speed up my query...basically...I have a form that is connected to a table with a one field called "week." So far it runs fine unitl I get to the end of the table and I get an error msg stating that I'm at the end of the table. So my code stops and it doesn't contine on to the next set of queries.

How can I handle the end of the table. I don't think the "Do Until IsNull(week)" is right to end the loop.

DoCmd.GoToRecord acForm, "Frm_Cat_EOM", acFirst
Do Until IsNull(week)
DoCmd.OpenQuery "a020_qCan_LY_YTD_Cat", acViewNormal
DoCmd.GoToRecord acForm, "frm_Cat_EOM", acNext
Loop

Any assistance would greatly be appreciated.
Thanks,
Jan

 

[FoFa]

What are you trying to accomplish with this code?

 

[AncientOne]

Your code doesn't appear to do anything. But you could open a recordset object and use the EOF property to detect the end of the recordset. Or use the RecordCount to determine how many loops you need. If you explain why you need to run this code in this way, we might be able to help with a simpler solution.

 

[Anhjan]

FoFa and Ancient one thanks for replying to my post...

Basically, I have three countries with linked data tables...all tables have the same structure and data fields. I have three queries that are the same but each use a different linked country tables. I'm trying to gather information by week, so I have a table with week numbers. I utilize a form that uses the week tables for it data source and the form has a macro button, that will run the code below.

Private Sub run_macro_Click()
DoCmd.SetWarnings False

DoCmd.GoToRecord acForm, "frm_Week", acFirst
Do Until IsNull(week)
DoCmd.OpenQuery "a010_qAus_LY_YTD"
DoCmd.RunMacro "mcr_Next"
Loop

DoCmd.GoToRecord acForm, "frm_Week", acFirst
Do Until IsNull(week)
DoCmd.OpenQuery "a010_qCan_LY_YTD"
DoCmd.RunMacro "mcr_Next"
Loop

DoCmd.GoToRecord acForm, "frm_Week", acFirst
Do Until IsNull(week)
DoCmd.OpenQuery "a010_qFra_LY_YTD"
DoCmd.RunMacro "mcr_Next"
Loop

DoCmd.SetWarnings True
End Sub

When I run this code, it works fine for the first county query, but when it reaches to the end of the table, it returns an error message, stating that it's reached the end of the table and there are no more records.

What I would like to do is set up a looper that will run one week at a time through the query...append the results to a table and then go to the next week, until the end of the weeks table, and then move to the next country query.

This VBA stuff is all new to me and I truly appreciate any help.

 

[FoFa]

Maybe I still do not fully understand, but why can't you run append queries using the form field/s as a criteria to populate the table?