need to get form property

[tpoettker via AccessMonster.com]

hi,

this is probably a very easy solution but I can't figure it out. I have Form
A (frm_ARCall), subForm B (sfrm_ARCallInv) and Form C. From Form C, I want
to change the Record Source property of subform B. I have tried many things
but they have all failed. The latest is:

Forms![frm_ARCall]![sfrm_ARCallInv]![Form].[RecordSource] =
"fqry_ttARCallInv"

NOTE: This line of code is on Form C which is a subform on another form
(Form D).

Please help

 

[PieterLinden via AccessMonster.com]

hi,

this is probably a very easy solution but I can't figure it out. I have Form
A (frm_ARCall), subForm B (sfrm_ARCallInv) and Form C. From Form C, I want
to change the Record Source property of subform B. I have tried many things
but they have all failed. The latest is:

Forms![frm_ARCall]![sfrm_ARCallInv]![Form].[RecordSource] =
"fqry_ttARCallInv"

I think it should be ...
Forms![frm_ARCall]![sfrm_ARCallInv].[Form].[RecordSource]

 

[tpoettker via AccessMonster.com]

Had tried that as well and I received an error message that says "object
required". The error I received for the one I posted is "Access can't find
the field 'Form' referred in my expression"

hi,

[quoted text clipped - 5 lines]

Forms![frm_ARCall]![sfrm_ARCallInv]![Form].[RecordSource] =
"fqry_ttARCallInv"

I think it should be ...
Forms![frm_ARCall]![sfrm_ARCallInv].[Form].[RecordSource]

 

[BruceM via AccessMonster.com]

To expand a little on the previous reply, the bang (!) denotes that what
follows is a member of a collection, while the dot shows a property.
frm_ARCall is a member of the database's Forms collection. sfrm_ARCallInv is
in turn a member of the controls collection of frm_ARCall. Form is a
property of the subform control sfrm_ARCallInv.

It gets a little complex in that a control is both a property of the form and
a member of the form's controls collection. Same for fields. That is why
you can use either of these syntaxes in VBA:

Me!ControlName
Me.ControlName

hi,

this is probably a very easy solution but I can't figure it out. I have Form
A (frm_ARCall), subForm B (sfrm_ARCallInv) and Form C. From Form C, I want
to change the Record Source property of subform B. I have tried many things
but they have all failed. The latest is:

Forms![frm_ARCall]![sfrm_ARCallInv]![Form].[RecordSource] =
"fqry_ttARCallInv"

NOTE: This line of code is on Form C which is a subform on another form
(Form D).

Please help

 

[tpoettker via AccessMonster.com]

thank you for the extended detail. I tried a little more and got it to work
with this:

Forms!frm_ARCall.sfrm_ARCallInv.Form.RecordSource =
"fqry_ttARCallInv"

To expand a little on the previous reply, the bang (!) denotes that what
follows is a member of a collection, while the dot shows a property.
frm_ARCall is a member of the database's Forms collection. sfrm_ARCallInv is
in turn a member of the controls collection of frm_ARCall. Form is a
property of the subform control sfrm_ARCallInv.

It gets a little complex in that a control is both a property of the form and
a member of the form's controls collection. Same for fields. That is why
you can use either of these syntaxes in VBA:

Me!ControlName
Me.ControlName

hi,

[quoted text clipped - 10 lines]

Please help

 

[BruceM via AccessMonster.com]

Regarding another part of the thread, brackets are for objects such as tables,
and for fields and controls (maybe I have left something out). Properties do
not get brackets, which if they are used causes Access to interpret them as
controls or fields. The brackets are necessary only if the name has
something other than letters, numbers, or underscores, but I often use them
to make the code easier to read (for me). Either of these should work:

Forms!frm_ARCall.sfrm_ARCallInv.Form.RecordSource
Forms![frm_ARCall].[sfrm_ARCallInv].Form.RecordSource

Also these (bang rather than dot in one location):

Forms!frm_ARCall!sfrm_ARCallInv.Form.RecordSource
Forms![frm_ARCall]![sfrm_ARCallInv].Form.RecordSource

thank you for the extended detail. I tried a little more and got it to work
with this:

Forms!frm_ARCall.sfrm_ARCallInv.Form.RecordSource =
"fqry_ttARCallInv"

To expand a little on the previous reply, the bang (!) denotes that what
follows is a member of a collection, while the dot shows a property.

[quoted text clipped - 14 lines]

 

[Ron]

I think this is something that has hung me and my noobie ways up many times.
I've been told, and have observed, that a *form field* is to be referenced
as a form property. Therefore [myForm}.{myField] will generate an error in
VBA. BUT: the expression builder (invoked from a control property in design
view) makes no distinction between [myTable].[myField] and
[myForm].[myField] and will put those brackets in there. So if you copy and
paste a valid expression from the expression builder into the immediate
window - bam. "Object Required.". (Not at the Access computer now, so vow
to self: confirm this once and for all, will ya?)

As a noob noob, I thought that expression builder was way cool. As a
slightly aged noob, ....not so sure.

Valuable thread. Thanks!

 

[Ron]

Sorry, meant all brackets, no braces.

 

[BruceM via AccessMonster.com]

I've never been a fan of the Expression Builder, but maybe that's because
I've learned how to do what it does, and it's faster not to use it.

I'm not sure I follow how you would use [MyForm].[MyField] in VBA. I
wouldn't expect anything in the immediate window. For one thing, there is no
context. It is as if you gave Access a street name, but it doesn't know
which of a hundred numbers you mean. If you pause code execution in a form,
you should get the value of MyField in the immediate window with any of the
following, plus a few other variations (brackets optional if there are no
spaces or special characters other than underscore in the name):
Forms![MyForm]![MyField]
or the VBA shorthand:
Me.MyField
or even:
MyField

With an expression in a text box or query you could have the first or the
third option, but the Me. prefix is understood only by VBA. In those cases
the expression applies to the current record.

The above work in VBA when code execution is paused because Access
"understands" which form you are in (and therefore which recordset you are
using), and which record you are on. [MyForm].[MyField] and [MyForm]!
[MyField] do not work in any circumstance I can find. Apparently it is
either too much or too little information.

I prefer Me.MyField when possible if for no other reason than that the VBA
intellisense takes over (as soon as you type Me. you are presented with a
list of selections). Also, I find it easier to read than MyField by itself,
which just to look at it could be a variable or a function.

Here is a discussion about the use of the bang and the dot:
http://my.advisor.com/doc/05352

I think this is something that has hung me and my noobie ways up many times.
I've been told, and have observed, that a *form field* is to be referenced
as a form property. Therefore [myForm}.{myField] will generate an error in
VBA. BUT: the expression builder (invoked from a control property in design
view) makes no distinction between [myTable].[myField] and
[myForm].[myField] and will put those brackets in there. So if you copy and
paste a valid expression from the expression builder into the immediate
window - bam. "Object Required.". (Not at the Access computer now, so vow
to self: confirm this once and for all, will ya?)

As a noob noob, I thought that expression builder was way cool. As a
slightly aged noob, ....not so sure.

Valuable thread. Thanks!

................brackets are for objects such as tables,
and for fields and controls (maybe I have left something out). Properties

[quoted text clipped - 5 lines]

them
to make the code easier to read (for me).

 

[Ron]

I'm not sure I follow how you would use [MyForm].[MyField] in VBA. I
wouldn't expect anything in the immediate window. For one thing, there is
no
context.

I should have said that I'd put the form in form view after copying an
expression from within design view. Sorry.

................ If you pause code execution in a form,
you should get the value of MyField in the immediate window with any of
the
following, plus a few other variations (brackets optional if there are no
spaces or special characters other than underscore in the name):
Forms![MyForm]![MyField]
or the VBA shorthand:
Me.MyField
or even:
MyField

............ [MyForm].[MyField] and [MyForm]!
[MyField] do not work in any circumstance I can find.

Yes. Confirmed, at least after a few minutes testing.
Forms.myformname.myField works, but if you put brackets around the
collection name, then the bang operator is needed.

I prefer Me.MyField when possible if for no other reason than that the VBA
intellisense takes over (as soon as you type Me. you are presented with a
list of selections).

Good to know! I'd been wondering why intellisense wasn't popping up more.
I've shied away from Me, since I don't fully understand the contextual
requiements - eg. does Me in a control event refer to the control itself or
the form containing the control? (rhetorical; I need *really* to think in
terms of collections, objects, and properties) - but also wanted to document
this first app with full bore expressions.

Here is a discussion about the use of the bang and the dot:
http://my.advisor.com/doc/05352

Thank you! I was venting, and shouldn't have extrapolated the problems I'd
had with extended expressions to simple field syntax. (I do remain wary of
the expression builder, though.) Despite the fundamental level of threads
like this, they're valuable to me (and hopefully, to others). These groups
are great. Thanks again!

 

[BruceM via AccessMonster.com]

The Me prefix means that what follows is a property of the form in which the
code is located. If the bang is used instead of the dot, what follows is a
member of a collection, but as noted this presents you with pretty much the
same options as you have with properties. The Me prefix by itself does not
refer to anything specific about the form, but rather sets the table for what
follows. Me.TextBox1 refers to TextBox1 no matter where it is used in the
form's code module. It could be in a form-level event such as Current, or in
an event for an individual control. Similarly, you can have a message box
display a form-level property such as the name of the form by placing this
line of code in a Click event, After Update event, form's Current event, or
wherever you like:
MsgBox Me.Name

I'm not sure I follow how you would use [MyForm].[MyField] in VBA. I
wouldn't expect anything in the immediate window. For one thing, there is
no
context.

I should have said that I'd put the form in form view after copying an
expression from within design view. Sorry.

................ If you pause code execution in a form,
you should get the value of MyField in the immediate window with any of

[quoted text clipped - 9 lines]

............ [MyForm].[MyField] and [MyForm]!
[MyField] do not work in any circumstance I can find.

Yes. Confirmed, at least after a few minutes testing.
Forms.myformname.myField works, but if you put brackets around the
collection name, then the bang operator is needed.

I prefer Me.MyField when possible if for no other reason than that the VBA
intellisense takes over (as soon as you type Me. you are presented with a
list of selections).

Good to know! I'd been wondering why intellisense wasn't popping up more.
I've shied away from Me, since I don't fully understand the contextual
requiements - eg. does Me in a control event refer to the control itself or
the form containing the control? (rhetorical; I need *really* to think in
terms of collections, objects, and properties) - but also wanted to document
this first app with full bore expressions.

Here is a discussion about the use of the bang and the dot:
http://my.advisor.com/doc/05352

Thank you! I was venting, and shouldn't have extrapolated the problems I'd
had with extended expressions to simple field syntax. (I do remain wary of
the expression builder, though.) Despite the fundamental level of threads
like this, they're valuable to me (and hopefully, to others). These groups
are great. Thanks again!