Repost: References to a form's recordsource fields

  • Thread starter Thread starter Fred Boer
  • Start date Start date
F

Fred Boer

Hello:

I posted this question a couple of days ago under the heading "Subform
reference returns value from query" (not a great subject heading, but I
couldn't think of anything better...), and got no answer, and I am still
curious....

Suppose I have a form which has a table as its recordsource. I know that a
bound control will contain the value stored in the field to which it is
bound. But I've been fiddling around and it looks like I can make a direct
reference to the field itself to access the value in the field.

For example:

Table: tblStudent; Field: StudentLastName

I can place an unbound textbox ("txtStudentLastName") on the form, and
form's current event I can put the following code:

Me.txtStudentInfo= Forms!tblStudent.StudentLastname

Now, suppose I have a subform, based on a different recordset, without any
controls at all. The recordset has a field called "Age". I could put the
following code in the current event of the main form:

Me.txtStudentInfo=Forms!frmMain!frmSubform.form!Age

So, to me, it looks like you can pull the data directly from the
recordsource of the subform. I've always thought you needed to have a bound
control and then pull the data from that control. Is this correct? Have I
just been limited in my thinking? Does Access look for a control and not
finding one, then attempt to match a field in the underlying recordsource?

Also, I *think* that a form would have a collection of controls, right? So
would the fields of a recordsource somehow be some kind of a collection of a
form based on that recordsource?

Apologies if this is not clear...

Thanks!
Fred Boer
 
Sorry, typo here:
I can place an unbound textbox ("txtStudentLastName") on the form, and
Click to expand...

Should be:

I can place an unbound textbox ("txtStudentInfo") on the form, and

Fred
 
Fred Boer said:
Hello:

I posted this question a couple of days ago under the heading
"Subform reference returns value from query" (not a great subject
heading, but I couldn't think of anything better...), and got no
answer, and I am still curious....

Suppose I have a form which has a table as its recordsource. I know
that a bound control will contain the value stored in the field to
which it is bound. But I've been fiddling around and it looks like I
can make a direct reference to the field itself to access the value
in the field.

For example:

Table: tblStudent; Field: StudentLastName

I can place an unbound textbox ("txtStudentLastName") on the form, and
form's current event I can put the following code:

Me.txtStudentInfo= Forms!tblStudent.StudentLastname

Now, suppose I have a subform, based on a different recordset,
without any controls at all. The recordset has a field called "Age".
I could put the following code in the current event of the main form:

Me.txtStudentInfo=Forms!frmMain!frmSubform.form!Age

So, to me, it looks like you can pull the data directly from the
recordsource of the subform. I've always thought you needed to have a
bound control and then pull the data from that control. Is this
correct? Have I just been limited in my thinking? Does Access look
for a control and not finding one, then attempt to match a field in
the underlying recordsource?

Also, I *think* that a form would have a collection of controls,
right? So would the fields of a recordsource somehow be some kind of
a collection of a form based on that recordsource?

Apologies if this is not clear...

Thanks!
Fred Boer
Click to expand...

Yes, Fred. Access does its best to resolve control/field references for
you. Wherever possible, Access makes *both* the controls on the form
and the fields in the form's recordset available as properties of the
form. That means that, for the most part, you can write

Me.FieldNameFromRecordSource

or

Me.ControlNameOnForm

and get the result you would hope for.

As for the bang notation -- e.g., Me!Something -- working with fields
from the form's recordset ... Technically, that notation should be used
only to refer to members of a collection, in this case the form's
Controls collection. However, if th name you refer to isn't found in
the Controls collection, Access helpfully tries to find it in the Fields
collection of the form's recordset. I prefer to use the dot notation
(e.g., Me.Something) when I'm going to refer to a field that isn't bound
to a control, but both will usually work.

In queries, it may be that you can't use this syntax for fields not
bound to controls -- I'm not completely sure. You can test that, if you
like.

The main situation in which you can't use the dot notation for a control
or field on a form is when the name of the control or field is the same
as the name of a predefined property or method of the form. Then Access
must interpret the reference to mean the existing property or method.
 
Thanks, Dirk:

Isn't Access so kind... trying so hard to resolve things for me! Thanks for
clearing that up!

Fred

P.S. About the notation...thanks for the explaination. To tell the truth, I
usually just use a dot, but there happened to be a bang in that code... more
 
Be careful though with more complex query/SQL statements.
If, for example, you are using information from several
tables that may have some identically named fields (like
foreign keys possibly) you may get Access saying
that "FieldX" may refer to more than one table in the
underlying query. I've seen this before.
 
Thanks, Jeff! Actually, I don't think I would routinely reference fields
rather than controls. In fact, the only reason this came up was because I
had made a mistake in my code (gasp!). I used what I thought was the name of
a control, when, in fact, the control had a different name and I just
happened to use a name that matched a field in the recordsource. Trying to
figure out *why* it still seemed to work brought me to this question...

When you make lots of mistakes you always have lots of opportunities to
learn! <g>

Fred
 
Back
Top