looping macro

  • Thread starter Thread starter boomtap via AccessMonster.com
  • Start date Start date
B

boomtap via AccessMonster.com

I have run into yet another hurdle. I am creating a macro in which I want
the following to happen:
-open form
-go to first record
-open query (append table)
-close query
-check if last record (stop macro if last record)
-go to next record
-go back to step 3 (open query).

I basically want the form to run page by page and run an append query.
I would really appreciate the help.
 
well, you might try writing a multi-step macro to
1) turn off Warnings
2) open the Append query (you don't need to "close" an action query; the
OpenQuery action just runs the query, it doesn't return a dataset in a
window.)
3) turn on Warnings
4) go to next record
5) call itself, with a RunMacro action.

run the multi-step macro from another macro, as
1) open form
2) RunMacro (the above multi-step macro)

though i don't think there's any way to make it run smoothly. when the macro
tries to move to the next record, from the last record in the form, you're
going to get an error - and there's no graceful way to handle errors in
macros.

i'd suggest you use VBA. if you're not doing anything in the open form
except moving from record to record to run the action query, then i'd
dispense with the form altogether. you can use looping code in VBA: open a
recordset, set up a loop to move through each record in turn; within that
loop, run the Append query on each record.

hth
 
The form contains calculations that the append query uses.
well, you might try writing a multi-step macro to
1) turn off Warnings
2) open the Append query (you don't need to "close" an action query; the
OpenQuery action just runs the query, it doesn't return a dataset in a
window.)
3) turn on Warnings
4) go to next record
5) call itself, with a RunMacro action.

run the multi-step macro from another macro, as
1) open form
2) RunMacro (the above multi-step macro)

though i don't think there's any way to make it run smoothly. when the macro
tries to move to the next record, from the last record in the form, you're
going to get an error - and there's no graceful way to handle errors in
macros.

i'd suggest you use VBA. if you're not doing anything in the open form
except moving from record to record to run the action query, then i'd
dispense with the form altogether. you can use looping code in VBA: open a
recordset, set up a loop to move through each record in turn; within that
loop, run the Append query on each record.

hth
I have run into yet another hurdle. I am creating a macro in which I want
the following to happen:
Click to expand...
[quoted text clipped - 8 lines]
I basically want the form to run page by page and run an append query.
I would really appreciate the help.
Click to expand...
Click to expand...
 
Boomtap,

Whereas I agree with Tina's advice, in fact it is actually quite easy to
do this with a macro by moving from record to record through the form.

But I would be interested to know what calculations are being done on
the form, that you feel could not be done within the query itself. In
my opinion, based on what you have told us so far, it should be possible
to do this with one Append Query, to process all records in one hit.
So, if you'd like to provide some more details, we can have another look
at options.
 
i'm really pretty rusty on macros, Steve. how do you avoid the error
generated by a GoToRecord | Next action when you're on the last record in
the recordset?

and i do agree with you that the whole thing can probably be handled by one
Append query; at the least, whatever calcs are taking place in the form
probably can be done at the query level instead.
 
Tina,
i'm really pretty rusty on macros, Steve.
Click to expand...

This is not true, Tina. You're one of the experts :-).
how do you avoid the error
generated by a GoToRecord | Next action when you're on the last record in
the recordset?
Click to expand...

From memory...
- Put an unbound textbox on the form, with its Default Value property
set to 0 - let's say you name this textbox RecordsProcessed
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [Processed]
Expression: [Processed]+1
- In the Repeat Expression argument of the RunMacro action in the
second macro, enter:

[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]
 
ah, very handy. thanks! :)
This is not true, Tina. You're one of the experts :-).
Click to expand...

well, once upon a time i was the macro queen, but i completely defected to
VBA several years ago and very, very rarely visit the old country nowadays.
;)


Steve Schapel said:
Tina,
i'm really pretty rusty on macros, Steve.
Click to expand...

This is not true, Tina. You're one of the experts :-).
how do you avoid the error
generated by a GoToRecord | Next action when you're on the last record in
the recordset?
Click to expand...

From memory...
- Put an unbound textbox on the form, with its Default Value property
set to 0 - let's say you name this textbox RecordsProcessed
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [Processed]
Expression: [Processed]+1
- In the Repeat Expression argument of the RunMacro action in the
second macro, enter:

[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]
Click to expand...
 
tina said:
well, once upon a time i was the macro queen, but i completely defected to
VBA several years ago and very, very rarely visit the old country nowadays.
;)
Click to expand...

I haven't used a macro in my own work for many years. But I still hang
around here because I know how great they are for people who are coming
up the learning curve. :-)
But with the new macro emphasis and functionality coming with 2007
Access, that may change again.
 
Back
Top