Populating a record based on another's criteria 1

[spartansFC]

Hi

I've set up a form which lists all members in a family:

if a person is the main parent/carer (tick box), type (mother, father, child etc), forename, surname etc.

Then i have an address table to show or to keep a different record of their addresses.

I need to set up the forms so that the user doesn't have to enter the same address, if say all the members of the family live at the same address.

my tables are set up as.

tblParentChild
ParentChildID (Autonumber)
mainparent (yes/no)
parentchildtype (number)..... etc

tblAddress
AddressID (autonumber)
ParentchildID (Foreign Key)
current address (yes/no)
addressline1....... etc

i've used a current address tick box as i only want to show on the form the most current address for that person.

What i thought was if i had another tick box on tblAddress, MainParentCarerAddress, if the user ticks this, the afterupdate then looks at the mainparent in tblParentChild and then autofills the address details for that other person in the family.

The user than has a choice of autofilling the address, or just typing in a different address for that person.

I've attached a picture to show you the form.

Any ideas

Mikie

 

[MajP]

You could make this a many to many. So once an address is added you just assign it to other members or add/pick a different address. You would add a junction table.

juncTblParentChild_Address
parentChidlID_fk
addressID_fk

so if John Doe (parentChildid 1) and Jane Doe (parentChildid 2) have the same address (addressID 7) the table gets populated

1 7
2 7

You then can have a button that runs an insert query when you click "assign Primary Parent Address", and it simply adds the two foreign keys to the junction table.

I think you also need to make table tblParentChild a self referencing table to show family relations. How do you currently know that James Doe is a son of Jane Doe, not another primary parent? I think you need a field
mainParentID_fk
So John Doe and James Doe get assigned to Jane Doe by the value 2 in that field.

 

[spartansFC]

Hi MajP

I've finally got round to looking at your solution and i've done part 1 of it, created the junction table and created forms from that.

It works so far, i've even gone into the junction table and changed one of the address numbers to another number and the frmParentChildSubAddress changes accordingly.

So my next problem, well for me is to create the command button and what code i need in it.

I've googled around a bit and found an example:

insert into <table>
(column1, column2, column3)
values
([val1],[val2],[val3])

So i understand that i've got to insert a value into the junction table where:

ysnParentChildMain = yes and ysnCurrentAddress = yes

but then how does that update the fields on the form? Do i need 2 update query's. One to update the junction table, the other to update tblParentChildAddress

Mikie