How would I update 2 tables based on an edit in a subform?

[cherold]

I have a subform that contains a table (in datasheet form). If I add a new record to the table from within the subform, I need the table to automatically enter data in a field and also need to add a record to a second table. I'm not sure how to make that all happen (or even if this question is more properly about forms or queries or something else).

There are three relevant tables (these are used to catalog my album collection, btw):

LPs
lps_id, album_title, artist_id

Songs
songs_id, song_title, artist_id

LPContents
lpcontents_id, song_id, lp_id

The main form displays a single album.

The subform displays all songs contained in that album (as indicated by LPContents).

What I need to do is: If I add a new song record in the subform, I need Songs.artist_id to be set to LPs.artist_id for the current album, and I need LPContents to have a new record with LPs.lps_id and the just-added songs_id.

I would think I would want to trigger these updates on an event, but none of the events look like what I need, which would be called something like OnRecordAdded.



I could write something in VBA to do the updates if I could figure out a way to trigger it when a song is added, although if there's some simpler way than VBA I'd like to hear about it.


I hope that's clear. Thanks.

[pbaldy]

What is the purpose of the contents table? The lp_id in the songs table is enough to display all songs on an album.

[cherold]

Apologies, I wrote that wrong. The Songs Table doesn't have an lp_id, it has an artist_id. I'll edit my question. Thanks.

[June7]

Subform is bound to LPContents and you select songs from a combobox that pulls list from Songs? You want to add a new song 'on-the-fly' while doing data entry? Use combobox NotInList event. This is a common topic. Review https://www.accessforums.net/showthread.php?t=70996

[cherold]

It's not a combobox, it's a text field. User types in the name of the song. I could call an event when the field is changed, but that means everytime I edit the title of an existing song in any way it would trigger the event, when I only need to know if a new record has been created.

[pbaldy]

I'd probably have the lp id in the song table, but you could use the after update event of the form with a test:

If Me.NewRecord Then

[June7]

Why not use a combobox? User can still type into the box.

[cherold]

I actually discovered that there is an AfterInsert event. I guess before I was looking at events for individual fields instead of the subform as a whole. So I call a script on that.