Multiple active copies: "The Save action was canceled"

[JoshuaAnthony]

Hi there,

I chose Microsoft Access to write a database because I was under the impression that it could handle multiple users manipulating data concurrently. I have written the database and have done a few tests with two copies open at the same time. I have no problems in a form which is used purely to add new records. Unfortunately, in another form where I try to edit records, upon trying to save ANY record (even if the other copy of Access is not manipulating the same OR ANY record) I get the message: "The Save action was canceled".

Any suggestions?

 

[JoshuaAnthony]

In fact, just to do a simple test I created a table with 2 fields and linked it to a form. Opening two copies of this database, it doesn't matter if I am adding or editing records (regardless of whether both copies are manipulating EXACTLY the same record or not) I get the following message when pressing the save button:

"Microsoft Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file."

Just for the record, I have 'default open mode' set to 'shared' not 'exclusive' under 'tools->options->advanced'.

 

[Pat Hartman]

Multi-user means multiple people entering and changing DATA, not multiple people creating and changing OBJECTS such as forms and reports. Access works fine for sharing production database applications. If your app is still under development and you need to have multiple developers creating and changing objects in a shared database, you will need to install a program such as SourceSafe to manage object changes.

Apparently, your application modifies a form and tries to save it. That will only work if there is only a single user in the db at that time. In a multi-user environment, you CANNOT modify objects. Perhaps your problem is as simple as doing -
DoCmd.RunCommand acCmdSave
when you really mean -
DoCmd.RunCommand acCmdSaveRecord

The first attempts to save the active object which is most likely a form whereas the second, saves the current record.

 

[JoshuaAnthony]

Thanks very much. That works now.

So when you save a form or report, you are actually saving design changes rather than the record to the table?

Also, I've found that when I close a form, changes I have made to the records are seen in the table without saving at all. Is it possible to set a property somewhere so that the user HAS to save the record for the changes to actually be seen in the table (to prevent accidental changes to records)? I do want the user to be able to edit records (so want to have AllowEdits set to 'yes').

 

[Mile-O]

Get rid of the current Close button on your form with the Close Button property.

Now, put a command button on your form with the Caption: Close, or something. Call it cmdClose

On it's Click() event put the code:

Private Sub cmdClose_Click()
    If Me.Dirty Then
        If MsgBox("Would you like to save your changes?", vbQuestion + vbYesNo, "Save Changes?") = vbNo Then
            Me.Undo
        End If
    End If
    DoCmd.Close acForm, Me.Name
End Sub

 

[JoshuaAnthony]

That's awesome.

Thanks mate.

 

[Pat Hartman]

Sorry but that doesn't really do it. You can still close the database which will cause any dirty records to be saved, you can still use the navigation keys to move to another record which will also cause the current record to be saved, and you can still click into a subform which will cause the current record to be saved. If I think about it long enough, I may come up with more ways to save a dirty record.

But, as I have said MANY, MANY times before, if you want to absolutely trap bad data or somehow prevent a record from being saved, you MUST use the BeforeUpdate event of the form. Any other methods are not singlely effective but always must be used in conjunction with code in other events. You have to figure out every way in which Access will attempt to save the dirty record and put code in those events.

Think of the BeforeUpdate event of the form as - The Buck Stops Here or The End of the Line or the mouth of the funnel. EVERY record that is updated via a form, MUST pass through the BeforeUpdate event of that form.

If you don't want the user to be able to edit records, open the form in read only mode or set the AllowEdits, AllowDeletions, and AllowAdditions properties to No.

 

[stormin_norm]

Pat-
In an Access97 database converted to 2000, a button allowed the user to toggle between edit mode and R/O mode -- the code behind the button loops through each control on a form and sets the AllowEdits/Deletions/Additions to false.

If I wish to duplicate this feature I'll follow your advice above and set AllowEdits/Deletions/Additions to false on the FORM vs. each control. Nice, easy, less code

I'm not a real big fan of this toggle switch...I like the idea of using BeforeUpdate event with a msgbox and Me.Undo and Cancel=True.
Now to convince my customer.

Thanks Pat and Mile-o-phile for all the undo threads. I enjoyed searching on ALL of them
Maybe a sticky?
Yet another reason for a most asked questions section of this web site.