Reference subform controls

D

Dylan Moran

I think there should be a simple solution to my problem. I have searched and
found similar questions and tried to implement suggested solutions, but to
date no success, so I am hoping you will be patient with me and help with a
question that appears to have been asked many times before. Thanks in advance
for your patience and time.

I have a form (frm02Company) with a subform (frm08ReturnAnalysis). And my
searching the forum has identified that a subform is actually a *control*. I
have also read that the subform *control* is sometimes not the name of the
subform itself, however I am unsure of how to identify the name of the
subform *control*. The subfom was created using the wizard and utilised an
already created form, so I am guessing that the subform *control* is most
likely the same name as the subform itself.

Any help as to how to identify the subform *control* name would be
appreciated.

But my real problem is referencing the controls on this subform in VBA.

Below is the code as it stands.

Basically I call a procedure in a module that I want to enable and unlock
controls on my subform.

In the on_current event of my subform (note subform on_current event) I have
this:

Private Sub Form_Current()

Call EnableAndUnlock

End Sub

And in a module, the procedure I call I have this:

Sub EnableAndUnlock()

On Error GoTo ErrorPoint

' ***** EnableAndUnlock code starts here *****

Forms!frm08ReturnAnalysis.year.Enabled = True
Forms!frm08ReturnAnalysis.Risk.Enabled = True

' Controls (Not Locked)

Forms!frm08ReturnAnalysis.year.Locked = False
Forms!frm08ReturnAnalysis.Risk.Locked = False

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " _
& Err.Description, vbExclamation, _
"Unexpected Error"
Resume ExitPoint

End Sub

The error that I get is
"Cannot find the form 'frm08ReturnAnalysis'

Any assistance in how to accurately reference the controls on my subform
from the called procedure would be most appreciated. An example using my
code would be more than appreciated, it would make my week!

Thanks
 
D

Dennis

You reference fields on your sub form like this

Forms!frm02Company!frm08ReturnAnalysis.Form.year.Enabled = True
 
G

George Nicholson

Any help as to how to identify the subform *control* name would be
appreciated.
Click to expand...

With the parent form in Design view and the Properties window open, click on
the subform's "container". You have the Subform control selected when you
see properties named "Link Child Fields" and "Link Master Fields". It's the
only type of control with those properties.

At that point the "Source Object" property will contain the name of the
subform and the "Name" property will contain the name of the control, which,
as you state, may or may not be the same.

The Forms!FormName syntax *never* works when referencing a subform. The
Forms collection only contains open Forms. Subforms simply aren't included
in that collection. That's why you need the name of the control containing
the subform.

You will probably end up with code something like:
Forms!frm02Company.subformCtrlName.Form.year.Enabled = True
or, since Form is the default property, it can be shortened to:
Forms!frm02Company.subformCtrlName.year.Enabled = True

HTH,
 
D

Dylan Moran

Many thanks for your assistance Dennis and George.
Works a treat and my understanding is much better.

--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia


George Nicholson said:
Any help as to how to identify the subform *control* name would be
appreciated.
Click to expand...

With the parent form in Design view and the Properties window open, click on
the subform's "container". You have the Subform control selected when you
see properties named "Link Child Fields" and "Link Master Fields". It's the
only type of control with those properties.

At that point the "Source Object" property will contain the name of the
subform and the "Name" property will contain the name of the control, which,
as you state, may or may not be the same.

The Forms!FormName syntax *never* works when referencing a subform. The
Forms collection only contains open Forms. Subforms simply aren't included
in that collection. That's why you need the name of the control containing
the subform.

You will probably end up with code something like:
Forms!frm02Company.subformCtrlName.Form.year.Enabled = True
or, since Form is the default property, it can be shortened to:
Forms!frm02Company.subformCtrlName.year.Enabled = True

HTH,
Click to expand...
 
Top