Database has amnesia???

[RoadKyng]

I opened the database I use on a daily basis and started getting errors in
the code pointing to undeclared variables. It seems associated with
statements that are pointed to controls on a form where the control name and
its underlying datasource are different. I had to change the code to match
the name of the control.
Other errors are generated where I have code writing a value to a table
associated with a certain form, but that particular field is not on the form.
I had to insert the field in the form and turn its visible value to no to get
the code to work.

Something has obviously changed but I cannot figure out what?

 

[RoadKyng]

Posted in wrong place. I just cross posted this in Programming.

apologies.

 

[Allen Browne]

There are indeed issues in some cases where the control name is different
than the name of the field it is bound to.

Typically this happens if you added the field to the form's source query
after creating the form. And it will certainly happen if you reassign the
RecordSource programmatically (e.g. in Form_Open.)

You can work around the issue by using the bang instead of the dot, e.g.:
Me!Field1
instead of:
Me.Field1
When you use the dot, Access verifies the reference at design time, whereas
the bang is not verified at compile time.

Alternatively, you can add a control for the field (hidden if you wish), and
use the control name instead of the field name.

Where you refer to a field in the form's RecordSource, you have an object of
type AccessField. Naturally enough this object has far fewer properties than
a control has. But I also suspect there are some bugs with the AccessField
type - possibly even crashing Access in some cases. IME, therefore, your app
is more stable if you do add the hidden controls. And if you do name your
controls differently than the fields they are bound to, you will probably
get a more stable app by using the control name rather than the field name
(so you are dealing with an object of type Textbox or whatever, rather than
an object of type AccessField.)

If you are talking about reports, there's a whole other range if issues
there. Access sometimes optimizes the reports and doesn't even fetch fields
that are not displayed. Therefore the attempt to refer to fields in the
report's RecordSource that are not on the report can fail completely. Again,
adding a (hidden) control bound to the field solves the issue, but for a
completely different reason.

HTH.

 

[RoadKyng]

Hi Allen,

The wierd thing is the database has been working fine for months with no
errors. It started the errors when the DB was opened yesterday. Today I tried
compiling the code and got a rash of errors as I have previously discussed.
Using the ctrl/space key method of coding, it does not see the field names of
the forms data source (table) it only has the names of the form controls.

I just don't understand why the DB woke up yesterday and decided the code
was wrong!

 

[Allen Browne]

Yep: found the same thing when compacted an old A97 app that worked for
years, so as to convert to a later version.

 

[Aaron Kempf]

MDB is crap

that is what happened

move to Access Data Projects

DAO and JET haven't been included with Office, MDAC or Windows for a decade