Subform referencing subform

[jimleon]

I know this question has been answered in many ways on this and other forums, but cannot find and anwer that directly refers to my problem.

I have 2 subforms, Clients (which contains client details) and Invoice address. The contents of both form are derived from the table clientDetails. I would like the Invoice address form to reflect the address stored in the clientDetails table referenced by the client in the clients subform. Have tried various way but still have not been able to make it work sucessfully.

Any help would be great people.

[ChipR]

When you say referenced by the client in the clients subform, what do you mean exactly? If a record is being selected, you may use the subform's Current event to Filter the other subform, or update a field on the main form, which is used as the Link Master field (which is probably easiest).

[jimleon]

Cool, and how do you do that?

[ChipR]

Ok here's how I think I would do it. I'm going to make up some field names and stuff, so substitute your own.

Open the main form that contains the 2 subforms in design mode.

Add an unbound text box to your main form called txtSelectedClie nt.

In the Properties->Events of the Clients subform, find the On Current event, and add an Event Procedure.

Private Sub Form_Current()
Forms!MainForm!txtSelectedClient = ClientNumber
End Sub

Now, look at the InvoiceAddress subform properties by clicking the outside edge of the subform, not the little box in the corner. Under the Data tab, you should see Link Master Fields and Link Child Fields.

Set the Link Master Fields to txtSelectedClie nt, and set the Link Child Fields to ClientNumber, or whatever the name is of the actual field. Now the subform will only show the record that matches what is put in that box when it's selected in the Clients subform.

Once it's working, you can set the visible property of txtSelectedClie nt to No.

[jimleon]

Thanks Chip, that worked a dream was a bit confused about the Link Master Fields and Link Child Fields but now i know what each does.