Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Losing data when you close a form (bug)

        1 votes: *****     2,679 views      No comments
by Allen Browne, 20 April 2005    (for ALL VERSIONS of Access)

Flaws in Microsoft Access

Provided by allenbrowne.com, January 2004. Updated January 2008.


Losing data when you close a form

When you enter data into a form, Access saves the record automatically. An error message notifies you if the record cannot be saved. Or does it?

Use the Close action or method, and the form fails to notify you if the record cannot be saved. The form closes, and your entry is silently lost.

To demonstrate this flaw, download AccessFlaws.zip, open the form LosingData, and follow the instructions.

(Note: if you close the form with the [X] at the right end of the form's title bar, you do receive a warning that the record cannot be saved. Typically the problem occurs when you place a Close command button on your form.)

Examples where the record would be lost

There are many reasons why a record could not be saved, such as:

  • a required field was left blank;
  • the record would create a duplicate in a unique index;
  • the form's Before Update event was cancelled;
  • a Validation Rule was not met.

Workaround

Once you know the flaw exists, the solution is obvious. Never use Close without explicitly saving the record first:

    If Me.Dirty Then
        Me.Dirty = False
    End If
    DoCmd.Close acForm, Me.Name

To force a record to be saved, you could use:

#1.  RunCommand acCmdSaveRecord
#2.  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
#3.  Me.Refresh
#4.  Me.Dirty = False

#1 fails if the form does not have focus.

#2 (as generated by the command button wizard in Access 95 to 2003) has the same problem, and also fails with pop-up forms in some versions.

#3 works, but older versions of Access do not generate a message if the save fails.

#4 specifies which form, and gives an error message if the save fails. The message may not be clear (e.g. "The property could not be set"), and the approach does not work in Access 1 or 2, but it is the most reliable approach unless you are working with the ancient versions.

This flaw exists in all versions of Access. It is very unlikely the issue will ever be fixed. Develop the habit of explicitly saving whenever you do anything that requires the record to be saved, e.g. applying or removing a Filter or OrderBy property, changing a RecordSource, or moving to another record.

Access 2007

In Access 2007, you can also avoid the problem if you use the Close action in a macro. You will also need to implement macro error handling (new in Access 2007.) The problem still occurs in Access 2007 if you use the Close method in VBA code. We recommend you explicitly save anyway.

Further references

The Microsoft knowledgebase demonstrates this bug has been known since version 2:


Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Losing data when you close a form (bug)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).