Referencing fields from a form's underlying table

[April Marano]

I have a form that is bound to an underlying table. Some of the tables
fields are bound to controls on the form but some aren't. How do I
reference fields in the underlying table programmatically that AREN'T bound
to a control?

Specifically, I wish to push data acquired from the form into such fields
without binding them to controls such as TextBoxes or ComboBoxes.

Any insight here? Thanks in advance!

April

 

[Allen Browne]

In the module of a form, you can just refer directly to the Fields, even if
they are not represented by controls on the form.

For example, this works if LastChanged is a Date/Time field, even if there
is no text box:
Me.LastChanged = Now()

It's a bit confusing. The form has both a Controls collection, and a Fields
collection, and can recognise members from either. That's why it gets
confused if you bind a control to an expression, but the control has the
same Name as a Field.

 

[rkc]

I have a form that is bound to an underlying table. Some of the tables
fields are bound to controls on the form but some aren't. How do I
reference fields in the underlying table programmatically that AREN'T bound
to a control?

Specifically, I wish to push data acquired from the form into such fields
without binding them to controls such as TextBoxes or ComboBoxes.

Any insight here? Thanks in advance!

You can refer to any field in a forms recordsource using the dot operator.

Me.Fieldname.Value

You will notice when you refer to a field that isn't bound to a control of
the same name that Value is the only property that shows up in intellisense.

So the following assigns the value of the control to the value of the field.

Me.Fieldname.Value = Me!ControlName.Value

 

[April Marano]

OK, but does it matter if the field and the control have the same name? How
would Access know which was which?

April

 

[April Marano]

You HAVE to use the value property when addressing a field?

April

 

[rkc]

You HAVE to use the value property when addressing a field?

April

No. You don't have to.

What problem are you trying to solve, anyway?

 

[rkc]

OK, but does it matter if the field and the control have the same name? How
would Access know which was which?

April

You can't give an unbound control the same name as a field in the form's
recordsource so it's not a problem.

 

[rkc]

You can't give an unbound control the same name as a field in the form's
recordsource so it's not a problem.

Oops! Scratch that, you can.

 

[Van T. Dinh]

Most of the times, it doesn't matter since if you assign the value to the
bound Control, the binding Field will get the same value and vice versa.
The only case to watch out is as Allen mentioned.

In practice, I *always* use Control names different from Field names by
adding the prefixes for the types of Controls to the binding Fields, e.g.
"txt" for TextBox, "cbo" for ComboBox, etc...

 

[Larry Linson]

Yes, it does matter. The Wizard may generate Controls with the same name as
the Field to which they are bound, but it is easy, in Design View, to open
the Control's Properties, click the Other tab and prefix the Field name with
"txt" for a Text Box, "cbo" for a Combo Box, "lst" for a List Box... etc.
Then you no longer have the problem.

Larry Linson
Microsoft Access MVP

 

[Allen Browne]

Although I hate these ambiguities in Access, this gets *so* confusing that I
personally believe you are better off to just use the same name for the text
box and the bound field, and let Access sort it out.

If you have a text box named txtSurname, bound to a field named Surname,
then:
Me.txtSurname
is an object of type Textbox, whereas:
Me.Surname
is an object of type AccessField. There is very little documentation about
this type, and it has no properties except Value.

The object:
Me.Dynaset.Fields("Surname")
is different again. That reference tells you what type of data is expected,
if the field is required, etc. None of that information is available from
the AccessField object.

The AccessField therefore does not give you the field of the form's
RecordSource, and does not give you the control on the form, but is in some
kind of nether-world between the two. I suspect its existence is a
programming fudge, especially designed so you can get away with Me.Surname
when there is no control of that name on the form.

This fudge:
- Occassionally and unpredictably fails, preventing Me.Surname from
compiling, while Me!Surname (which is not checked by the compiler) still
works.

- Does not work on reports in the same way it does in forms, so you may have
to place a control on the report before you can refer to the field. (Again
this behavior is inconsistent.)

- Is probably the cause of intermittent serious crashes in Access 2002 and
2003, which you can work around by adding a text box on your subform for the
foreign key field(s) nominated in LinkChildFields. (That makes sense if the
Textbox reference is stable, while the AccessField reference fails
intermittently.)


If you do name your controls differently than their ControlSource, you also
open up the possibility of assigning a value to the AccessField instead of
the Textbox. The display is then inconsistent, and may not be updated
correctly until the record is saved.

So, IME, leaving the control names the same as their ControlSource is
simpler, quicker to develop, easier to maintain, and more stable.

 

[Gary Walter]

Thank you for this "fudge" perspective Allen!

While reading it, I also remembered point Tom
once made about

Me.Recordset.Fields("Surname")

and how in the Current Event, after navigating
to the next record,

Me!Surname <> Me.Recordset.Fields("Surname")

just one more *so*....