Results 1 to 5 of 5
  1. #1
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18

    "You cannot add or change a record because a related record is required" - but the record exists

    Hi everyone,

    I am creating a library database and have found a problem I can't seem to overcome. This is what I have done:

    1) I have a form based on a table for people (tblPeople) where I store the name of each person as well as their biographical data


    2) But some "people" are not real, instead being pseudonyms, so the tblPeople has a yes/no field for Pseudonym which, in the frmPeople, unhides/hides a subform
    3) This subform is based on a many-to-many tblPseudonym which has three fields (ID, PeopleID, Pseudonym)
    4) Both tblPseudonym.PeopleID and tblPseudonym.Pseudonym fields are related to the tblPeople.ID
    5) The frm.Pseudonym exists as a subform inside the frm.People and the Data property sheet is set as:
    Source Object: frmPseudonym
    Link Master Field: ID
    Link Child Field: Pseudonym
    4) The tblPseudonym.Pseudonym should be populated with the frmPeople.ID (the idea being that the person I just created automatically becomes a pseudonym when I check the yes field)
    5) The tblPseudonym.PeopleID should be populated by a combobox in the subform which is set as follows:
    Control Source: PersonID
    RowSource: SELECT..... (basically concatenates the three fields that compose the name of the person so it shows up as one single unit)

    Problem: Everything seems to be working, except that when I choose the real name (Mr A) to go with the pseudonym (Ms C) I get an error, and it only accepts the same name as the pseudonym (Ms C).

    Error message: You cannot add or change a record because a related record is required in table tblPeople.

    I'm extremely confused because I'm populating the combobox with values from the tblPeople. Which is actually a list of all names, those of the real people, and those of the pseudonyms.

    Thanks for any light you can shed on this.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It might be because the subform is trying to add a record that is related to a NEW record you are creating in the main form. The problem is that the new record does not exist yet because you have not saved it. Even though you see the data on the (main) form, it has not been committed to tblPeople. What you could do is save the new record before you unhide the subform.

  3. #3
    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
    You cannot add or change a record because a related record is required in table tblPeople.
    This message is telling you that you have a Parent--Child type of relationship. And that you are trying to add/refer to a new Child record and there is no associated Parent record.
    In effect, Access is preventing you from creating an orphan, or a child with no parent.

    You can only add a record to the child table if there is an associated Parent record.

  4. #4
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    Thanks for your input. I tried the following vba to try and create a new record but it did not work.

    Private Sub Form_Current()
    If Me.chckPseudonimo = True Then
    Me.subfrmPseudonimos.Visible = True

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPseudonimos", dbOpenDynaset)
    rs.MoveLast
    rs.MoveFirst
    rs.AddNew
    rs.Update

    Else
    Me.subfrmPseudonimos.Visible = False
    End If
    End Sub

    Then, as I was writing this post, it occurred to me I had misunderstood what you said: the "missing record" is not in the tblPseudonyms, but in the tblPeople. I went back and tried doing Me.Refresh, but it didn't work either.

    But then again, the only value that I'm allowed to put in the subformPseudonym.PersonID is exactly the same which is in the present record's frm.ID

    Sorry if this all seems very straightforward (or basic) to you, but I'm finding it quite difficult to understand how to solve the situation.

  5. #5
    TLC is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Portugal
    Posts
    18
    OK, I've tried something completely different:

    I created a new field in tblPeople called RealName (a number field). Then I created a query that lists all the people. Finally, I created a combobox in the frmPeople which is populated by the query. When I pick a name, its ID is copied into the RealName field.

    So far it seems to be working as a charm - but I wonder if it will bring me grief later on. Would this roundabout be considered safe or bad procedure (akin to not separating the address into several fields)?

    I'm still interested in getting an answer to my original problem, but I guess I'm still a bit behind in my access knowledge to achieve the solution right now. So, I'll keep at it after I finish this specific database.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-02-2017, 02:33 PM
  2. Replies: 6
    Last Post: 07-28-2016, 06:07 PM
  3. Replies: 15
    Last Post: 03-05-2015, 03:30 PM
  4. Replies: 1
    Last Post: 12-05-2014, 07:47 PM
  5. Replies: 40
    Last Post: 08-20-2013, 11:38 PM

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