Updating data in an Access form updates incorrect record
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.
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.
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.
Leigh
If I am correct it shouldn't be difficult to fix but I would have to see all the code for the form.
Leigh
When they are switched to a different record, is this a NEW record or an existing record?
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.
Don
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.
Don
ASKER
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.
workshop2.jpg
workshop1.jpg
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.
workshop2.jpg
workshop1.jpg
(My users are not that skilled) - Never under-estimate a hacker. Check out the hires just before the problem began.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Donald: I don't see anything on sreeen!
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" ;-)
AllowAdditions Property
You can use the AllowAdditions property to specify whether a user can add a record when using a form.
Setting
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.
Remarks
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?