Help w Subforms

[painterz]

Hello All,

I've attached my db. My main form is Neighborhood Input Form. On that form I have subforms. There is one, Family subform, I've recently added and can't seem to get it to work how I want. Let me start from the beginning

Original design: one family per address

New design (currently not working): multiple families per address. My intent is the Family subform shows all of the families living at the address. The user can click on the family name and the Neighbors subform will show all of the people in the family. The Neighbors subform's master/child link to the Family subform is txtsfrmPeopleID and PeopleID.

Problem: When the address is sold and a new family moves in. Now, I've lost my connection to the Family subform. I can add the people to tblPeople but tblFamily doesn't update so the people won't show when I come back to the address. It's not "backward compatible".

Do you know how I can solve this? I was trying to avoid code.

Thanks!

 

[Ranman256]

Then the tPeople table will need a FamilID
tPeople tbl
-----------------
PeopleID (auto)
FirstName
LastName
gender
FamilyID (FK to tFamily)

tFamily table
----------------
FamilyID (auto)
LastFirst (text to show the family name)
AddrID (FK to the tAddress table)


master table tFAMILY ,
add all people from the tPeople in a subform to assign FamilyID to each person.

then set the master form for this family to an address ID. (combo box looking at tAddr)

 

[painterz]

Hi Ranman,

I already have FK FamilyID in tblPeople and FK AddressID in tblFamily.

The master/child on the Family Subform is AddressID linked to the master form, Neighborhood Input Form.

"master table tFAMILY ,
add all people from the tPeople in a subform to assign FamilyID to each person."

I'm a little lost on the above portion. How do I set all of the people to the same FamilyID? What do you mean by 'master table tFamily'?

Thanks

 

[Ranman256]

create a master form for family
then add a subform for People. (linked to the familyID in master form)

MY way is:
on the master form add a list box, that shows all people (sorted by lastname/firstN)
then make a query to add the selected item in the listbox to the family,using an update query.

dbl-click on the person in the list box, this will run the query and add the family ID to the person in the list box.

sql for 'qaAdd1Person2Family' qry:
update tPerson set tPerson.FamilyID = forms!frmMaster!txtID where tPerson.PersonID = forms!frmMaster!lstBoxPerson

when you dbl-click the listbox person, the dbl-click event is:

 sub lstBoxPerson_doubleClick()
   docmd.setwarnings false
   docmd.openquery 'qaAdd1Person2Family' 
   docmd.setwarnings true
 end sub

Now you select the FAMILYID in the master record
then add all people into a family group just with a double click

 

[painterz]

It sounds like a good plan. However, I'm trying to retrofit an existing db. If I make the Family a master to the People, I have a whole lot of rewriting to do in the code. At the rate I'm going now, it might be better to scrap all the forms and start over.