UPDATE: Have attached DB and added some explanation to the end of this post.
I have a rather complex set of tables and am trying to use the "On Not in List" function to add records to several tables. But it seems that something, perhaps the structure of the underlying query, is automatically creating an incomplete row in a table, and I can't find a way to stop it or improve it.
This is a database for record albums. There is a [LPs] table for the albums. Then is a [Songs] table for songs (i.e. "Yesterday") and a [Recordings] table for recordings of songs (i.e. the Beatles recording of "Yesterday"). And there is a [LPContents] table that indicates what recordings are on an album.
The ultimate goal is to be able to add new and existing songs from the form, but right now I'm focused on what happens when I add a new song title that is not currently in the [Songs] table. I have a field on the form that displays an album with its list of songs, and the song title is a drop down field. If I enter a title that is not in the [Songs] table then it triggers an "On Not in List" function which aims to add rows to all the relevant tables.
First the function adds the song to the [Songs] table. That works fine, giving me a row with the song title and a SongID.
Then my function adds the song to the [Recordings] table. But after the function has run I see that I now have two rows in the [Recordings] table. One that my function created, and a second that seems to be automatically created by Access when I put "Response = acDataErrAdded" at the end of the function so it knows the new title is now part of the [Songs] table.
The problem with the automatically created row in [Recordings] is that it has the Song ID but not the performer ID, which I also need. And because this row isn't created until my function is finished I don't have a way to add something to that row within that function.
So I need to either find a way within VBA to get a correct row, or I need to find a way to customize the information Access is using to create an automatic record. Which I'm guessing is based on the query populating my form, although I may be wrong about that.
Detailed Info:
For testing, I'm using a form that looks at a single Album. It's populated with the query:
Code:
SELECT LPs.LPID, LPs.Album, LPs.Performer_id, LPs.[Key Artist role]FROM LPs
WHERE (((LPs.LPID)=10));
I have a subform which displays a list of songs for that album.
Code:
SELECT [Songs].Title, [Songs].Excerpt, [Songs].Notes, [Songs].SongID, Recordings.song_id, Recordings.RecordingID, [LPContents].Recording_id, [LPContents].LP_id, [LPContents].Track, [LPContents].Side, Recordings.Rating, Recordings.Mood, Recordings.Genre, *
FROM ([Songs] INNER JOIN Recordings ON [Songs].SongID = Recordings.song_id) INNER JOIN [LPContents] ON Recordings.RecordingID = [LPContents].Recording_id
WHERE ((([LPContents].LP_id)=[LPID]));
I have a dropdown field in that list of songs which is populated with this query:
Code:
SELECT [Songs].SongID, [Songs].Title, [Songs].Excerpt, [Recordings].Comments, [Recordings].OriginalName
FROM [Songs] LEFT JOIN [Recordings] ON [Songs].SongID = [Recordings].song_id
ORDER BY [Songs].Title;
If text is entered into the dropdown that doesn't match any existing title, it triggers a "On Not in List" function. This function first adds the new song to the [Songs] table.
The next thing that needs to happen is to create a new row in [Recordings] that sets [Recordings].song_id to the new [Songs].[SongID] and sets [Recordings].ArtistID to the main form's Performer_id.
THIS IS THE ISSUE: I can add a new entry with those tow fields set, but whether I do that or not, when the function completes and I signal with acDataErrAdded that the song has been added to the dropdown list, Access will create a new row with [Recordings].song_id set to the new [Songs].[SongID] and all other fields unset, because Access doesn't know to set [Recordings].ArtistID to Performer_id, nor does it know that I've already created a new row in [Recordings] and that nothing else needs to be done.
Information on attached database:
I was asked to upload my database so people could see what's going on, so I'll add some explanation:
I've been slowly improving my albums database for years (it started as two flat database files - one for classical albums, one for pop albums - in something called RapidFile). I am now attempting to finally reform the database so all my albums are in a single set of tables that can list all my albums, which involves some radical restructuring.
I figured out the new structure, and am working on being able to add songs to that structure. I have created several "experimental" tables and forms that use the new structure. If you list objects in the "custom" display these are listed under "Song List Experiment." In the example above I simplified a few things, most notably changing the table names which currently are oddly named so as to not conflict with existing tables. So the tables are actually [LPContents (new)], [Recordings], [Songs (Compositions)]. I also have two queries specific to the experiment, [Songs query (New)] and [Recordings query].
Entering new songs is done through the form [LPs experiment], with song entry done in the contained subform [Songs subform experiment].
I'm currently working on entering new song titles in the "dropdown" field of the Songs subform. If a title entered is not in the dropdown it triggers the VBA function Dropdown_NotInList() in "Form_Songs subform experiment".
This function is supposed to do 3 things. 1) add the song to the Songs table; 2) query Songs table for the most recent addition then put that Song id in the Recordings table along with the key artists' id and role (composer or performer) that I take from the form; 3) Query for the new Recordings id and put that and the Songs id into the LPContents table, which connects recordings to LPs (aka albums or discs).
Currently I've commented out everything in the NotInList() function beyond adding a row to the Songs table and returning a code. As explained above, I"m having issues with step 2 and thus step 3 can't be implemented usefully.
Since it might affect how people think about approaching my problem, I"ll just mention that the ultimate goal goes beyond just adding new song titles. From the song list one will be able to (hopefully):
1) Add a new song title not in the dropdown list, which involves additions to the Songs, Recordings, and LPContents tables. (which is what I'm currently stuck on).
2) Add an existing recording to an album, which only involves an addition to LPContents.
3) Add a new recording of an existing song, which requires an add to Recordings and LPContents.
4) Add a new song that happens to have the same title as an existing song, which requires adding to the same tables as 1).
(For 2-4 I think I'll have to use dialogue boxes to ask the user which option is appropriate, which is problematic since Access will only let you do "yes" "no" "cancel" dialogue boxes, but that's an issue I'll worry about once I get 1) squared away).
Thanks so much to those who take a look at this. I hope I've given enough information.