Need syntax help for subform

[teqslamer]

I have a search form with 2 subforms, one is the search criteria the
other is the results of the search. This all works really well that is
not my problem. the issue is that based upon the data being searched I
need to change the lables, record sources and control sources of the
objects in these two subforms. I am using VBA and have it working by
hard coding the changes to each object that needs changing I am looking
for the syntax to address the objects in the subforms via variables.
This way I can use a table to drive the formation of the subforms
lables, textboxes, combo boxes and their related data.

I can pass the form name to the function then using:

Dim frm As Form

Set frm = Forms(FormName)

How do I address the subforms in this manner?
Note: this code is in a module not within the form.

 

[Allen Browne]

Instead of passing the name of the form, pass a reference to the form.

The function declaration will be:
Function DoSomething(frm As Form)
and call it like this:
Call DoSomething(Me)
or:
Call DoSomething(Me![Sub1].Form)

Then in the function, you can use frm just the same as you would use Me in
the form's own module.

BTW, I shudder at the idea of reassigning the ControlSource of the controls
after the form is open. Make sure the controls don't have the same Name as
their Control Source. Be careful about controls that change data type. And
be very careful about controls bound to fields that no longer exist: that
can crash Access (closed down by Windows.)

 

[teqslamer]

Allen, Thanks for your response!

I follow the way to call the function with the "Call DoSomething(Me)"
and i can get that to work fine but the two subforms are what I would
like to call with a variable so I can reuse the same function to change
the controls labels and sources.

As to your concern about changing the sources of the form and its combo
boxes this works fine they are not named the same and I am not changing
data type just changing the labels and the Control Source of text boxes
and combo boxes, in relation to the forms and subforms Record Source,
again this works without issue.

 

[Allen Browne]

Is there are problem with calling the form in the subform control named Sub1
like this:
Call DoSomething(Me![Sub1].Form)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thanks for your response!

I follow the way to call the function with the "Call DoSomething(Me)"
and i can get that to work fine but the two subforms are what I would
like to call with a variable so I can reuse the same function to change
the controls labels and sources.

As to your concern about changing the sources of the form and its combo
boxes this works fine they are not named the same and I am not changing
data type just changing the labels and the Control Source of text boxes
and combo boxes, in relation to the forms and subforms Record Source,
again this works without issue.

Instead of passing the name of the form, pass a reference to the form.

The function declaration will be:
Function DoSomething(frm As Form)
and call it like this:
Call DoSomething(Me)
or:
Call DoSomething(Me![Sub1].Form)

Then in the function, you can use frm just the same as you would use Me
in
the form's own module.

BTW, I shudder at the idea of reassigning the ControlSource of the
controls
after the form is open. Make sure the controls don't have the same Name
as
their Control Source. Be careful about controls that change data type.
And
be very careful about controls bound to fields that no longer exist: that
can crash Access (closed down by Windows.)

 

[teqslamer]

Is there are problem with calling the form in the subform control named Sub1
like this:
Call DoSomething(Me![Sub1].Form)

No using that format it is not a problem calling the subform but if I
tried to use a variable:

SubFormName = "Me![Sub1].Form"

Call DoSomething(SubFormName)

does not want to work.

To add a little clairity on what I am trying to accomplish in a table I
have the form name the subform names and the control names and their
settings ie visable, enabled, Lable text, control source and so forth.
Using a recordset of that table I want to control the forms, subforms
and the controls.

 

[Allen Browne]

Okay, so you want to store the control name in the table, and get a
reference to it in your code. Presumably this needs to cope with
sub-subforms and so on to the theoretical limit of 7 nestings.

If you are just trying to set the properties of the subform control, you
could call it with:
Call DoSomething (Me("Sub1"))
where Sub1 can be a string in the table.

If you are trying to execute the code again for each subform found on your
form, and then for any sub-subforms found on those, and so on, you need to
write your function recursively. For an example of how to loop through all
the form's controls, setting properties, and identifying subforms to call
recursively, see:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

In your case, you will need to read the SourceObject of the subform control,
so you can match it to the field in your table where you store the form
name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there are problem with calling the form in the subform control named
Sub1
like this:
Call DoSomething(Me![Sub1].Form)

No using that format it is not a problem calling the subform but if I
tried to use a variable:

SubFormName = "Me![Sub1].Form"

Call DoSomething(SubFormName)

does not want to work.

To add a little clairity on what I am trying to accomplish in a table I
have the form name the subform names and the control names and their
settings ie visable, enabled, Lable text, control source and so forth.
Using a recordset of that table I want to control the forms, subforms
and the controls.