Error when refreshing form

[MChancellor]

Hi! I'm having a problem when I try to refresh a form after updating some information in the form through the use of a macro. Below are the steps that are performed by the macro and the error we get:

We start by deleting and recreating a table of data that is used to update the information on the main form from the sub form. We then stop and save any changes that have been made. Then we set the value of the check box on the main form to on in the record that we want to run the report for. Following this, we again save any changes and then attempt to refresh. At which point, we get the following error message:

"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

Save Record Copy to Clipboard Drop Changes"

If we choose "save record" the changes that we made in the subform are not updated to the main form, and the old information appears in the report. If we choose "copy to clipboard" or "drop changes" the changes are made to the main form from the subform, but the report is blank when it comes up. (In order for the information to be selected for the report, the checkbox value has to be set to "on". Apparently when you do not choose to save the record it doesn't keep the value of the check box set to on, even though it refreshes the other information in the form.) This error only occurs when we try to refresh - regardless of if the refresh is due to a macro command, moving from one record to another, or clicking in the main form after the changes are made (after the macro updating the information is run - without a refresh command).

What I'm needing to know is if there is some way to circumvent this error or to eliminate this error. We've tried practically every arrangement of the macro commands that we can think of that would be logical, but so far all we've managed to do is frustrate ourselves.

Any help would be greatly appreciated.

Thanks!
Misti

 

[VBslammer]

Your form is probably set to the default record locking of 'No Locks' or optimistic locking. Have you tried setting it to 'Edited Record' or 'All Records'?

VBSlammer

 

[MChancellor]

I tried all three options (No Lock, All Records, Edited Record) and got the same error on all three. We may have to try to see if we can come up with some other kind of work around for this. I understand that this error appears to be one that is common to Access 2000, but usually occurs more often when you are using a SQL Server back end with an Access front end. We aren't using SQL Server, so I'm not sure what the best fix for us would be. I guess it is seeing our update query as another user. Any suggestions as to a work around solution to this problem will be appreciated.

Thanks for your help.

Misti

 

[VBslammer]

I have had similar problems with this error when trying to do everything programmatically. Sometimes the inclusion of the 'DoEvents' method at key locations in your code allows the system to refresh itself.

VBSlammer