Solved Editing conflicts after splitting the database

[hhag]

Access gives you constantly new challanges....
After I splitted the database in order to open up for additional users I get a message 'Write conflict', or 'Record has been changed by another user'.
The thing is that I know for sure that no other 'user' has been doing changes (i'm using a test version, where I've sole access). But I've a macro to do some changes.

I've a form where I do some follow-up. In this form I've a combobox where I change status for a record. When changing status I trigger the event 'AfterUpdate'. With the code I change status of different fields in three different tables depending on changed status. Everything seems to work, since all fields are changed accordinlgy. It's when I'm leaving the form I get the message 'Record has been changed by another user', and the 'error' message indicates the same table continuously. And a second check shows that all changes created by the macro are OK.

I've not been written so much VBA code in Access (my background is from Excel) but in my opinion I've done some simple coding. E.g. when assigning new values in fields I use:

With rsXXXX
.Edit
.Fields("Date")=Date
.Fields("Actual")=True
.Update
End with

For a moment I thought that '.Update' might has something to do with it, but it didn't seem so after some testing. And when using this in other tables I don't get any 'error' message.
Can you add some code that will stop this check or something else in order to avoid it? I know for sure that we'll not have any conflicts among real users concerning changing status in a specific record.

Does anyone have any idea how to solve this? When I hade the table, forms, queries in the same file, I didn't have this phenomenon.

I'm extremely grateful for some advice and findings

Rgds,
HHAG

 

[theDBguy]

Hi. Just to confirm, are you saying you didn't get any write conflict error before splitting the db?

 

[hhag]

Yes, I didn't see this message before....
I do changes in three different tables with the macro code, and only one 'kicks backwards'.

 

[hhag]

I've read bout using locks to stop overlapping edits. This might be an idea?

 

[hhag]

I need to leave my PC for a couple of hours now. I'll be back this eventing.

 

[theDBguy]

Yes, I didn't see this message before....
I do changes in three different tables with the macro code, and only one 'kicks backwards'.

Interesting. I couldn't understand why without seeing your code and your db setup. Normally, you would only want to update a single table per user input form.

 

[isladogs]

Is the backend an Access file or in SQL Server?

 

[hhag]

It's an Access file.
The form is based on a multi table view and I've googled that this might create problems. Well, you'll learn as long as you live.
I've a main form with a tab-control of three different tabs. One tab contains the 'main data' from one specific table and the other two tabs contain two different subforms based on each table. The main table is the parent till the two other child-tables.
I thought that using a tab control will keep all data related to one main-record would be a nice thing. But now, since I splitted the database I might need to re-think maybe?

The VBA code is built with the following structure:

Defining strSQLXXX (I've about five of these)

Set db=CurrentDb
Set rsXXX = db.openRecordSet(StrSQLXXX, dbOpenDynaset)

rsXXX.MoveLast
rsXXX.MoveFirst

If 'condition' Then

With rsXXX
.Edit
.Fields("Field1")=True
.Update
End with

rsXXX.Close

Set rsXXX=Nothing

db.Close

End Sub

In my simplicity since I close the Recordset I think that no editing conflicts might be possible. Not in the child tatable at least.

 

[Deleted member 147267]

Somehow you have placed a record in edit mode and are trying (perhaps not deliberately) to edit the same record. One way to do this is have a record in edit mode and attempt to alter another record which then attempts to cascade update the first record. Sometimes the answer is to ensure the first record edit is committed. I have also seen forms open with a record in edit mode (thus it is already locked without a single keystroke or mouse move), probably because of calculations but I never bothered with the reason why as it was only a testing db. IMO splitting a db or putting subforms on tab control pages is not the cause of this when we're talking single user here. It may be that a form Refresh will help, but that might also hide fact that the problem is due to your db process, which may need tweaking. If the process is right and just can't be helped, the Refresh might eliminate the issue.

 

[hhag]

I've tried to change and add records in the child tables presented as subforms in the tab-control. And this works fine. No message about editing conflicts. It's when I chane status in the main table (parent table) one table shows an editing conflict.

 

[hhag]

Is it all three forms (the main and the two subforms) I should try to refresh?

 

[Deleted member 147267]

I've tried to change and add records in the child tables presented as subforms in the tab-control. And this works fine. No message about editing conflicts. It's when I chane status in the main table (parent table) one table shows an editing conflict.

What can I say except what I've already said? If it's a single user situation, then something has already modified that record (but has not committed the edit) before you attempt to deliberately edit it. At least, it's the most likely reason. If it turns out there is another I will learn something about the issue.

Is it all three forms (the main and the two subforms) I should try to refresh?

Probably won't hurt. Might be a band-aid solution, but hey, we all need a band-aid sometimes?
When you have such issues, do you have a backup db in case code and design changes go horribly awry?

 

[hhag]

Does anyone know if there's a way to run a macro after an open record is closed and the form is about to be closed? Some kind of an eventbased propertrty in order to prevent overlapping edits?

 

[theDBguy]

Does anyone know if there's a way to run a macro after an open record is closed and the form is about to be closed? Some kind of an eventbased propertrty in order to prevent overlapping edits?

Hi. Not sure if this will help you, but try the UnLoad event.

 

[Deleted member 147267]

If 2 records are being edited at the same time by different people, you'd want to catch that before Unload - probably BeforeUpdate. I would think Access will present you with a system message at that point, asking you what you want to do about it. If it's the same user causing their own write conflict you have a different issue.

 

[hhag]

Seems to be solved. Shame on giving up!
I removed my event based macro from the combobox and replaced it under the Form_AfterUpdate() event. And then suddenly my editing conflicts seems to be erased. And all records in all three tables seems to be according to expected and the conflict message is gone. Nice. I hope nothing will change overnight

Thank you all for your advices tips etc. I can't express me gratitude enough!

 

[theDBguy]

Seems to be solved. Shame on giving up!
I removed my event based macro from the combobox and replaced it under the Form_AfterUpdate() event. And then suddenly my editing conflicts seems to be erased. And all records in all three tables seems to be according to expected and the conflict message is gone. Nice. I hope nothing will change overnight

Thank you all for your advices tips etc. I can't express me gratitude enough!

Hi. Congratulations! However, I am still perplexed as to why you would only have this problem after splitting the database. No matter. Good luck with your project.

 

[Deleted member 147267]

I am still perplexed as to why you would only have this problem after splitting the database

Coincidence.

 

[theDBguy]

Coincidence.

Could be. I was hoping they would test it. Use an unsplit copy and "see no error." Then, use a split copy and "get error."

 

[Galaxiom]

The "other user" is yourself on the form. This error is a very common problem when accessing records in two different ways and trying to edit them. It doesn't cause a problem with the Form After Update because the record edit on the form is completed before the code is run. If you use the control AfterUpdate the record is still dirty when you run the code and Access detects the conflict.

The actual mistake is loading a bound form, starting an edit on a record then attempting to edit the record in the recordset. It is bad design and wrong thinking.