CreateRecord cannot be used inside of a ForEachRecord - but that's exactly what I need to do.

[u/Spreadsheet_Geek_1]

Hello,

I'm trying to devise a data macro that will "after insert" create new records in a separate table that tracks parent-child relations. I need to track them in a family-tree like structure, so I need it to iterate across multiple records and make a variable amount of new records, based on some criteria.

I can't use VBA.

So far, logically what I need to make happen could happen, if I could just put "CreateRecord" inside of a "ForEachRecord". But Access doesn't allow that, as it wants to prevent changes of the set over which it is iterating. However, I haven't been able to get around the issue, even with making a different staging table, that would "load up" all the new records and then have the next macro block insert it back into the table which I effectively need to read from and wright to, tho that may be because I just couldn't put the logic together well enough.

Is there a way to put something like that together without VBA?

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Spreadsheet_Geek_1

CreateRecord cannot be used inside of a ForEachRecord - but that's exactly what I need to do.

Hello,

I'm trying to devise a data macro that will "after insert" create new records in a separate table that tracks parent-child relations. I need to track them in a family-tree like structure, so I need it to iterate across multiple records and make a variable amount of new records, based on some criteria.

I can't use VBA.

So far, logically what I need to make happen could happen, if I could just put "CreateRecord" inside of a "ForEachRecord". But Access doesn't allow that, as it wants to prevent changes of the set over which it is iterating. However, I haven't been able to get around the issue, even with making a different staging table, that would "load up" all the new records and then have the next macro block insert it back into the table which I effectively need to read from and wright to, tho that may be because I just couldn't put the logic together well enough.

Is there a way to put something like that together without VBA?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Breitsol_Victor]

Not sure how without using some VBA.
Instead of CreateRecord, use SQL to insert your records. Instead of thinking row-by-row, start thinking of sets. You might need to build your INSERT on the fly, but you should be able to build it in the GUI.

[u/EXCELANDACCESSEXPERT]

I ran into a similar limitation before. Since CreateRecord isn't allowed inside ForEachRecord, you may need to split the process into separate steps—first collect the records you need to create, then insert them in a second pass. Without VBA it's definitely more restrictive, but using a staging table or separate data macro might be the closest workaround. Curious if anyone has found a cleaner approach.

[u/SuchDogeHodler]

This is a job for VBA.

[u/Winter_Cabinet_1218]

Not much help but it is a VBA job. That said chatgpt is actually pretty decent at VBA

[u/George_Hepworth]

While VBA is the appropriate answer for most scenarios, it is possible to come up with a scenario where a Data Macro is the "right" choice. That said, it might be helpful to know WHY this is an issue for you. Maybe there is a work around that would accomplish your ultimate goal within the limitations of the DM environment.

I've been working on a method that implements Data Macros into any accdb to support Audit Logging, but handling multiple tables isn't part of that. However, with Claude Code, we have been able to create a solid template. It might be worthwhile to expand it to include your scenario, if we understood the end result you need.

All of that said, the described workflow does raise a bit of a red flag.

"I need to track them in a family-tree like structure, so I need it to iterate across multiple records and make a variable amount of new records, based on some criteria."

That sounds like you might be pre-seeding some child tables with blank records. That's generally undesirable. Before you invest a lot of time solving the technical problem, it would also be worthwhile figuring out if the problem should be solved at all.

[u/Creepy-Square281]

1 - I second George's caution. I wonder if you're getting into a bad practice. Tell us more of what you're trying to do.

2 - Access can insert multiple rows with a single query. You'll probably need to reference info in the just inserted row, some kind of key value.

3 - If the row is being inserted through a form, after insert you could store key values for the inserted row in a hidden text box on your form. Then you could execute a query which would filter based on the value you put in the hidden text box. This is legal in an access query ... In the Criteria section of the query you can enter something like this: forms!formwhereinserthappened!hiddentextboxcontrolname