Access 2007 - table field references no longer valid on forms (?)

  • Thread starter Thread starter Allen Browne
  • Start date Start date
A

Allen Browne

When you add the fields to your form, Access creates controls with the same
name as the fields. The name:
Me.Field1
then refers to the control. This works in all versions of Access.

If you have a field in the form's RecordSource named Field1, but there is no
control with that name on your form, then:
Me.Field1
refers to an object of type AccessField (not an object of type TextBox or
whatever.) The AccessField reference is unreliable, i.e. the code may not
compile. In some circumstances, Access may even crash.

Workarounds:
a) Try:
Me!Field1
Access will compile, i.e. the reference is not checked at design time. So,
unless you altered the form's RecordSource programmatically so Field1 is no
longer in the RecordSource, the field will be present at runtime and the
reference should work.

b) Add a control named Field1 to your form (hidden if you wish.)

c) If you already have a control bound to Field1, use the name of the
control instead, e.g.:
Me.txtField1

In general, it seems that:
a) Me.Field1 works even if there is no control with that name, provided
Field1 was present when you first designed the form.

b) Me.Field1 doesn't work if you programmatically add Field1 to the SELECT
clause of the form's RecordSource while the form is running.

c) Me.Field1 may or may not work if you add Field1 to the form's query after
creating the form.

d) Case (c) can trigger a failure when you convert to a different version of
Access. (This goes back at least to Access 97, where code that worked
sometimes failed to compile when converted to Access 2000.)

An example of the crash referred to above is when the AccessField is used in
the LinkMasterFields/LinkChildFields of a subform control, again only when
the RecordSource is a query where fields were added after the original
design.

So, the best solution is to add the hidden control, so the AccessField
issues don't arise. And if you don't want to do that, use Me!Field1 instead
when referreing to objects of type AccessField.

HTH
 
Which line fails?

I would not have used the "Is =", i.e.:
Case "downlight"

Does using a ! instead of a dot solve the problem?

When does it fail? Access should not be able to determine whether
frm.txtLuminaireType is valid at compile time.

If it fails, in break mode, open the Immediate Window and ask Access what
its understanding is, e.g.:
? TypeName(frm!txtLuminaire)

In converting from Access 97 to 2000/2/3, it would sometime error and you
ended up with a label control instead of a text box. The form was unusable
until you fixed this. Can't say I've seen A2007 do that though.
 
I have code behind several forms written in earlier versions (2003 and
earlier).
The data source for the forms might include a table with fields: Field1,
Field2, Field3...

the code would have reference the field data for the current record like
this:
me.Field1...
me.Field2...
me.Field3...

but now in Office 2007, this does not seem to work anymore; does this make
sense to anyone? what is the appropriate work around?

thanks in advance,
mark
 
this is incredibly useful THANKS!
on, what I would assume is a related issue, these lines of code, which are
in a subroutine called by the main form, did not translate into 2007

Sub GenerateDescription(frm As Access.Form)
Select Case frm.txtLuminaireType.Value
Case Is = "downlight"

the line: frm.txtLuminaireType no longer works (I am evaluating
txtLuminaireType, and text box on the main form)
the same logic as you provided below seems to not work, but perhaps, I'm
trying in the wrong way (?)

any suggestions here?

again, thanks in advance,
mark
 
Back
Top