Reference to Subform Control

[Guest]

I'm going crazy trying to get this right! I have a main form [New Invoice2]
and on it I have two subforms: [HDRPLATSubform] and [LINPLATSubform].
[LINPLATSubform] is nested within {HDRPLATSubform]. [LINPLATSubform] and
HDRPLATSubform] are related to one another by a field called [INVNUM]. I'm
trying to tie them both the the field [Invoice Number] on the main form so
that when I type in the invoice number, it appears on my subforms and on
their underlying tables. I was thinking of useing either the AfterUpdate
event or the OnCurrent event to do this, but I can't get the syntax right to
save my life! Please help.

Further (and perhaps unnecessary) explanation: This mainform is based on a
query that brings together information from the Contacts table, the Order
Entry table and the New Invoice table. When I try to include the tables
LINPLAT and HDRPLAT, the query becomes non-updateable. My idea was to
include them as subforms. However, one table [LINPLAT] has no fields that
can be related to the mainform. It is, however, related to the other table
[HDRPLAT] by the field [INVNUM]. My solution was to nest [LINPLAT] into
[HDRPLAT] and use the [Invoice Number] field from the main form to tie them
together. I've tried so many combinations of references my head is numb!
How can I tie the value entered into the field [Invoice Number] on the
mainform to one of the [INVNUM] fields on the subforms?

 

[strive4peace]

main form, subform, LinkMasterFields, LinkChildFields
---


Hi Jaybird,

since both subforms link on InvNum, which is on the main form (isn't
it?), do this:

add InvNum to each of your subforms

make sure the control name on (1) mainform and (2) each of the subforms
--> is called Invnum as well

Then, from the design view of the main form

1. turn on the properties window
(r-click anywhere and choose Properties from the shortcut menu)

2. click ONE time on the subform controls if it is not already selected

3. click on the DATA tab of the Properties window

SourceObject --> drop list and choose the name of the form you will use
as a subform

LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;maincontrolname
LinkChildFields --> MainID;childcontrolname

where MainID is an autonumber field (InvNum in your case) in the parent
table and a Long Integer field in the child table

the link field properties actually use controls, not fields -- so the
controls you reference must be ON each of the respective forms and the
NAME property is what you need to reference -- if a control is bound, I
usually make the Name of the control the same as the ControlSource (what
is in it)

It is common to set the Visible property to No for the control(s) used
in LinkChildFields

then, while still on the subform control, click the ALL tab -- change
the Name property to match the SourceObject property (minus Form. in the
beginning if Access puts it there)

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

Personally, I edit subforms directly, not from inside the main form -- I
have had trouble with Access putting property changes in the wrong place
for RowSources and RecordSource. Since it happens there occassionally,
for major changes, I go to the design view of the "sub"form directly
from the databse window when the main form is closed.
the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I'm going crazy trying to get this right! I have a main form [New Invoice2]
and on it I have two subforms: [HDRPLATSubform] and [LINPLATSubform].
[LINPLATSubform] is nested within {HDRPLATSubform]. [LINPLATSubform] and
HDRPLATSubform] are related to one another by a field called [INVNUM]. I'm
trying to tie them both the the field [Invoice Number] on the main form so
that when I type in the invoice number, it appears on my subforms and on
their underlying tables. I was thinking of useing either the AfterUpdate
event or the OnCurrent event to do this, but I can't get the syntax right to
save my life! Please help.

Further (and perhaps unnecessary) explanation: This mainform is based on a
query that brings together information from the Contacts table, the Order
Entry table and the New Invoice table. When I try to include the tables
LINPLAT and HDRPLAT, the query becomes non-updateable. My idea was to
include them as subforms. However, one table [LINPLAT] has no fields that
can be related to the mainform. It is, however, related to the other table
[HDRPLAT] by the field [INVNUM]. My solution was to nest [LINPLAT] into
[HDRPLAT] and use the [Invoice Number] field from the main form to tie them
together. I've tried so many combinations of references my head is numb!
How can I tie the value entered into the field [Invoice Number] on the
mainform to one of the [INVNUM] fields on the subforms?

 

[Guest]

Crystal,

Many thanks for your thorough response... I didn't know about the
difference between the subform and the subform control. It makes sense,
given what I know about tab controls. I'm not sure if my syntax would have
worked under those circumstances or not, because you brought me back to the
real source of the problem: My child and master relationships weren't
working properly. Somehow during all of my tweaking, I renamed one of my
controls and that screwed everything up. Once that was fixed, and I removed
all of the event procedures that I had written, it worked fine. Man, I could
really use a second set of eyes. Such a simple thing... Thanks again.

--
Why are you asking me? I dont know what Im doing!

Jaybird

main form, subform, LinkMasterFields, LinkChildFields
---


Hi Jaybird,

since both subforms link on InvNum, which is on the main form (isn't
it?), do this:

add InvNum to each of your subforms

make sure the control name on (1) mainform and (2) each of the subforms
--> is called Invnum as well

Then, from the design view of the main form

1. turn on the properties window
(r-click anywhere and choose Properties from the shortcut menu)

2. click ONE time on the subform controls if it is not already selected

3. click on the DATA tab of the Properties window

SourceObject --> drop list and choose the name of the form you will use
as a subform

LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;maincontrolname
LinkChildFields --> MainID;childcontrolname

where MainID is an autonumber field (InvNum in your case) in the parent
table and a Long Integer field in the child table

the link field properties actually use controls, not fields -- so the
controls you reference must be ON each of the respective forms and the
NAME property is what you need to reference -- if a control is bound, I
usually make the Name of the control the same as the ControlSource (what
is in it)

It is common to set the Visible property to No for the control(s) used
in LinkChildFields

then, while still on the subform control, click the ALL tab -- change
the Name property to match the SourceObject property (minus Form. in the
beginning if Access puts it there)

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

Personally, I edit subforms directly, not from inside the main form -- I
have had trouble with Access putting property changes in the wrong place
for RowSources and RecordSource. Since it happens there occassionally,
for major changes, I go to the design view of the "sub"form directly
from the databse window when the main form is closed.
the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I'm going crazy trying to get this right! I have a main form [New Invoice2]
and on it I have two subforms: [HDRPLATSubform] and [LINPLATSubform].
[LINPLATSubform] is nested within {HDRPLATSubform]. [LINPLATSubform] and
HDRPLATSubform] are related to one another by a field called [INVNUM]. I'm
trying to tie them both the the field [Invoice Number] on the main form so
that when I type in the invoice number, it appears on my subforms and on
their underlying tables. I was thinking of useing either the AfterUpdate
event or the OnCurrent event to do this, but I can't get the syntax right to
save my life! Please help.

Further (and perhaps unnecessary) explanation: This mainform is based on a
query that brings together information from the Contacts table, the Order
Entry table and the New Invoice table. When I try to include the tables
LINPLAT and HDRPLAT, the query becomes non-updateable. My idea was to
include them as subforms. However, one table [LINPLAT] has no fields that
can be related to the mainform. It is, however, related to the other table
[HDRPLAT] by the field [INVNUM]. My solution was to nest [LINPLAT] into
[HDRPLAT] and use the [Invoice Number] field from the main form to tie them
together. I've tried so many combinations of references my head is numb!
How can I tie the value entered into the field [Invoice Number] on the
mainform to one of the [INVNUM] fields on the subforms?

 

[strive4peace]

you're welcome, jaybird happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*