Results 1 to 12 of 12
  1. #1
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82

    Code to add entry to table somehow getting me an extra incomplete entry

    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.
    Attached Files Attached Files
    Last edited by cherold; 11-04-2019 at 02:53 PM. Reason: attach DB and explain DB

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Well, that should not happen. Post your procedure code.

    What do you mean by "create a new row"? If you have made a selection in combobox then new record is already initiated.
    If form and subform master/child links are set, then Performer_ID should automatically populate to subform record.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might have to post a db copy.
    If the form is based on a query that is updatable, perhaps you are adding a record in code but you are also adding to a related table because of the query? Or you have Cascade Updates set in your relationships and by adding a value in code it propagates a value into some other table? Either of those 2 notions are based on the understanding that the 'duplicate' record is incomplete.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As June and micron have suggested---posting a copy of your database may get you more focused responses.
    Good luck.

  5. #5
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    I attached the database to the question and added some information at the bottom. Thanks for taking a look!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Strongly advise not to use spaces nor punctuation/special characters in naming convention.

    Normally, a form is used to do data entry to only one table. [Songs subform experiment] RecordSource includes 3 tables. Which one should this form enter data into - Recordings? I would not include [Songs (Compositions)] in RecordSource. Its fields are available in the combobox RowSource. Have textboxes reference combobox columns to display associated info. I don't understand why you include dependent table [LPContents (New)] in RecordSource. Also doesn't make sense to me for combobox RowSource to include Recordings table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    Normally, a form is used to do data entry to only one table. [Songs subform experiment] RecordSource includes 3 tables. Which one should this form enter data into - Recordings?
    It depends on what the user does. If they are adding a new song title, then the first thing that has to happen is a new row in [Songs (Compositions)], since you can't add a row to [Recordings] until you know the song id, and in the same way you can't add something to [LPContents] until you have a recordings id.

    However, if the user were to select an existing item from the dropdown list, that means there is already a row for the song in [Songs (Compositions)] and most likely (though not guaranteed) a row in [Recordings]. So you might only need to add a row to [LPContents].

    I would not include [Songs (Compositions)] in RecordSource. Its fields are available in the combobox RowSource. Have textboxes reference combobox columns to display associated info.
    I'll take a look at that. This is all adapted from my original forms which had a somewhat different underlying structure. There isn't a dropbox in the old interface, so when I added this one I didn't look to see how that could allow me to simplify things elsewhere.

    I don't understand why you include dependent table [LPContents (New)] in RecordSource.
    The subform is listing songs that are on the currently displayed album. The currently displayed album is hardcoded in the experimental form to make my life easier, but there are hundreds of albums). [LPContents (New)] is the key that says which recordings are on which Albums. Without "WHERE ((([LPContents (New)].LP_id)=[LPID]));" it would just display every recording in the database.

    Also doesn't make sense to me for combobox RowSource to include Recordings table.
    As currently set up [Recordings] is probably not necessary. I put it in because I might use that information when choosing existing songs/recordings. To explain further:

    I said in my original question that I might try and use the Yes-no-cancel dialogue box to ask the user when they add an existing song title if this is for a different song with the same title, an existing recording, or a new recording. But there is another possibility, which would be to include information in the dropdown so I can choose from multiple recordings. So if the dropdown says:

    Yesterday - The Beatles
    Yesterday - Peggy Lee

    Then I could choose the specific recording I wanted from the dropdown. Of course, there is still the issue of the user wanting to add a new recording of Yesterday by the Beatles, like a live one. I might still need a dialogue box to ask that question, but at least that means less questions and an easier time with the yes-no-maybe limitation.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Again, normally a form should be bound to table or query that allows entry/edit to only one table.
    Whether or not code behind that form will add data to other tables does not alter that guidance.
    Including related dependent table in RecordSource may be contributing to the difficulties you encounter. I haven't explored that yet to confirm but I suggest you simplify data sources and then try coding.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    The problem is the form is used both for data entry and for display, and I can't think of a way to display all information satisfactorily and still be able to add and edit within the single-table constraint. I don't want the redesign to result in an inferior user experience to the current one.

    I'm beginning to think that my best bet for saving the user experience and proper functionality is to simply accept that useless rows will be added to [Recordings] and periodically run a query to delete them. It's horribly inelegant and I was hoping to avoid that but I would think it would work just fine.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Really should use form/subform arrangements for entry and display of related data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest you stop and fix your field names, object names and table relationships.

    Besides having spaces in object names, there special characters (parenthesis) in 3 field names, at least 3 tables names, several in query names and several in form names .
    There are 2 reserved words used as field names - "Single" and "Year".

  12. #12
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    Yeah. Once this is complete I'm planning to rename everything more sensibly, so I'll look at all my naming issues. I was extra sloppy with the experimental tables because I knew they were all going to change, but I'll admit there are problematic naming non-conventions throughout.

    Quote Originally Posted by ssanfu View Post
    I would suggest you stop and fix your field names, object names and table relationships.

    Besides having spaces in object names, there special characters (parenthesis) in 3 field names, at least 3 tables names, several in query names and several in form names .
    There are 2 reserved words used as field names - "Single" and "Year".

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Entry choices dependant on another field entry
    By BlackBooks in forum Forms
    Replies: 2
    Last Post: 11-06-2017, 08:18 AM
  2. Replies: 4
    Last Post: 12-21-2015, 10:57 PM
  3. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  4. Replies: 2
    Last Post: 05-20-2013, 05:23 PM
  5. Replies: 6
    Last Post: 09-27-2012, 08:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums