Simultaneously add a record to 3 tables

[msmithtlh]

I’m working on a database for an event that takes place next week. Because there were so many data items for each person, I have split the main data into 3 tables:

1. MAINPARTICIPANTS (primary key – autonumber “participantID”
2. MAINSPORTS (primary key is an autonumber, secondary key is “participantID”
3. MAINTRANSPORTATION (primary key is an autonumber, secondary key is “participantID.”

Although there is probably a better way to structure the data, I don’t have time to do that. My problem is that when the user adds a person to MAINPARTICIPANTS, I need it to add a record to MAINSPORTS and MAINTRANSPORTATION (with the same participantID), but haven’t figured that out.

Urgently need help!! Thanks!!!

[ranman256]

you dont do it simutaneously,
but just run 3 append queries. 1 for each table.

[pbaldy]

You haven't shown how you're adding, but you'd need to get the ID from the first append to do the others. Note how Allen does it here:

http://allenbrowne.com/ser-57.html

[kd2017]

How about a "datamacro" or table macro that runs on the tables After Insert event. Its as easy as after insert add new record to other table, set foreign key in other table equal to primary key in main table. Repeat.

[pbaldy]

I thought about that but couldn't remember if they were available in 2010. My gut said no so I didn't mention it.

[msmithtlh]

I need to clarify. I have 3 forms, 1 for MAINPARTICIPANTS data, based on a query, 1 for MAINSPORTS data, based on a query, and1 for MAINTRANSPORTATION data, based on a query.
These three forms work together, pulling up data for the same person, using existing data. BUT, when I add a new person on the MAINPARTICIPANTS form, and go to one of the other forms (MAINSPORTS or MAINTRANSPORTATION), I need it to create a new record in the related table with the same participantID as MAINPARTICIPANTS form. Do I need extra code on the "add new" button on the MAINPARTICIPANTS form to create these related records. If so, can I do this with a macro, rather than VBA?

[kd2017]

I suspect if you use a main form with subforms that access will take care of the rest for you.

To answer your question, yes you can use a macro as I outlined in my previous reply. In the macro you would have add new record action, then a set field action. You'll find the available list of actions in the right pane in the macro editor.

[msmithtlh]

To KD2017,
I would like to use your approach, but am having trouble.
I created the after-insert macro:

[msmithtlh]

To KD2017

My after-insert macro:

[msmithtlh]

To KD2017 continued:

The macro seems to execute before the ID # is assigned. Is there a different property I can use?

[kd2017]

This is more of what I was thinking:

Just put this macro on the table itself like so:


This is all that's needed in the macro:



This is all it takes in Access 2016, I don't use a2010 so I don't know if all these actions are available or not. If table macros aren't available then putting this same macro on the FORM's after insert event (as you have tried above) will still work. If these actions still aren't available then you can still do it with code very easily.

However, I'm still wondering if you should just be using a form based on the participants table, that has two subforms on it that are based on the other tables. This is how it's usually done.

[msmithtlh]

To KD2017:

This is great except that at the point of "after insert" the participantid is still null. Is there a way to combine properties ("after insert" and "dirty")?

[kd2017]

I'm not sure I understand the question.

Are you trying to do this from the form's after insert event? If so then maybe ParticipantID is null because the form moved on to a new record. The Macro works as presented on my machine when called by the table itself. Have you tried the macro from the table's after insert event and not the form's?

Can you upload your database?

[msmithtlh]

Attached is a zipped copy of my database.

[June7]

Don't see any DataMacros associated with MainParticipants table.