We help IT Professionals succeed at work.

Updating data in an Access form updates incorrect record

Medium Priority
Last Modified: 2011-10-19
We are experiencing an odd phenomenon.  We have an access database.  The data (backend) resides on the server.  All users for the database have a front-end that is linked to the tables on their local hard drives.  I've created forms for them to use for data entry and navigation to the different parts.  Lately we've been experiencing a situation where someone is adding data to an existing record.  (This data is displayed on a form.)  When the data is changed (e.g., a date changed, a number changed, text field changed) and they move to another field on the form, the record switches to a different record entirely!  It doesn't appear to matter if they use the tab key, enter key or mouse to move to a new field.  I've compacted the database.

If I go directly to the tables, I can modify data without a problem.  I do not have users who are familiar enough with access that they can use this database with the forms.  We've had this database for several years and it has only recently (last few months) started this strange behavior.  I am at a total loss as to why.
Watch Question

This from Help:

AllowAdditions Property

You can use the AllowAdditions property to specify whether a user can add a record when using a form.


The AllowAdditions property uses the following settings.

Setting Visual Basic Description
Yes True (1) (Default) The user can add new records.
No False (0) The user can't add new records.

You can set the AllowAdditions property by using the form's property sheet, a macro, or Visual Basic.


Set the AllowAdditions property to No to allow users to view or edit existing records but not add new records.

If you want to prevent changes to existing records (make a form read-only), set the AllowAdditions, AllowDeletions, and AllowEdits properties to No. You can also make records read-only by setting the RecordsetType property to Snapshot.

If you want to open a form for data entry only, set the form's DataEntry property to Yes.

When the AllowAdditions property is set to No, the Data Entry command on the Records menu isn't available.

Note   When the Data Mode argument of the OpenForm action is used, Microsoft Access will override a number of form property settings. If the Data Mode argument of the OpenForm action is set to Edit, Microsoft Access will open the form with the following property settings:

AllowEdits  Yes

AllowDeletions  Yes

AllowAdditions  Yes

DataEntry  No
To prevent the OpenForm action from overriding any of these existing property settings, omit the Data Mode argument setting so that Microsoft Access will use the property settings defined by the form.

Per change, is AllowEdits set to no?
It would appear that there is some sort of data validation happening to unbound controls on your form and that a form requery or refresh is occuring before the control values are actually being written to the table fields.

If I am correct it shouldn't be difficult to fix but I would have to see all the code for the form.

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

When they are switched to a different record, is this a NEW record or an existing record?

Donald MaloneyConsultant

IS the database front end a MDE file type or MDB?  If MDB  someone may have changed the form and added code to advance the record. e,g   movenext.
Do you have a backup of the FE  before this problem started?
It may also be that the code has been corrupted or the data is out of synch.
Do you do a compact and restore to clean up the extras that access adds everytime the db is opened?
I have not seen a record advance as the fields are tabbed through so i am thinking that somehow the application sees the enter/tab/mouse move  as a record advance almost like a moveNext  when any field looses or gets the focus.  When the form opens can youi put the mouse on the forst field and it stays?  what happend if you put the mouse on the second field and move back?  same thing?
If thye application is an mde  then I would look for a backup or recompile the code.
Is this problem with all users?  do all users have there own FE on their own PC?
This is a strange one.


Here's a little more information: I've attached two screen shots.  One is of the menu (form) used to navigate and the other is a shot of the form that experiences the problems.

The FE is a mdb file as is the BE.  This form is opened from a hyperlink and the form properties for AllowEdits, AllowDeletions and AllowAdditions is yes; DataEntry is no.  The form is based on a query which selects the workshops display based on a date range and location.

Each user has their own FE on their own PC.

I run an autoexec macro upon opening that hides toolbars and opens the menu form.  Users cannot get to tables, queries or design unless they know how to bypass the autoexec.  (My users are not that skilled).  In addition, I keep a copy of the FE on the network and when each user logs in the login script updates their local copy to match the most recent version on the server.

I do have unbound controls on the form, but no validation occurring.

Hope this additional info helps.  I've been creating apps in Access for years and this behavior absolutely has me baffled.

(My users are not that skilled) - Never under-estimate a hacker.  Check out the hires just before the problem began.
Hmm,  thinking on screen.
Is it possible that the DB has duplicate keys and the form is being refreshed after each entry?
The refresh may jump to a matching record.
I would Make the FE a mde and in code hide all the options and prevent the shift key on open.

The code below in a macro   autoexec         runcode   modSetStartupMDBorMDE()
(I found this on EE.)
WIll look at your Db and allow (with mdb) or shut off  {with mde} all options.
The first time you run with a mde all options are on since the code options are set at start.
the second run with th emde, the one you would save in your master, is the one that your users would download.  Just make sure  the old ones on their PCs are removed and that someone didnt save  a mdb to play with later.

Option Compare Database
Function modSetStartupMDBorMDE()
If right([CurrentDb].Name, 3) = "mde" Then 
Call SetStartupProperties
Call SetAdminProperties
End If
End Function
'This sets security, so users can't fool around with stuff--very useful....
Function SetStartupProperties()
ChangeProperty "StartupShowDBWindow", dbBoolean, False
ChangeProperty "StartupShowStatusBar", dbBoolean, False
ChangeProperty "AllowBuiltinToolbars", dbBoolean, False
ChangeProperty "AllowFullMenus", dbBoolean, False
ChangeProperty "AllowShortcutMenus", dbBoolean, False
ChangeProperty "AllowBreakIntoCode", dbBoolean, False
ChangeProperty "AllowSpecialKeys", dbBoolean, False
ChangeProperty "AllowBypassKey", dbBoolean, False
End Function
'This turns all the protection off
Function SetAdminProperties()
ChangeProperty "StartupShowDBWindow", dbBoolean, True
ChangeProperty "StartupShowStatusBar", dbBoolean, True
ChangeProperty "AllowBuiltinToolbars", dbBoolean, True
ChangeProperty "AllowFullMenus", dbBoolean, True
ChangeProperty "AllowShortcutMenus", dbBoolean, True
ChangeProperty "AllowBreakIntoCode", dbBoolean, True
ChangeProperty "AllowSpecialKeys", dbBoolean, True
ChangeProperty "AllowBypassKey", dbBoolean, True
End Function
'This part is the function used to make the changes
Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Exit Function
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

Open in new window

Donald:  I don't see anything on sreeen!
Donald MaloneyConsultant

was gonna say "out loud" but you couldn't hear me,  then "on paper " but none here, so typing is "on screen"?

I know that, I was just sarcastically arguing the use of the word "thinking" ;-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.