Repeating Macro

[Guest]

I am trying to open a form (OpenForm), go to a control (GoToControl), requery (Requery)a specific field within my form, then move to the next record. I have done this but I need it to move through all the records until it reaches the last one. As of now I would have to enter this set of commands for each record. However, new records are added daily. Is there a way this can be done automatically? I need this to go to each record no matter how many records there are. Can anyone help?

 

[Steve Schapel]

Randy,

I do not understand the concept "requery a specific field within my
form". Can you give more details of what you are doing?

 

[Guest]

Hi Steve….Thanks for your quick response….What I have is a field [household size] that automatically updates another field [qual pct] based on another field [annual income]…This is an affordable housing waiting list by the way! Once a year we get new income limits from the state. I have a table just for the annual income limits which are based on household size. When we receive our new income limits from the state I input them into the income table. What I am trying to accomplish is when I enter the new income limits into the income table, I need it to go through all the records and update each persons qualifying percentage [qual pct]. I have a macro as mentioned in the first posting, that I created to open the data entry form and basically go to the [household size] and re-query that field. This automatically updates the [qual pct] field. Looks something like this

OpenForm [Data Entry Form
GoToControl [Family Size
Requery [Family Size
RunCommand RecordsGoToNex
GoToControl [Family Size
Requery [Family Size
RunCommand RecordsGoToNex

Etc…â€

Do you know of a way to make it go through each record without having to repeat these commands over and over for each record? I hope that gives a little more info….Should you need more let me know….Thanks again for your help

 

[Steve Schapel]

Randy,

Thanks for the further explanation.

Does something actually happen when you apply the Requery action to the
[Family Size]? To be frank, I still can't imagine what you are doing...
I have never experienced Requery being applied to a single field!

There are a number of issues in your question. If you need to apply a
macro to each record, you can do this by making a separate macro, using
the RunMacro action, to run the first macro, and set the Repeat Count
argument to the number of records in the form.

However, in this case, it would seem that an Update Query to do all the
records in one hit would be a more applicable approach. You can make
the query, and then use the OpenQuery action if you need to run it via a
macro.

But over-riding all of this, is the consideration of the validity of
your basic design. The general principle in databases is that data
which is calculated or derived from other existing data should not be
stored in tables. According to this, you should not have a [qual pct]
field at all! The correct method is that the qual pct value should be
calculated "on the fly" using a query whenever you need it for your
purposes on form or report.

 

[Guest]

Thanks Steve....I don't think I am being clear on how the whole process works....Unfortunatley I do not have time right now but will describe better for you on Monday....Thank you so much for your help and I will talk with you soon...Randy