Form Subform of Subform Link

[diofree]

Hi guys,
I have a form which currently allows me to pick from a Combo Box from a list of candidates. When selected, if they have a record in another table, it will show those records in the subform.

Problem is that subform has LOTS of fields, some of it is tombstone data, the other is quantitative data.

I'd like to have the first subform simply show the tombstone data (this works now) and have another subform below that which would show the missing columns of data for the record (row) selected in the first subform.

How do i achieve this?

thanks!

 

[Minty]

There are a number of ways of doing this - here's one;
Create another subform with your additional data and the FK field of your First subform.
Create a unbound hidden textbox on your parent form and then on the current event of the first subform set the value of the unbound control to the FK of the subform1. Set the Parent / Child on subform2 to the main form unbound control / FK on subform 2

 

[diofree]

I believe I am following you.
FK = Foreign Key correct?

Can I not use Primary key? That may be my problem.

I tried following your steps but nothing is getting written to the textbox, so I must have that step wrong somehow.

On the Form's "On Current" I did:
=[Text87]=[Assessments Subform].[Form]![AssessmentID]

 

[Minty]

I'm on tablet so can't check syntax. FK yes foreign key.
On the subform 1 on current try something like me.parent.text87 = Me.assessmentID

 

[diofree]

Got it working, thanks!!

 

[Minty]

Good. Might be worth posting up what you did as an answer to others. It's not an obvious solution, but you can't easily link unrelated sub forms.

 

[diofree]

Darnit, I broke it again and can't recall what I did. I will record it this time!
Arg.

So SubForm1 points to Combo Box successfully.

Subform2 doesn't update when I click a record in Subform1.
Not sure if it's the requery or the Child/Parent fields.

 

[diofree]

Ok got it again.

Requery goes on Subform1 ON CURRENT as such:
Me.Parent![Subform2].Requery

Subform2 set data as follows:
Link Master Fields: [Subform1].Form![Subform1ID]
Link Child Fields: [Subform2ID]