Macro-Action RunMacro

[Guest]

I am running a macro that calls a query. The query is an update query that
matches two tables on a field and if there is a match a Match field is
updated to True.

I have created a Macro that calls this query. I would like the macro to
keep running until the result is 0 records to update.

I tried to create a macro with Action RunMacro and need to know the
expression for Repeat Expression for this situation.

Thank you

 

[John W. Vinson]

I am running a macro that calls a query. The query is an update query that
matches two tables on a field and if there is a match a Match field is
updated to True.

ummmm... Why? As soon as you edit a record in the table, that Match Yes/No
field *will be wrong*, with no way to detect it. I'd reconsider whether this
field should even EXIST!

What are the two tables? Are you storing data redundantly and need this to
handle the update anomalies, or what?

I have created a Macro that calls this query. I would like the macro to
keep running until the result is 0 records to update.

A properly written query (if the query is needed at all) should be able to do
all the updates in one go (unless you're matching on the Match field
itself)... Care to post the SQL of the query?

John W. Vinson [MVP]

 

[Guest]

Hi John, sorry to confuse you. I'll be more specific.

I have two tables because we download data from two different systems. I
download from one system the data into an Access Table (TableA) and I
download data from another system into another Access Table (TableB).

I created an update query that matches both tables on two fields e.g
BTN+Amount

TableA TableB
BTN --------- BTN
Amount -------- Amount
Match Match
If there is a match, I update the Match field with True.
I have a button they press that calls a macro that calls this query. They
have to press it until the results is 0 records updated.
I wanted the macro to run automatically instead of the user pressing the
button until 0 records updated. I thought maybe I could update the repeat
expression in the macro but I don't know what the expression would be. I
hope I haven't confused you more. Thank you,

 

[John W. Vinson]

Hi John, sorry to confuse you. I'll be more specific.

I have two tables because we download data from two different systems. I
download from one system the data into an Access Table (TableA) and I
download data from another system into another Access Table (TableB).

I created an update query that matches both tables on two fields e.g
BTN+Amount

TableA TableB
BTN --------- BTN
Amount -------- Amount
Match Match
If there is a match, I update the Match field with True.

Again: WHY? If you have a unique Index on the combination of BTN and AMOUNT,
you can determine whether the records match *just from the query*. All you're
accomplishing with the MATCH field is storing that bit of information AGAIN,
redundantly. You don't *need* to store the information that they match - just
use a Select Query joining the tables. They match, or they don't!

I have a button they press that calls a macro that calls this query. They
have to press it until the results is 0 records updated.

Running the query once, or twice, or fifty times should not make ANY
difference. Either the records match or they don't match. If they don't match,
running the query again *won't change that fact*.

As requested (again) - please open this Query in SQL view and post the SQL of
the query. It makes no sense to me that rerunning the query should be
necessary; in fact as noted above, I don't see any need to run the query even
*once*!

I wanted the macro to run automatically instead of the user pressing the
button until 0 records updated. I thought maybe I could update the repeat
expression in the macro but I don't know what the expression would be. I
hope I haven't confused you more. Thank you,

I know how to do that in VBA code, but I don't use Macros much and don't know
how to loop in macros; it's easy in VBA. But I'd like to establish whether
it's even *needed*.

John W. Vinson [MVP]