Using VBA to get data from a parent table record to a child table record (address)

[roulette6]

Hello everyone:

I've done some searching for this and haven't found anything that solves my issue. I'm an intermediate Access user when it comes to tables, forms, and queries, but am a beginner when it comes to VBA. With that said, here's my question:



I have a table full of demographic information for a patient in a study. That patient's family members can join and their information is entered in a family member table. Since they often have different addresses, I want to use a check box indicating that the family member's address is the same as the patient's, in which case the corresponding address fields in the family table will pull the information from the patient table. Here's a simplified layout. I am using forms to populate the fields.

tbl_patient
patientID [primary key]
name
address

tbl_family
patientID [linked to primary key in tbl_patient]
name
addressSame
address

Thank you

[June7]

Are you using a form/subform arrangement for data entry - main form bound to patient and subform bound to family?

Use the checkbox OnChange event and try:

If Me.checkboxname = vbYes Then
Me.address = Parent.Address
Else
Me.address = Null
End If

[roulette6]

Are you using a form/subform arrangement for data entry - main form bound to patient and subform bound to family?

Use the checkbox OnChange event and try:

If Me.checkboxname = vbYes Then
Me.address = Parent.Address
Else
Me.address = Null
End If

Thanks for your reply. Currently, the form for family members is stand-alone and is not embedded in the form for the patient. The reason is that the patient form is fairly long. If necessary, I can make a form for the patient information that includes only the patient address and mark it invisible unless it's as easy to pull the patient's address without the form/subform arrangement.

[June7]

In addition to form/subform, consider a tab control on main form to organize other controls, including the subform.

It would be possible to pull the patient address onto the family form. However, you might find the form/subform arrangement the most convenient for data entry to both tables.