No error generated when missing required field

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

We're using Access 2002 as a front end to a SQL Database.
We've created a form for adding data to a SQL table.

If we close a form without data in a required field, the
form closes and no data is appended to the table (but the
user doesn't know this). No error condition is created to
trap.

If we work directly from the access query that the form is
bound to, an ODBC error message (invalid use of null) is
generated when trying to add incomplete data.

Anyone know why the error message is not generated in the
form?
 
Jeff, this is a serious, data-loss bug that has been in Access from at least
version 2. It is not limited to SQL Server: the same data loss occurs with
the JET tables built into Access.

The close action *silently* *loses* the data is if there is *any* reason why
the record cannot be saved, e.g. duplicate index, required field missing,
related record required, ... As you say, the user gets no notification that
the save failed. In a program that saves data by default, the silent failure
constitutes a serious, data-loss bug. (Note that this applied to the Close
action/method in a macro or VBA code, and not to closing the form with the
built-in button.)

Once you know about the bug, it's easy enough to work around it: Always
explicitly save (or undo) before executing the Close:
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name

I have tried repeatedly and over many years to get Microsoft to fix this.
Please let MS know that you are in a long queue of people who have been hurt
by this bug. If it's any help, quote my anger also that they just go on
releasing new versions with the same behaviour.
 
Allen, does your code to set the form's dirty property to false cause any
edited data to revert to its old value? I was under the impression that the
property could be manually set to true or false at will without it affecting
any data that may or may not have been changed at that point. So if I edit
some data, then manually set the dirty property to false, does that undo my
changes?
John Loewen
 
Setting a bound form's Dirty property to False forces any edits that are in
progress to be *saved*.

This is the best way to save a record:
- "RunCommand acCmdSaveRecord" saves whatever form is active, not
necessarily the one you want.

- The "DoMenuItem ..." nonsense that the Wizard generates has the same
issue.

- "Me.Refresh" generates no error message if the save fails (which is
pointless: you don't code a save unless you need to know it worked.)

- "Me.Dirty = False" saves the record in the nominated form, and generates
an error if something is wrong (e.g. required field missing.

If you did want to undo the edits instead:
If Me.Dirty Then
Me.Undo
End If
 
I take it you mean the best way to save a record is the last one in your
list of four, since it generates an error message?
 
Yes: error messages are your friends.

An error message lets you know that what you asked for did not happen, e.g.
the form did not open, or the record did not save, or there were no records
to include in the report, or the export did not happen. There is no point
the code continuing blindly trying to find a record in the form that didn't
open, or apply a filter when you are stuck editing a record that can't be
saved, or ...

All your code will therefore contain error handling to gracefully recover
from the error, usually let the user know what happened, and take an exit
instead of driving madly on when the road is closed.

If you want an explanation of how to use error handling and even log the
errors ('coz users don't remember them), see:
http://allenbrowne.com/ser-23a.html

In the case of "Me.Dirty = False", the typical error numbers to trap are
3314, 2101, and 2115.

In the case of a report not opening, expect 2501.
 
I'm sorry, Allen, I should have been a little clearer. I know what error
messages are and why they're good. Its just that your previous response said
"This is the best way to save a record:" and then listed four methods. I was
just trying to confirm which of the four was your suggestion as *the* best
method, and I assumed it was the one which supplies an error message since
that is obviously preferable.

By the way, I've enjoyed browsing your web site and learning from your code.
I had developed an error logging function somewhat similar to yours (which
you referenced in your last response). But I found it only seemed to work on
a full-licensed Access machine. When running on a client's computer under
Access Run Time, it failed to log errors. I never pursued it after that to
try to solve it, just assumed run-time didn't allow that kind of interaction
with errors. I notice your function uses a recordset feature. Is that the
basic reason why your error logging function works where mine didn't under
ART? Or does yours also fail under ART (I assume not)?

Also, is it possible to place a call to the error logging/display function
from each form itself that will gracefully handle any unexpected error that
isn't trapped at all in specific procedures? For instance, if I have some
seemingly safe procedures that don't have error handling built-in, and an
error occurs there, can a generic catch-all error trap be set at the form
level to catch them anyway and log them with your function? (I know, its
better to just include the code in every procedure. But what if, just for
curiosity if nothing else?)
 
Okay. Yes, you got it: my preference is for Me.Dirty = false because of the
trappable error.

Error logging should work in runtime. When we first developed that routine,
we used Me.Module.Name to get the name of the module where the error
occurred. That reference fails in an MDE, so we ended up using a private
constant named conMod in every module. Each routine passes the private
constant to the error handler. When you cut a procedure from one module and
paste it into another, the conMod reference picks up the value of the
private constant in the new module, so there is no need to change the code.
Other than that change, yes, we use error logging in mde and runtime
versions (including a report which the user can send us on request if
needed.)

There is no simple way to generically trap all non-handled errors: each
routine needs its own error handler, especially in the runtime! There is no
excuse for writing a routine and omitting error-handling, with the possible
exception of a one-liner that calls a function that contains error handling
and has no arguments that could go wrong.

If you want to be able to be able to add your own customized error handling
into a procedure with a single mouse-click, get mztools from
www.mztools.com. A great set of utilities.
 
Thanks for your help and information. I fully agree with your statement
about including error handlers in virtually every procedure. I checked out
the web site for MZ-Tools, looks good. I'll download it and try it out
later.

You wouldn't happen to know of any utility that allows one to peek inside a
corrupted database file and see the data, would you? I really need to get
the data from a table named Activities in database file that is either an
Access database or one so similar that Access can open it.
 
No. If it's an Access database, you have tried the obvious options (repair,
import, drop indexes/relations, decompile, ... ), and you want to pay somone
to resurrect it, talk to Peter Miller at pksolutions.com. I think he's still
doing this stuff.
 
Back
Top