Effective links between tables

[E-D]

Happy new year !
I have two different forms:
frmPeople linked to Table TblPeople
and frmAddress linked to table tblAddress
both tables have PeopleID as a primary key field

I want to use a Command button in form frmPeople in order to go from frmPeople to frmAddress, but I also want
the PeopleId field to be updated in frmAddress automatically
I used this code:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 however it didn't work for me.
How can I fix it?

 

[Pat Hartman]

Since you intend to store only one address per person, you should use a single table rather than two tables related 1-1. You can still use separate forms to display the data but you will avoid the problem of having to force a related row to be added to tblAddress for each row in tblPerson. Using a single table, you would then use the WHERE parameter of the OpenForm method to synchronize the Address form to the People form.

 

[E-D]

thank you for your kind answer, however
the tables I mentioned were only example for
better explaining my problem.
The real tables I wanted to relate are:
tblCostumers and tblOrders.
that's why I kept them apart.
Do you know how can I enforce such a relation ?

 

[Pat Hartman]

Usually the many-side table is displayed as a subform so just setting the master/child links properly takes care of getting the foreign key populated. When you use a separate form, you need to use the WHERE parameter of the OpenForm method to pass the linking key. Then in the many-side form's on Insert event, you need to populate the foreign key manually. This key field does not need to be visible on the form but it does need to be included in the form's recordsource.

Me.OrderId = Forms!YourFirstForm!OrderId

 

[E-D]

Thank you my friend
problem is solved !