Setting a Form Variable using Variables

[DinosRose]

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
..
..
..
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
....

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[Dale_Fye via AccessMonster.com]

I think you can replace:

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

With something like:

Forms(frmName).subFormControlName.Form.SourceObject = SubformName

Note that subFormControlName is the name of the subform control on your form,
not the name of the form object that populates that control.

HTH
Dale

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[Jack Leach]

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName & "].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[Jack Leach]

I suppose I could explain it some, just for the record.

When setting the value of an object, a string value doesn't work (we don't
use "[Forms]!ThisForm....", just plain [Forms]!Thisform....). This is part
of access's Expression Service (I think)

The Eval() function takes a string expression and performs the access
expression service on the string passed to see what it can come up with (or
something like that anyway... not 100% positive on this, but I'm pretty sure
it's close enough for govt work).

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName & "].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[Douglas J. Steele]

I wouldn't have thought that would work, as the Forms collection (which is
what you're using) doesn't contain forms that are being used as subforms.

However, try

Set subfrm = Forms(frmName).Controls(subfrmName).Form

 

[vanderghast]

If the code runs under the form having the subform control,


Me.SubformControl.SourceObject = "Form1"


is enough to dynamically display the form Form1 inside the SubformControl.
So, it is just a matter to change the constant, here "Form1" with an
expression supplying the the name of the form to be used, in the appropriate
case:


Me.SubformControl.SourceObject
= DLookup("NameOfFormToBeUsed", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)



Vanderghast, Access MVP

I think you can replace:

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

With something like:

Forms(frmName).subFormControlName.Form.SourceObject = SubformName

Note that subFormControlName is the name of the subform control on your
form,
not the name of the form object that populates that control.

HTH
Dale

I'm attempting to refactor some of my existing code, and I've run into a
bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a
global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate
use
of the select case by assigning parentForm and subForm to variables and
using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[DinosRose]

I just tried that and it didn't work. It gave a run-time error 2769 (A
property of the Automation object requires or returns a data type that isn't
supported by Visual Basic.)

Any other suggestions?
--
-DinosRose - sometimes I think I've gotten in over my head...

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName & "].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[DinosRose]

I have it a try and it didn't work. It gave a run-time error 2769 (A
property of the Automation object requires or returns a data type that isn't
supported by Visual Basic.)

Any other suggestions out there?
--
-DinosRose - sometimes I think I've gotten in over my head...

I suppose I could explain it some, just for the record.

When setting the value of an object, a string value doesn't work (we don't
use "[Forms]!ThisForm....", just plain [Forms]!Thisform....). This is part
of access's Expression Service (I think)

The Eval() function takes a string expression and performs the access
expression service on the string passed to see what it can come up with (or
something like that anyway... not 100% positive on this, but I'm pretty sure
it's close enough for govt work).

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName & "].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[Jack Leach]

hmm... I'm almost positive I just did this a week or so ago. I'll have to
check, this should work...

It might be a little while before I can look into, I've got an extremely
busy weekend and next week coming up, but I'll get back as soon as I get a
chance to find where I did this and what the conditions were (if I remember
correctly, I used to to reference a form rather than subform (subforms don't
seem to be technically "open", even if the parent is), but calling the
subform through the parent should work).


I may have ended up using something like
<var> = Eval("Forms!frmName.Controls("sfrmcontrol").Form")


I'll try and find this later on.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I just tried that and it didn't work. It gave a run-time error 2769 (A
property of the Automation object requires or returns a data type that isn't
supported by Visual Basic.)

Any other suggestions?
--
-DinosRose - sometimes I think I've gotten in over my head...

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName & "].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[DinosRose]

No need for you to test...got the following to work:

Set subfrm = Forms("[" & frmName & "]").Controls("[" & subfrmName & "]").Form

Just needed someone to point me in the right direction.

Thanks much!
Paula
--
-DinosRose - sometimes I think I've gotten in over my head...

hmm... I'm almost positive I just did this a week or so ago. I'll have to
check, this should work...

It might be a little while before I can look into, I've got an extremely
busy weekend and next week coming up, but I'll get back as soon as I get a
chance to find where I did this and what the conditions were (if I remember
correctly, I used to to reference a form rather than subform (subforms don't
seem to be technically "open", even if the parent is), but calling the
subform through the parent should work).


I may have ended up using something like
<var> = Eval("Forms!frmName.Controls("sfrmcontrol").Form")


I'll try and find this later on.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I just tried that and it didn't work. It gave a run-time error 2769 (A
property of the Automation object requires or returns a data type that isn't
supported by Visual Basic.)

Any other suggestions?
--
-DinosRose - sometimes I think I've gotten in over my head...

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?


try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName & "].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

I'm attempting to refactor some of my existing code, and I've run into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is a global
variable which holds a value represting which "Audit Form" is currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the subForm
represented by the 7 different gintAuditForm values. I want to eliminate use
of the select case by assigning parentForm and subForm to variables and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue=" &
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?

 

[vanderghast]

Unless I am missing a point.

Assume, for a moment, it is a button, rather than a subform. And you want to
change its label, so you use:

buttonControl.Caption = "new text"

and NOT

Set buttonControl = .... some other button control ...







So, for the subform that logic is the same: you don't really want to change
the control (the subform) but what it holds, and what it holds is a form,
so, try:


subFormControl.SourceObject = "FormNameToBeUsed"


That is how you can make a "wizard" sequence of displayed 'subforms' (where
the main form hold the subform control, and the navigation buttons, as
example). Each 'step' of the wizard simply loads a new form, appropriately,
through the SourceObject property (which is a string value) of the subform
control (exactly like we could change the text of a button control )






Again, I may have missed something.



Vanderghast, Access MVP

No need for you to test...got the following to work:

Set subfrm = Forms("[" & frmName & "]").Controls("[" & subfrmName &
"]").Form

Just needed someone to point me in the right direction.

Thanks much!
Paula
--
-DinosRose - sometimes I think I've gotten in over my head...

hmm... I'm almost positive I just did this a week or so ago. I'll have
to
check, this should work...

It might be a little while before I can look into, I've got an extremely
busy weekend and next week coming up, but I'll get back as soon as I get
a
chance to find where I did this and what the conditions were (if I
remember
correctly, I used to to reference a form rather than subform (subforms
don't
seem to be technically "open", even if the parent is), but calling the
subform through the parent should work).


I may have ended up using something like
<var> = Eval("Forms!frmName.Controls("sfrmcontrol").Form")


I'll try and find this later on.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I just tried that and it didn't work. It gave a run-time error 2769 (A
property of the Automation object requires or returns a data type that
isn't
supported by Visual Basic.)

Any other suggestions?
--
-DinosRose - sometimes I think I've gotten in over my head...


:

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?


try Eval()

Set subfrm = Eval("[Forms]![" & frmName & "]![" & subfrmName &
"].[Form]")

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

I'm attempting to refactor some of my existing code, and I've run
into a bit
of a snag.

In my current code, I'm setting a variable for Form to set controls
on the
form I am opening to the value of controls on the
Forms!parentForm!subForm.form in use. In my code gintAuditForm is
a global
variable which holds a value represting which "Audit Form" is
currently in
use.

Dim subfrm As Form

Select Case gintAuditForm
Case 1
Set subfrm = [Forms]![Audit Form]![audit subform].[Form]
Case 2
Set subfrm = [Forms]![Audit Form Stage2]![audit subform
stage2].[Form]
.
.
.
End Select

Me.txtALFirsttNm = subfrm![txtFirstNm]
...

I've built a reference table that lists the parentForm and the
subForm
represented by the 7 different gintAuditForm values. I want to
eliminate use
of the select case by assigning parentForm and subForm to variables
and using
that to set the Form variable.

However, the following code doesn't work:

frmName = DLookup("parentForm", "zzzAuditFormRefTbl", "FormValue="
&
gintAuditForm)
subfrmName = DLookup("subForm", "zzzAuditFormRefTbl", "FormValue="
&
gintAuditForm)

Set subfrm = [Forms]![" & frmName & "]![" & subfrmName & "].[Form]

Any ideas?