Referencing subform

[gmazza via AccessMonster.com]

Hey there,
I need to reference my subform in a module I have that is creating an audit
trail. The name of the subform is getting passed in through a function. The
name of the subform is BinderSub and it gets passed to the function in a
string called FormName.
When I try and start my For loop for the audit trail, like so:
For Each C In Forms(FormName).Controls

It errors on this as it says it can't find the form BinderSub referred in a
macro expression or VB Code.
Can anyone help me reference this properly without actually putting the name
of the form!subformname because I need this to work with all my databases and
all my forms and I don't want to change the code in the module for the audit
trail.
Thanks in advance!

 

[Stuart McCall]

Hey there,
I need to reference my subform in a module I have that is creating an
audit
trail. The name of the subform is getting passed in through a function.
The
name of the subform is BinderSub and it gets passed to the function in a
string called FormName.
When I try and start my For loop for the audit trail, like so:
For Each C In Forms(FormName).Controls

It errors on this as it says it can't find the form BinderSub referred in
a
macro expression or VB Code.
Can anyone help me reference this properly without actually putting the
name
of the form!subformname because I need this to work with all my databases
and
all my forms and I don't want to change the code in the module for the
audit
trail.
Thanks in advance!

Screen.ActiveForm!SubformControlName.Form

The subform control name must be consistent across all your forms for this
to work correctly.

 

[gmazza via AccessMonster.com]

Thanks for your reply.
In my Audit Trail function I added this:
For Each C In Screen.ActiveForm!SubformControlName.Form.Controls
etc

and got this error:
<Database Name> can't find the field 'SubformControlName' referred to in your
expression.

My for is called Binder and my subform is called BinderSub.
When i mke a change to either, it goes into the same routine to do the Audit
Trail so it needs to work for the mainform or the subform.
Thanks!

Hey there,
I need to reference my subform in a module I have that is creating an

[quoted text clipped - 17 lines]

trail.
Thanks in advance!

Screen.ActiveForm!SubformControlName.Form

The subform control name must be consistent across all your forms for this
to work correctly.

 

[Stuart McCall]

Thanks for your reply.
In my Audit Trail function I added this:
For Each C In Screen.ActiveForm!SubformControlName.Form.Controls
etc

and got this error:
<Database Name> can't find the field 'SubformControlName' referred to in
your
expression.

My for is called Binder and my subform is called BinderSub.
When i mke a change to either, it goes into the same routine to do the
Audit
Trail so it needs to work for the mainform or the subform.
Thanks!

Hey there,
I need to reference my subform in a module I have that is creating an

[quoted text clipped - 17 lines]

trail.
Thanks in advance!

Screen.ActiveForm!SubformControlName.Form

The subform control name must be consistent across all your forms for this
to work correctly.

If you look carefully, you'll see I posted SubformControlName. That refers
to the subform control on your main form which houses (hosts) the subform.
Then you refer to the .Form property of that to get the hosted subform
object.

 

[John W. Vinson]

Hey there,
I need to reference my subform in a module I have that is creating an audit
trail. The name of the subform is getting passed in through a function. The
name of the subform is BinderSub and it gets passed to the function in a
string called FormName.
When I try and start my For loop for the audit trail, like so:
For Each C In Forms(FormName).Controls

It errors on this as it says it can't find the form BinderSub referred in a
macro expression or VB Code.
Can anyone help me reference this properly without actually putting the name
of the form!subformname because I need this to work with all my databases and
all my forms and I don't want to change the code in the module for the audit
trail.
Thanks in advance!

A Subform is not in the Forms collection; you must reference it via the
mainform and the name of the Subform control within that mainform. The Name
property of the form you're using as a subform is irrelevant!

I would suggest passing the *form object* itself: in the subroutine, you could
use

Public Sub MyAuditTrail(frm As Form)
....
For Each C IN frm.Controls
....

In the calling routine, use

Call MyAuditTrail(Me)

or, if it's a function you're calling, you can simply use

=MyAuditTrail(Form)

in the Event property (rather than [Event Procedure].

 

[gmazza via AccessMonster.com]

Sorry, duh? I missed that one.
The problem is that code will not work for when I make a change to my main
form. When I make a change to my main form, it calls the same Audit Trail
routine and then the Screen.ActiveForm! etc won't work as it will be the name
of my subform.
During any changes to whatever form I am on, I grab the form name and pass it
to the Audit Trail routine. From there I want a generic "For Each C In"
statement that works for the main form and subform.
I hope I am making sense.
Thanks for your help Stuart.

Thanks for your reply.
In my Audit Trail function I added this:

[quoted text clipped - 22 lines]
If you look carefully, you'll see I posted SubformControlName. That refers
to the subform control on your main form which houses (hosts) the subform.
Then you refer to the .Form property of that to get the hosted subform
object.

 

[gmazza via AccessMonster.com]

Thanks John, but how will this work for a main form and a subform.
I already pass the form name to my audit trail routine. I pass it in as
FormName.
My main form name: Binder
My sub form name: BinderSub

If my statement is
For Each C In Forms(FormName).Controls
then that works for the main form, but it doesn't work for the subform.
Is there anyway to get it working for both a main form and sub form without
explicitly putting the names of the forms in?
Thanks!

Hey there,
I need to reference my subform in a module I have that is creating an audit

[quoted text clipped - 11 lines]

trail.
Thanks in advance!

A Subform is not in the Forms collection; you must reference it via the
mainform and the name of the Subform control within that mainform. The Name
property of the form you're using as a subform is irrelevant!

I would suggest passing the *form object* itself: in the subroutine, you could
use

Public Sub MyAuditTrail(frm As Form)
...
For Each C IN frm.Controls
...

In the calling routine, use

Call MyAuditTrail(Me)

or, if it's a function you're calling, you can simply use

=MyAuditTrail(Form)

in the Event property (rather than [Event Procedure].

 

[John W. Vinson]

Thanks John, but how will this work for a main form and a subform.
I already pass the form name to my audit trail routine. I pass it in as
FormName.
My main form name: Binder
My sub form name: BinderSub

If my statement is
For Each C In Forms(FormName).Controls
then that works for the main form, but it doesn't work for the subform.
Is there anyway to get it working for both a main form and sub form without
explicitly putting the names of the forms in?
Thanks!

The fact that you're putting the name of the form in *IS* the source of your
problem.

A Subform is *not in* the Forms() collection. It just doesn't exist, as far as
Access is concerned! Passing the name of the form *will not work*.

What I'm suggesting is that instead of what you're doing - passing the name of
the Form and then using the Forms(FormName) syntax to locate the actual Form
object - is to instead pass the *form object itself* as an argument.

Try it. It works.

 

[gmazza via AccessMonster.com]

I tried it, and it works!!
Thanks for your help John, much appreciated!

Thanks John, but how will this work for a main form and a subform.
I already pass the form name to my audit trail routine. I pass it in as

[quoted text clipped - 8 lines]

explicitly putting the names of the forms in?
Thanks!

The fact that you're putting the name of the form in *IS* the source of your
problem.

A Subform is *not in* the Forms() collection. It just doesn't exist, as far as
Access is concerned! Passing the name of the form *will not work*.

What I'm suggesting is that instead of what you're doing - passing the name of
the Form and then using the Forms(FormName) syntax to locate the actual Form
object - is to instead pass the *form object itself* as an argument.

Try it. It works.