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?

Thanks,

Joshua

 

[shreked]

Joshua

can you post the code for how you are editing the records
so i can have a better understanding of what you are doing

Shrek

 

[JoshuaAnthony]

Errrmmmm... I know this is quite messy but it works fine when I only have one copy of Access running:


Private Sub Log_Transfer_Details_Click()
On Error GoTo Err_Log_Transfer_Details_Click

Dim Next_Location As String

DoCmd.OpenForm "Personnel", , , "[Extensions]![Personnel] = [Forms]![Transfer NCR]![Personnel]"
Next_Location = [Forms]![Personnel]![Location]
DoCmd.Close acForm, "Personnel", acSaveNo

DoCmd.GoToRecord , , acLast

' The following loops through existing records to find one with user-entered NCR number
' Once found, change the location field and update the time out from old location and
' the total time spent in that location.
Do Until [Forms]![Transfer NCR]![NCR Number] = 0
If [Forms]![Transfer NCR]![NCR Number] = [Forms]![Transfer NCR]![NCR] Then

' Find the current location for timestamping the time OUT field and calculating total time in location
Select Case [Forms]![Transfer NCR]![Location]
Case "Cause/Corrective"
[Forms]![Transfer NCR]![Cause/Corrective Time Out] = Now()
[Forms]![Transfer NCR]![Cause/Corrective Total Time] = [Forms]![Transfer NCR]![Cause/Corrective Time Out] - [Forms]![Transfer NCR]![Cause/Corrective Time In] + [Forms]![Transfer NCR]![Cause/Corrective Total Time]
MsgBox ([Forms]![Transfer NCR]![Cause/Corrective Total Time])
Case "MRB Disposition"
[Forms]![Transfer NCR]![MRB Disposition Time Out] = Now()
[Forms]![Transfer NCR]![MRB Disposition Total Time] = [Forms]![Transfer NCR]![MRB Disposition Time Out] - [Forms]![Transfer NCR]![MRB Disposition Time In] + [Forms]![Transfer NCR]![MRB Disposition Total Time]
Case "MRB Office"
[Forms]![Transfer NCR]![MRB Office Time Out] = Now()
[Forms]![Transfer NCR]![MRB Office Total Time] = [Forms]![Transfer NCR]![MRB Office Time Out] - [Forms]![Transfer NCR]![MRB Office Time In] + [Forms]![Transfer NCR]![MRB Office Total Time]
Case "MRB Signatory"
[Forms]![Transfer NCR]![MRB Signatory Time Out] = Now()
[Forms]![Transfer NCR]![MRB Signatory Total Time] = [Forms]![Transfer NCR]![MRB Signatory Time Out] - [Forms]![Transfer NCR]![MRB Signatory Time In] + [Forms]![Transfer NCR]![MRB Signatory Total Time]
Case "Planning"
[Forms]![Transfer NCR]![Planning Time Out] = Now()
[Forms]![Transfer NCR]![Planning Total Time] = [Forms]![Transfer NCR]![Planning Time Out] - [Forms]![Transfer NCR]![Planning Time In] + [Forms]![Transfer NCR]![Planning Total Time]
Case "QA Signatory"
[Forms]![Transfer NCR]![QA Signatory Time Out] = Now()
[Forms]![Transfer NCR]![QA Signatory Total Time] = [Forms]![Transfer NCR]![QA Signatory Time Out] - [Forms]![Transfer NCR]![QA Signatory Time In] + [Forms]![Transfer NCR]![QA Signatory Total Time]
Case "Other"
[Forms]![Transfer NCR]![Other Time Out] = Now()
[Forms]![Transfer NCR]![Other Total Time] = [Forms]![Transfer NCR]![Other Time Out] - [Forms]![Transfer NCR]![Other Time In] + [Forms]![Transfer NCR]![Other Total Time]
Case Else ' This should never occur since location is a drop down menu...
MsgBox ("INTERNAL ERROR - Log_Transfer_Details_Click(): Current Location doesn't exist&quot
End Select

' Log the next NCR destination as the current location
[Forms]![Transfer NCR]![Status] = "Active"
[Forms]![Transfer NCR]![Stored Personnel] = [Forms]![Transfer NCR]![Personnel]

' Find the next location for timestamping the time IN field
Select Case Next_Location
Case "Cause/Corrective"
[Forms]![Transfer NCR]![Cause/Corrective Time In] = Now()
Case "MRB Disposition"
[Forms]![Transfer NCR]![MRB Disposition Time In] = Now()
Case "MRB Office"
[Forms]![Transfer NCR]![MRB Office Time In] = Now()
Case "MRB Signatory"
[Forms]![Transfer NCR]![MRB Signatory Time In] = Now()
Case "Planning"
[Forms]![Transfer NCR]![Planning Time In] = Now()
Case "QA Signatory"
[Forms]![Transfer NCR]![QA Signatory Time In] = Now()
Case "Other"
[Forms]![Transfer NCR]![Other Time In] = Now()
Case Else ' This should never occur since location is a drop down menu...
MsgBox ("INTERNAL ERROR - Log_Transfer_Details_Click(): Next Location doesn't exist&quot
End Select
DoCmd.Save acForm, "Transfer NCR"
MsgBox ("NCR transferred to " & [Forms]![Transfer NCR]![Stored Personnel] & " in " & Next_Location & " successfully&quot
DoCmd.Close
Exit Sub
Else
DoCmd.GoToRecord , , acPrevious
End If
Loop

MsgBox ("NCR not found&quot

Exit_Log_Transfer_Details_Click:
Exit Sub

Err_Log_Transfer_Details_Click:
MsgBox Err.Description
Resume Exit_Log_Transfer_Details_Click

End Sub

 

[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'.

 

[shreked]

Joshua

I think your problem is your trying to save the form. Is this line of code for saving your record you have updated

if so all you need to do is move the recordset to save a record not the form

DoCmd.GoToRecord , , acNext

DoCmd.Save acForm, "Transfer NCR"

 

[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 the form is linked to?

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').