For Each problem in Modue with variable

[Reppana]

I have Variable varFormName (as String). I get the name partly from input
form and
add txt "pic" in front, so varFormName is EG picFP601Parts
Also I have ctlFormBox (as Control)
I have in a module For Each loop and when I try to run this:
For Each ctlFormBox in Forms!varFormName
Program says that it cannot find form "varFormName"!
When I put the real name (Forms!picFP601Parts) everything is OK

I want to run this code from module when these pic- forms open

What have I done wrong? I have tried many kinds of bracketts and also tried
to
study this in internet, but haven't found the answer

 

[Jack Leach]

Forms!varFormName is trying to explicitly find a form called varFormName...
the Expression Service doesn't know you're referencing a variable. Try this
syntax instead:

For Each ctlFormBox In Forms(varFormname)

(the direct syntax for this would be Forms("picFP601Parts"), but you'll be
passing the variable as a string).

Another alternative would be to use the Eval function:

For Each ctl in Eval("Forms!" & varFormName)

The eval functions returns a value readable by the expression service, but
IMO, the variable syntax of Forms(varFormName) is a better way to go.


hth
--
Jack Leach
www.tristatemachine.com

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

 

[Jack Leach]

What have I done wrong? I have tried many kinds of bracketts and also tried
Just as a side note, here's my take on the difference between the two
methods...

The first method of working with collections (the one you were trying), is
relevant only to the Jet expression service. Jet sees something like
Forms!Formname.Controls!Controlname.Property, and each ! in the string tells
the Jet engine that you are referring to a collection on the left side, and
the item in the collection that's on the right side. (collection being the
Forms collection, on the right side of ! being the name of the form in the
collection).

We see this syntax used extensively in forms coding, calculated expressions
in controls, SQL queries, etc. The key here is that this all references of
these are "fed" by Access (and ultimately the Jet Engine). Access/Jet
Expression Service looks at this syntax and breaks it down appropriately.


In VBA, however, there is no expression services (except through a function
like Eval, which sends the string to the expression service and returns what
it thinks is correct).

With VBA, the correct syntax to refer to a collection is through a syntax
such as CollectionName(Index or String of the Name). Because what you are
trying to is in VBA, this is why you were getting the error trying to pass
this as a Jet Expression Service syntax.

Take a look at these two lines, which equate to the same thing, depending on
ES or VBA reading it:

Forms!frmMain.Controls!ctlThisControl.Value = blahblahblah
(expression service/access/jet, but not VBA)
or

Forms("frmMain").Controls("ctlThisControl").Value = blahblahblah
(vba syntax)


Hopefully this sheds a little light on the subject. This is why you see the
ES syntax used extensively in forms and reports, but only sparsely and
carefully in standalone VBA code.

--
Jack Leach
www.tristatemachine.com

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

 

[Reppana]

Great!!!

I thought there is something small wrong. Just forget the exclamation mark
and add two brackets, thats all!!


Thank You Very Very Very much for this solution.

Forms!varFormName is trying to explicitly find a form called varFormName...
the Expression Service doesn't know you're referencing a variable. Try this
syntax instead:

For Each ctlFormBox In Forms(varFormname)

(the direct syntax for this would be Forms("picFP601Parts"), but you'll be
passing the variable as a string).

Another alternative would be to use the Eval function:

For Each ctl in Eval("Forms!" & varFormName)

The eval functions returns a value readable by the expression service, but
IMO, the variable syntax of Forms(varFormName) is a better way to go.

hth

I have Variable varFormName (as String). I get the name partly from input
form and

[quoted text clipped - 10 lines]

to
study this in internet, but haven't found the answer

 

[Piet Linden]

I have Variable varFormName (as String). I get the name partly from input
form and  
add txt "pic" in front, so varFormName is EG picFP601Parts
Also I have ctlFormBox (as Control)
I have in a module For Each loop and when I try to run this:
For Each ctlFormBox in Forms!varFormName
Program says that it cannot find form "varFormName"!
When I put the real name (Forms!picFP601Parts) everything is OK

I want to run this code from module when these pic- forms open

What have I done wrong? I have tried many kinds of bracketts and also tried
to
study this in internet, but haven't found the answer

Also, the Forms collection refers only to Forms that are *open* when
you run it. If you want all the forms in your database, then you need
to use the AllForms collection and open them one at a time,,, then you
can use the forms collection and looop through the Controls collection
and manipulate it.

 

[Jack Leach]

Would anyone with a better understanding of this be able to advise on how
much of my previous post is correct? After reading back and pondering this
for a while, I'm second guessing myself.

I've used my suggestion for a while as my standard practice, but I've seen
cases where Forms!frmName is used in vba... though generally in form modules.
I can't seem to make a solid connection of how this works, and what the
actual rules to this game are.

(the reasons I gave seem to work for me, so hopefully I wasn't misleading at
all)

--
Jack Leach
www.tristatemachine.com

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

 

[Dirk Goldgar]

Would anyone with a better understanding of this be able to advise on how
much of my previous post is correct? After reading back and pondering
this
for a while, I'm second guessing myself.

I've used my suggestion for a while as my standard practice, but I've seen
cases where Forms!frmName is used in vba... though generally in form
modules.
I can't seem to make a solid connection of how this works, and what the
actual rules to this game are.

(the reasons I gave seem to work for me, so hopefully I wasn't misleading
at
all)

You're mistaken in thinking that the Forms!FormName reference syntax (using
the bang) is provided by the Jet Expression Service. It's built into VB.
The bang, in this case, is the "dictionary lookup operator", and means that
the name on the right is to be used as a string index to the collection that
is returned by evaluating the name on the left. You can use it in VB or VBA
wherever there is a collection with a string index.

In Access, the Forms collection is indexed by the form name, and the
Controls collection is indexed by the control name. That's why you so
frequently see references in the form "Forms!FormName" or
"Me!ControlName" -- the latter taking advantage of the fact that the default
collection of a form object is its Control collection.

In VBScript, as I recall, the bang operator doesn't work, and you have to
use (e.g.) 'rsMyRecordset.Fields("MyFieldName")', where in VB/VBA you can
write 'rsMyRecordset!MyFieldName'.

 

[Jack Leach]

Thank you Sir,

--
Jack Leach
www.tristatemachine.com

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