Question So simple it's difficult (1 Viewer)

[GregoireLeGros]

I have two tables, people and households. One household can have several people living there. We cant have any person who does not have a household to belong to , i.e he or she has to have an address.

My people table has four fields
FirstName, LastName, personkey (autonumber unique key), foreignHouseholdKey ( relating to the household table)

My household table has three fields
Address1, address2, householdkey (autonumber unique key )

I want to create a form where I can add people, edit people, add households and edit households. So far I have a main form, form1 which has FirstName, LastName, personkey, foreignHouseholdkey and also a subform HouseholdSubform which contains Address1, address2, householdkey. Householdsubform links to form1 by the foreignHouseholdKey---HouseholdKey relationship.

I must not be able to create a person if there is not a household for him already existing. I must be able to add a new household. If I have a record in view I must trap adding a new household so I dont change the household unless I want to. I must be able to add new people to an existing household. I must be able to edit people and their households.

Here is a scenario.
I get a new person to add. I know I cant add him unless he has a household so I first create a household. The household key appears on the HouseholdSubform. I now add the person and Access says

How can this be achieved ? I am using Access 2000 but I could use 2007. I have coded all this using VBA but it is too complex, and I am sure I shouldnt have to use any code .

 

[wiklendt]

i would reverse the mainform/subform _ have your main form as the HOUSEHOLD, then the subform as people. you can then have a "edit people..." button which opens a form to enter new people.

do it this way because one household can have many people, but one person cannot have many house holds. (by the way, seeing as you require an address, how do you handle homeless people, or is that non applicable to your application? -sorry haven't time to look at your mdb just yet)

the way i like to set up most of my forms as seen int he pic below. this allows you to find existing records, edit them, and add and edit subform data. it has a listbox that you can navigate to existing records, or search by freetext if you have many of them.