Issue with write conflict (1 Viewer)

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
I keep getting the write conflict box appear when running some update code. I believe the issue is the code runs some SQL that updates the table, but then the form and table become out of sync.

All the code does is ask the user if the option they choose is correct, then updates a check box if they select yes. That sets the control on the form to true and then runs an update query within the code to make sure the table gets properly updated. Normally that happens when the user checks the box, but since in this instance the box isn't being clicked, I am doing via VBA.

I would guess doing that way is the root of the problem and there must be better methods to it. I did try adding me.dirty = false to the start of the code, but alas, no luck.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:28
Joined
May 21, 2018
Messages
4,766
make sure before running the sql that you set the form's dirty property to false.
 

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
make sure before running the sql that you set the form's dirty property to false.
I did try that after reading around, but that still didn't resolve the conflict.

The code for reference is (with the dirty part since it has been removed):
Code:
Private Sub LockQuoteBtn_Click()

 Dim strsql


If Me.QuoteComplete = True Then
    
    Me.ProtectedCheckbx = True
        strsql = "UPDATE tblJobDetails SET tblJobDetails.Protected = True"
        strsql = strsql & " WHERE (([QuoteNum] Like [Forms]![JobQuote]![QuoteNum]));"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql
        DoCmd.SetWarnings True
    'Call LockForm
Else
    If MsgBox("Is the quote done/complete?", vbYesNo, "Lock Quote") = vbYes Then
    
    
    Me.QuoteComplete = True
    Me.CompletedDate = Date
    Me.JobAwarded.Visible = True
    Me.JobAwardedLabel.Visible = True
    Me.JobLost.Visible = True
    Me.JobLostLabel.Visible = True
        strsql = "UPDATE tblJobDetails SET tblJobDetails.QuoteComplete = True"
        strsql = strsql & " WHERE (([QuoteNum] Like [Forms]![JobQuote]![QuoteNum]));"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql
        DoCmd.SetWarnings True
    
    Me.ProtectedCheckbx = True
        strsql = "UPDATE tblJobDetails SET tblJobDetails.Protected = True"
        strsql = strsql & " WHERE (([QuoteNum] Like [Forms]![JobQuote]![QuoteNum]));"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql
        DoCmd.SetWarnings True

    'Call LockForm
  
        
    End If
End If

'DoCmd.RunCommand acCmdSave
DoCmd.RefreshRecord
DoCmd.Requery


End Sub
 

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
I added in the refresh/requery hoping that would resolve it as well, but it did not.
I originally had me.dirty = false at the top before any of the code started.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:28
Joined
Feb 19, 2002
Messages
31,487
Running action queries that update the current record is poor practice.
Everything that updates the current record should be using bound controls or VBA behind the form.

If you are trying to update all the Revs of a quote, your table structure is incorrect. Put the common data in the quote table and create a separate table for the rev data.
 

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
Running action queries that update the current record is poor practice.
Everything that updates the current record should be using bound controls or VBA behind the form.
What would be the best method for this one then?
I need the field in the table to be updated, as well as its corresponding control the form to reflect that. Normally updating the field is done when you click the control, but in this instance, they clicked the button before checking the box.
 

Isaac

Lifelong Learner
Local time
Today, 15:28
Joined
Mar 14, 2017
Messages
4,393
- use a bound control
- Me.Checkbox=True
-Me.Dirty=false, or however you save records
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:28
Joined
Feb 19, 2002
Messages
31,487
Simply changing the value of a control, puts it on the path to being saved when you leave the record or force a save.
So,

Me.SomeControlName = SomeCalculation or whatever is all you need.

If you use the practice of giving the controls different names from the bound fields (as I do), then ALWAYS update the control name if you want the change to be immediately visible on the form.

So, if my control is named txtRevNum and it is bound to a field named RevNum, then
Both
Me.txtRevNum = NewValue
and
Me.RevNum = NewValue

will end up updating the table when the record gets saved. The difference is that the user will see the control on the form change if you use the first method but will not see the field on the form change if you use the second method.

When you use wizards to create forms, Access automatically assigns the Bound Field name as the Control name so the column RevNum becomes the control name as well. If for some reason, you make a change to the control to bind it to a different field but don't change the name of the control so the form name is RevNum and the new bound field is xyz, you won't get an error but you may later get some strange mystery message if you try to add RevNum back to the form. You won't be able to because you already have a control by that name.

When you create forms manually by creating the control and later binding the control to a field in the recordSource, they will end up with names like Text83 or Combo25. If you bind these to RevNum and Quote number, you modify the values in VBA by referencing RevNum and Quote, the values will get saved but will not actually show on the form. This may be what you did.

More Best practice advice - When you create a control manually and bind it later, make sure you change the name of the control IMMEDIATELY so you won't forget. Use a prefixed name if you like or use the name of the bound field if you prefer that but DO NOT leave controls named Text97 EVER. Neatness counts. Just as aligning your clauses in a nested IF saves your sanity, so does keeping control over your object names. Access lets you do things that will make your hair fall out as you get old before your times.

Your only defense is sound development practices. And while I'm on a lecture rant, save early, save often. Make multiple backups during the day as you develop. You'll thank me for that too.
 
Last edited:

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
I did make the mistake early on with not naming controls. I have been paying for that since. Now I name them to practical things such as open(someform)btn or usercmbbx etc. Makes them super identifiable in what they are bound to as well as to what kind of control they are at a glance.

I have been fixing the ones I did not name originally as I find them.

I will change the code I am doing to do what you suggested rather than run an update query. I guess I was super convoluting it.

As far as backups go, I learned that one the hard way too. I now make a backup before I open it every time, and also after I complete a feature/fix/update etc.

My poor recycle bin is full of deleted backups.
 
Last edited:

Minty

AWF VIP
Local time
Today, 23:28
Joined
Jul 26, 2013
Messages
8,186
And just to add to the rantyness :cool: ...
This

Me.QuoteComplete = True
Me.CompletedDate = Date


Makes no sense, QuoteComplete is a given if it has a completed date. And ergo it's not complete if it hasn't got a date.

With this structure, you are having to update a second field based on the date being added, and reversing the field contents if that date gets removed, which is all lots of unnecessary work.
Yes/no boolean fields are rarely any use for actually storing data. If it's an event use an event date.
 

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
And just to add to the rantyness :cool: ...
This

Me.QuoteComplete = True
Me.CompletedDate = Date


Makes no sense, QuoteComplete is a given if it has a completed date. And ergo it's not complete if it hasn't got a date.

With this structure, you are having to update a second field based on the date being added, and reversing the field contents if that date gets removed, which is all lots of unnecessary work.
Yes/no boolean fields are rarely any use for actually storing data. If it's an event use an event date.
That makes sense. I did it that way as I wanted it to be annoyingly obvious to the users. Some of the people I am dealing with a not computer literate at all.
I had asked if they would prefer to enter a complete date to flag it as complete, or check a box it is complete and the date get populated. They all requested the latter lol.
 

Minty

AWF VIP
Local time
Today, 23:28
Joined
Jul 26, 2013
Messages
8,186
Schoolboy error, asking users what they prefer ;)
That's fine - you don't need to store the checkbox though, do you...
 

tmyers

Active member
Local time
Today, 18:28
Joined
Sep 8, 2020
Messages
692
Schoolboy error, asking users what they prefer ;)
That's fine - you don't need to store the checkbox though, do you...
Kinda? I use it mostly as a visual indicator.
 

Isaac

Lifelong Learner
Local time
Today, 15:28
Joined
Mar 14, 2017
Messages
4,393
Yes/no boolean fields are rarely any use for actually storing data. If it's an event use an event date.

Untitled.jpg
 

Minty

AWF VIP
Local time
Today, 23:28
Joined
Jul 26, 2013
Messages
8,186
Kinda? I use it mostly as a visual indicator.
You can simply calculate it and you know it's always correct.

QuoteCompleted: Not IsNull([DateCompleted])
 

Users who are viewing this thread

Top Bottom