Getting a Macro to run multiple times if conditions are met

[John_]

Is there a way to re-run a macro multiple times until a field on a table is
no longer null?

I have two tables. Table #1 has multiple header records with a date field.
It also contains detail records below each header record on this table. The
detai records contain no date. I am trying to add a date to each of the
detail records beneath each header record to match the date of the header
record. There are no other common fields between the header and detail
records.

I have sequentially numbered the records in table #1. I have made a copy of
table #1 (table #2) and have added a column that I update with an altered
record number by subtracting 1 from each of the sequentially numbered
records. Then I linked the numbered field in table #1 with the altered
numbered field in table #2. Then I run a query that updates the dates in
table #2 on the altered line with the date from table #1. Then I take this
date and go back to table #1 and update it to record #2 (with a query that
links the original record # on table #1 with the original record # on table
#2 if the record on table # 2 is null). I have a macro that runs a macro
about 50 times.

This works fine but the macro continues to run even after all the dates in
the table have been assigned. Is there a way to get Access to run a macro
only until all the null fields are populated? It just runs a lot longer than
it needs to in many cases and I wondered if there was a better way. Thanks!

 

[Pat Hartman]

You have design flaws with this table. In a relational database, ALL rows
of a recordset carry the same weight. There is no such thing as the concept
of header and detail records. Header records belong in one table and Detail
records belong in a separate table. The detail records contain the ID of
their "parent" header record so that they can be joined in queries. Given
the correct structure, attributes that have a single value (i.e. the date in
question) belong in the Header table where they occur only once.

To answer your specific question - don't do this with a macro - use an
update query. Trying to make macros perform programming tasks is
significantly more difficult (IMHO) than actually writing VBA to perform the
same task. But in this particular case, neither a macro or VBA is
appropriate - use an update query.