Data entered via a Form not "sticking"

[MrYoda1]

I was working with a user who was using an Access file I created. The previous day two people had been entering some data into the file. Everything appeared to working and they thought the entries they made were actually staying put. When I stopped by the next day we went to use the newly entered data, but discovered it wasn't there. The person I was working with was in the room watching the other two make their entries.

So, we decided to re-enter the same data. I sat with her and watched as she took care of it. After updating a record she clicked on the Save button I created. The code I use is:

DoCmd.RunCommand acCmdSaveRecord

Once again, the data didn't stick. This time I did the updating and for some reason it all worked. I didn't do anything different. I did exactly what the other person did, but it worked (great for my reputation of having magic fingers; horrible for my reputation of creating an Access database that they trust)!

Then, the same thing happened to another user a few days later, who is located in a different school. I'm stuck with a method to figure this out and prevent it in the future.

Here are the particulars in case it helps:

• Windows 7
• Microsoft Access 2010
• Initially working off of a network server

The original users were working off a network server. The next time I relocated the file onto the user's computer.

Everything I've been able to find about this is pretty old (Access 2000 and 2003) so I'm hoping with a newer version someone has figured out what to look for.

[zmbd]

Well, not a lot to work with here;

+ The very first place I would work with is your IT-dept, make sure that your users have a true connection to the database and not an image, or a heavily buffered VPN, etc...

++ You point out that you had no issues, I am guessing that you logged on to the network under your user profile, this leads me to a similar issue I've had in the past! Double check with IT that your users have at least read/write/modify permissions to the folder. They need to be able to read the file, write new files, and modify the existing file structure - in that folder/directory.

As I mentioned, I've had occasion where I've had read and write permissions and was able to open and use the database only to have the data revert... weird, no explanation why, My IT-Guru didn't "get it" either; however, adding Modify to the privileges fixed the issue.

+ Next, have your problem user(s) enter a few records.
Typically there will be a "pencil" icon on the record while it is being edited.
BEFORE THE USER CLICKS or changes the focus from the current record to ANYWHERE ELSE on the form - the record needs to be in edit mode - have the user press <ctrl><s>. This explicitly saves the currently edited record.

close and reopen the database - see if the data stays.

+ Change your "save and close" button to:

If Me.Dirty Then
   Me.Dirty = False
End If
docmd.Close _
   objecttype:=acForm,objectname:=me.name, _
   save:=acSaveNo

The save:=acSaveNo will prevent any changes your user may have made to the form from being saved to the DESIGN.

Most of my forms, especially subforms, I have just the IF-Then in the on_close event

If Me.Dirty Then Me.Dirty = False

notice just the one command thus no need for the end if. This helps ensure that any open records are flushed.

+ You might want to take a look at Allen's info on this too
Allen Browne: Losing data when you close a form

[NeoPa]

zmbd:
the double save may be a bit overkill; however, it doesn't hurt things.

I was going to jump in anyway, but this I had to comment on.

What you have there is not a double-save! That is a good save (Albeit weird code as that is how Access has been designed.), followed by a save of the design of the form. In my view that is harm. Many designers allow design changes to be saved by users whenever they close a form after changing the sort or filter. I believe that's a limited approach. User's have no qualifications for changing the design. The design should always be as the designer left it (IMHO).

Back to the question though - Allen Browne's page is helpful on this. If the save fails then it won't always tell you - especially if saving and closing together. It's generally good practice either to check all the data is valid prior to issuing the save, or to check the error status afterwards in case it failed. I'm guessing you have an On Error Resume Next line in your code. Otherwise I'd expect the save to cause Access itself to prompt you.

[zmbd]

Neopa, good point, and one I should have thought of... I'll change line-6 to reflect that.

:) - of course, there's error trapping.... :)
Never leave home without it!

Also in my code I have data validation either at the table level, before_update, and/or after_update events.

For context:
I usually allow the user to re-order the columns in the datasheet views and a few other options such as custom filters. Most of my end-users are Chemists and quite familiar with what happens, hence the save:=AcSaveYes; however, that more than likely isn't the case for the majority of situations.

[MrYoda1]

Thanks for the help with this one, and the suggestions, as well.

Unbeknownst to me the problem turned out to be permissions set on the folder and file in question. Since I don't have access to any of the permissions it was serendipitous that I accidentally discovered the folder permission was set to Read Only!! I'm not sure why that was the setting since it wasn't something I suggested the user seek to accomplish. I wasn't aware that any of the tech support people in the building even knew the server folder existed.

Regardless, a valuable lesson learned. In the future I will definitely find a way to have the permissions changed, if necessary--before anyone attempts to enter any data!

To be honest, a part of me is happy and relieved with the cause. At least I know it wasn't something I did. Since everything had always worked in the past it was odd that it suddenly stopped working.