By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,412 Members | 2,835 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

Pass subform name to function or sub

P: n/a
Hello

I am trying to pass the name of my subform to a function/sub but I can't
seem to get it to work. I am using an autokey function (ctrl E) to unlock
text boxes on a subform. I have a few forms in the database that will use
this function, so I need to be able to tell the code which form to unlock.
What I have is as follows:

Public Function akeyEdit()

If Form_frmname = True Then

activefrm = "subformname"
Call lockControls(activefrm)

End if

End Function

Sub lockControl(activefrm)
Dim frm As Form
Set frm = Forms(activefrm)
Dim Ctrl As Control

For each ctrl.....

End Sub

I tried passing the subform name - didn't work - erro - can't find form
I tried passing the form.subform name - didn't work - type mismatch

I've tried a combination of things, but still no luck. This is my first time
trying this. Please help!

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 1 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
The Form embedded in the Subform Control is not "open" and, thus, is not in
the Forms Collection. The name of a Control is never in the Forms
collection. The Form embedded in the Subform Control, when visible, is the
Form Property of that Subform Control. So, you are not going to be able to
use the Forms Collection in the way you were attempting.

To reference a Control, "C" on a Form "F2" embedded in a Subform Control
"SF" on a main Form "F1", the fully qualified reference is:

Forms!F1!SF.Form!C

(Note that the name of the embedded Form, "F2", is not used in the
reference.)

If you pass the names as text, then use the text format:

Forms("F1")("SF").Form("C")

For the literal text names, you can substitute the variables in which you
passed those names, eg.,

Forms(strMainFormName)(strSubformControlName).Form (strControlName)

It may seem a little complex at the start, but once you understand the way
Access refers to these things, it soon becomes "second nature." All the
above is "air code" and it's possible that I might have mistyped something,
so if something doesn't work, post back here with specific details.

Larry Linson
Microsoft Access MVP

"DMUM via AccessMonster.com" <u16673@uwewrote in message
news:6ba60f94ac0b0@uwe...
Hello

I am trying to pass the name of my subform to a function/sub but I can't
seem to get it to work. I am using an autokey function (ctrl E) to unlock
text boxes on a subform. I have a few forms in the database that will use
this function, so I need to be able to tell the code which form to unlock.
What I have is as follows:

Public Function akeyEdit()

If Form_frmname = True Then

activefrm = "subformname"
Call lockControls(activefrm)

End if

End Function

Sub lockControl(activefrm)
Dim frm As Form
Set frm = Forms(activefrm)
Dim Ctrl As Control

For each ctrl.....

End Sub

I tried passing the subform name - didn't work - erro - can't find form
I tried passing the form.subform name - didn't work - type mismatch

I've tried a combination of things, but still no luck. This is my first
time
trying this. Please help!

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 1 '07 #2

P: n/a
Hi Larry
Thank you very much for responding. Unfortuneatly I got lost in your example.
Can you describe a different way?

I am curious. you say the subform is not open? Really? I thought if the
main form was open, the subform was also open. if I try DoCmd.Open "subform"
the subform will open by itself. (Not within the Mainform)

The form embedded in the subform control would be my ? I'm lost with this.
I don't have a subform in my subform?

Let me see:

F2 is the main form
SF is my subform
C are my controls on my subform SF
What is F1?

Also, how am I passing the name of the subform to my sub? if sub name is
unlock would it be

unlock( Forms!F1!SF.Form!C)

what would my sub look like

sub unlock(??????)

end sub

Sorry I'm not getting it and requesting more explanation. Any additional
help would be most appreciated.

Thank you
Larry Linson wrote:
>The Form embedded in the Subform Control is not "open" and, thus, is not in
the Forms Collection. The name of a Control is never in the Forms
collection. The Form embedded in the Subform Control, when visible, is the
Form Property of that Subform Control. So, you are not going to be able to
use the Forms Collection in the way you were attempting.

To reference a Control, "C" on a Form "F2" embedded in a Subform Control
"SF" on a main Form "F1", the fully qualified reference is:

Forms!F1!SF.Form!C

(Note that the name of the embedded Form, "F2", is not used in the
reference.)

If you pass the names as text, then use the text format:

Forms("F1")("SF").Form("C")

For the literal text names, you can substitute the variables in which you
passed those names, eg.,

Forms(strMainFormName)(strSubformControlName).Form (strControlName)

It may seem a little complex at the start, but once you understand the way
Access refers to these things, it soon becomes "second nature." All the
above is "air code" and it's possible that I might have mistyped something,
so if something doesn't work, post back here with specific details.

Larry Linson
Microsoft Access MVP
>Hello
[quoted text clipped - 32 lines]
>>
Thank you
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 2 '07 #3

P: n/a
DMUM via AccessMonster.com wrote:
>I am trying to pass the name of my subform to a function/sub but I can't
seem to get it to work. I am using an autokey function (ctrl E) to unlock
text boxes on a subform. I have a few forms in the database that will use
this function, so I need to be able to tell the code which form to unlock.
What I have is as follows:

Public Function akeyEdit()

If Form_frmname = True Then

activefrm = "subformname"
Call lockControls(activefrm)

End if

End Function

Sub lockControl(activefrm)
Dim frm As Form
Set frm = Forms(activefrm)
Dim Ctrl As Control

For each ctrl.....

End Sub

I tried passing the subform name - didn't work - erro - can't find form
I tried passing the form.subform name - didn't work - type mismatch

A different way to do that kind of thing is to use the form
object instead of its name. I have no idea what the If
statement is checking, but the rest of it could be:

Public Function akeyEdit()

If Form_frmname = True Then
Call lockControls(Me.subformname.Form)
End if

End Function

Sub lockControl(frm As Form)
Dim Ctrl As Control

For Each Ctrl In frm
. . .

End Sub

--
Marsh
Jan 2 '07 #4

P: n/a
Hi Marshall

The if statment should have been:

If Form_frmname.Visible = True Then

Basicly I am using an autokey Macro that can be used from any form. I have 1
main form that contains a few subforms which are visible = false until the
user clicks on the menu button to make a form visible. Each subform contains
other subforms, so it's kind of a 3 layer approach.

so to illustrate -

Form Slpash screen contains subform MainMenu and subform TimeSheetMenu.
TimeSheetmenu remains invisible until the users clicks on the subform
mainMenu "TimeSheet" button. This makes subform Main Menu invisible and
subform TimeSheet visible

SubForm MainMenu contains 2 additional subforms which are used for data
entry/view

Subform TimeSheetMenu contains 1 subform used for data entry/view

Note: I have more then 2 of these forms.

The autokey - in this instance ctrl^E is used to unlock all controls on a
given sub-subform (the dataentry/view subform). I have to identify which
form becuase some forms require that a few text boxes remain locked or some
additional issues that need to be addressed depending on the form.

So when the user hits Ctrl^E,

1. I need the code to verify which form is visible
2. I need the code to unlock the controls on the correct form sub-subform
(dataentry/view subform)

Because i am not calling the function from an actual form I cannot use ME.
The code for the autokey is in a module.

Hope I gave you a pretty good picture.

Thanks

Marshall Barton wrote:
>>I am trying to pass the name of my subform to a function/sub but I can't
seem to get it to work. I am using an autokey function (ctrl E) to unlock
[quoted text clipped - 24 lines]
>>I tried passing the subform name - didn't work - erro - can't find form
I tried passing the form.subform name - didn't work - type mismatch

A different way to do that kind of thing is to use the form
object instead of its name. I have no idea what the If
statement is checking, but the rest of it could be:

Public Function akeyEdit()

If Form_frmname = True Then
Call lockControls(Me.subformname.Form)
End if

End Function

Sub lockControl(frm As Form)
Dim Ctrl As Control

For Each Ctrl In frm
. . .

End Sub

--
Marsh
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 2 '07 #5

P: n/a
"DMUM via AccessMonster.com" wrote
Thank you very much for responding. Unfortuneatly
I got lost in your example. Can you describe a different way?
Well, first, let's get some terminology straight. There is no object in
Access called a "Subform", but there is an object called a "Subform
Control". Sometimes we say "subform" but we shouldn't as it is quite
obviously confusing.

A "Subform Control" has a "Source Object" which is a Form... that Form,
unless you have done some tricks with code, can also be opened directly, as
well as being the "Source Object" (aka "embedded in") the Subform Control.

{I hope this doesn't muddy the waters, but you can also use Datasheet View
in a Subform Control, but I strongly advise against Datasheet View either
alone or in a Subform, because there are too many things that an unwary user
can do that will be harmful.}
I am curious. you say the subform is not open? Really?
Yes, really.
I thought if the main form was open, the subform
was also open.
Back to the definition... no such thing as a Subform... only Subform
Controls, into which you embed a Form as Source Object. Controls aren't Open
or Closed... they have different Properties. Thus, not as you thought, that
instance of the Form exists as the Form property of the Subform Control.
if I try DoCmd.Open "subform" the subform will open
by itself. (Not within the Mainform)
Not germane to the discussion. It is a Form, not a Subform, and can be
Opened. When it is Opened directly, it is open and will be in the Forms
collection.
The form embedded in the subform control would be
my ? I'm lost with this.
It wouldn't be _your_ anything, it would be the SourceObject of the Subform
Control, and would be addressed as the Form property of the Subform Control.
I don't have a subform in my subform?
No, firstly, you don't have "a Subform," so you couldn't have anything in
it. Back to the clarification of terminology.
Let me see:

F2 is the main form
No, "F2" is the Form that is Source Object of the Subform Control, aka the
Form embedded in the Subform Control.
SF is my subform
No, SF is the Subform _Control_ which resides on the main Form F1. There's a
difference between the Subform Control and the Form that is its Source
Object -- they are different Access objects, have different Properties, and
serve different functions.
C are my controls on my subform SF
No, C represents a Control on Form F2, which is the Source Object of the
Subform Control SF, which is a Control on Form F1, which is the main Form.
What is F1?
F1 is the main Form.

Draw a big box on a sheet of paper to represent F1, the main Form. Inside
that box, draw a smaller box to represent SF, the Subform Control. Inside
the SF box, you can draw a picture of whatever's on Form F2, which is the
SourceObject of SF (aka the Form embedded in SF), and what you'll have drawn
are representations of the Controls C that reside on Form F2.
Also, how am I passing the name of the subform to my sub? if sub name is
unlock would it be
I wouldn't pass the "Name", but a Form object. And that would be, if your
code is in the main Form F1's module:

Dim frmPassMe as Form

Set frmPassMe = Me.SF.Form

Unlock (frmPassMe)
unlock( Forms!F1!SF.Form!C)
what would my sub look like
sub unlock(??????)
end sub
Sub Unlock (pfrmForm as Form)

do whatever you are going to do
to address a Control by text name, use something like

pfrmForm("NameOfControl").Locked = False

End Sub

If you do not have the same Controls on each of the Forms used as Source
Objects in Subform Controls, then, instead of passing the Form object, you
will need to pass the Control Object (unless you are going to use code to
open all the Controls of particular types in that Form's Controls
Collection).
Sorry I'm not getting it and requesting more explanation.
Sorry, I wasn't clear. Hope this is clearer. If not, post back, and either I
or someone else will take a shot at additonal clarification.

Larry Linson
Microsoft Access MVP


Jan 2 '07 #6

P: n/a
DMUM via AccessMonster.com wrote:
>The if statment should have been:

If Form_frmname.Visible = True Then

Basicly I am using an autokey Macro that can be used from any form. I have 1
main form that contains a few subforms which are visible = false until the
user clicks on the menu button to make a form visible. Each subform contains
other subforms, so it's kind of a 3 layer approach.

so to illustrate -

Form Slpash screen contains subform MainMenu and subform TimeSheetMenu.
TimeSheetmenu remains invisible until the users clicks on the subform
mainMenu "TimeSheet" button. This makes subform Main Menu invisible and
subform TimeSheet visible

SubForm MainMenu contains 2 additional subforms which are used for data
entry/view

Subform TimeSheetMenu contains 1 subform used for data entry/view

Note: I have more then 2 of these forms.

The autokey - in this instance ctrl^E is used to unlock all controls on a
given sub-subform (the dataentry/view subform). I have to identify which
form becuase some forms require that a few text boxes remain locked or some
additional issues that need to be addressed depending on the form.

So when the user hits Ctrl^E,

1. I need the code to verify which form is visible
2. I need the code to unlock the controls on the correct form sub-subform
(dataentry/view subform)

Because i am not calling the function from an actual form I cannot use ME.
The code for the autokey is in a module.

Hope I gave you a pretty good picture.

Sorry, but that is a pretty muddy picture. Using a macro to
control this kind of thing is confusing because of the
difficulty in identifying the form object (especially if
it's displayed by a subform control) that you want to
reference. If the VBA procedure the macro calls "knows"
it's a main form then you can use Screen.ActiveForm.
Otherwise, you may get somethimg useful through
Screen.ActiveControl and the Parent property, but I get lost
somewhere in this situation.

I see that Larry is doing a better job of clarifying things
than I am, so I should leave to him and not get in the
middle and muddle things any more.

--
Marsh
Jan 2 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.