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.