G
Guest
Table Structure- Household (one record per address), People (one record per
person with related Household ID), Interest (all the Interests available
withing our organization), and AccountInterest (Household ID(related), People
ID(related), Interest ID(related), and Reference Type)
Integrity is enforced in all the relationships.
The purpose of the AccountInterest table is to associate people with
available Interests. The reason the Household ID is involved as well is that
an Interest may only apply to an individual, but it might also apply to the
whole family (Household)- the way to distinguish one from the other will be
tracked in the "Reference Type" field- whose value will be either Household
or People.
So right now, I have created 2 Interest subforms on my People form- one that
allows the user to add an Interest, and the other is a continuous subform
that lists the existing Interests. The subforms are linked to the main
People form via People ID.
The addInterest subform has a command button to add an Interest that works
via a RunSQL command:
SQL = "INSERT INTO AccountInterest ([Ref Type], [Household ID], [People ID],
[Interest ID]) " & _
"VALUES ('People', " & Me.Parent.[Household ID] & ", " &
Me.Parent.[People ID] & ", " & Me.ComboTitle.Value & ");"
DoCmd.RunSQL SQL
Me.Parent![Subform Interest].Form.Requery
The command pulls all the values needed from the parent forms and adds the
record to AccountInterest correctly, but I still get an error when I try to
close the People form after adding an Interest that says "You cannot add or
change a record because a related record is required in the table Household"
(click OK)
and then another- "You can't save the record at this time"
It's working just like I need it to, but how can I get rid of the error
messages? Is it a mistake to try to track the Household ID in that
AccountInterest table as well as the People ID?
Thanks, Amanda
person with related Household ID), Interest (all the Interests available
withing our organization), and AccountInterest (Household ID(related), People
ID(related), Interest ID(related), and Reference Type)
Integrity is enforced in all the relationships.
The purpose of the AccountInterest table is to associate people with
available Interests. The reason the Household ID is involved as well is that
an Interest may only apply to an individual, but it might also apply to the
whole family (Household)- the way to distinguish one from the other will be
tracked in the "Reference Type" field- whose value will be either Household
or People.
So right now, I have created 2 Interest subforms on my People form- one that
allows the user to add an Interest, and the other is a continuous subform
that lists the existing Interests. The subforms are linked to the main
People form via People ID.
The addInterest subform has a command button to add an Interest that works
via a RunSQL command:
SQL = "INSERT INTO AccountInterest ([Ref Type], [Household ID], [People ID],
[Interest ID]) " & _
"VALUES ('People', " & Me.Parent.[Household ID] & ", " &
Me.Parent.[People ID] & ", " & Me.ComboTitle.Value & ");"
DoCmd.RunSQL SQL
Me.Parent![Subform Interest].Form.Requery
The command pulls all the values needed from the parent forms and adds the
record to AccountInterest correctly, but I still get an error when I try to
close the People form after adding an Interest that says "You cannot add or
change a record because a related record is required in the table Household"
(click OK)
and then another- "You can't save the record at this time"
It's working just like I need it to, but how can I get rid of the error
messages? Is it a mistake to try to track the Household ID in that
AccountInterest table as well as the People ID?
Thanks, Amanda