linking subform to subform?

[Guest]

Hi Everyone,
I have a form that has the Delivery Header as the main form, a subform
showing containers, and another subform showing items in the containers.
Record ID is the link field thru the form/subform1/subform2.

The problem is I only want the detail (subform2) to show for the selected
container in subform1, not all the detail relating to the Record ID in the
main form.

There is a link field between the two tables on the subforms, Container_ID,
but Access isn't giving me that option, only allowing me to link subform 2 to
the main form.

As always, I appreciate your kind help.
Regards,
Pat.

 

[Guest]

Is this what you want?

Main form: DELIVERY HEADER
displays "Delivery A" info

Subform: CONTAINERS
displays "Delivery A" - Container "A1" info (current record)
displays "Delivery A" - Container "A2" info
displays "Delivery A" - Container "A3" info

Subform CONTENTS
displays "Delivery A" - Container "A1" contents "A1C1"
displays "Delivery A" - Container "A1" contents "A1C2"
displays "Delivery A" - Container "A1" contents "A1C3"
displays "Delivery A" - Container "A1" contents "A1C4"

If that's the case, then Access won't support it using the automatic
parent/child linking between subform CONTAINERS and subform CONTENTS. The
reason is that it doesn't allow a parent form to be a "list view".

Instead, don't use the parent/child linking. Do it yourself via the
"OnCurrent" event of CONTAINERS so it alters the record source of CONTENTS.

In subform_CONTAINERS,
Private Sub Form_Current()
Dim frm_CONTENTS as Form
Dim SQL As String

Set frm_CONTENTS = Me.subform_CONTENTS.Form
SQL = "SELECT {column list} FROM [tbl_CONTENT] WHERE ([CONTAINER] = """ &
Me.ContainerID & """)"
frm_CONTENTS.RecordSource = SQL
Set frm_CONTENTS = Nothing
End Sub

 

[Roger Carlson]

I'm not sure what you're asking, exactly, but on my website
(www.rogersaccesslibrary.com), are two small Access database samples called
"SubForm3Levels.mdb" and "SynchronizedSubforms.mdb" which might get you
started.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[David]

Hi,

Trying this method vs. putting a text box on main form which refers to a
field in SubForm A, to be linked to by SubForm B (as I cannot get the text
box to show up in the LINK FIELD DROP DOWN LIST.)

I get an error on the syntax you use in the statement:
Me.subform_CONTENTS.Form
"Compile Error: Method or data member not found". (My subform is called
fsub_Color, so my command is: Set frm_COLOR = Me.subform_fsub_Color.Form)

How should that be formatted?

OR, what I would really like to be able to do is like stated above: putting
a text box on main form which refers to a field in SubForm A, to be linked to
by SubForm B (as I cannot get the text box to show up in the LINK FIELD DROP
DOWN LIST.)

Thanks.