How to avoid Write Conflict when updating records with CurrentDb.Execute?
With the following code, I keep getting a dialog box that pops up with Write Conflict once I do a Select All as I click on its check box. The highlight ends up on CurrentDB.Execute sSQL and then I get the dialog box.
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.
The only problem there is no other user just me running VBA Code after an event - I understand that I have both the check box after update and click events running the same code but it doesn't seem to refresh the records correctly?
How may I adjust the following to not have that dialog box and process the transactions smoothly? Is there a better method than what I'm doing instead of using both the click event and after update to get the check boxes refresh properly on the form?
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.
The only problem there is no other user just me running VBA Code after an event - I understand that I have both the check box after update and click events running the same code but it doesn't seem to refresh the records correctly?
How may I adjust the following to not have that dialog box and process the transactions smoothly? Is there a better method than what I'm doing instead of using both the click event and after update to get the check boxes refresh properly on the form?
Private Sub chkShow_AfterUpdate()
Call Adj_Form
End Sub
Private Sub chkShow_Click()
Call Adj_Form
End Sub
Private Sub Adj_Form()
Dim sSQL As String
If chkShow.Value = True Then chkShow.Value = False Else chkShow.Value = True
If [Dept_Name] = "( Select All ) -------------------------" Then
sSQL = "UPDATE [shwPROJ_DPT] SET [Show] = " & chkShow.Value
CurrentDb.Execute sSQL
DoEvents
End If
Call Adj_Show_All
End Sub
see if this is what you want
TowerIII.mdb
TowerIII.mdb
ASKER
lee555J5:
This is the coding that I have per your instructions but it still doesn't work -
capricorn1:
Your version of my demo works but I can compare my code in the VBA window with yours - line by line and I don't see a difference? How come yours works with the same VBA code?
This is the coding that I have per your instructions but it still doesn't work -
capricorn1:
Your version of my demo works but I can compare my code in the VBA window with yours - line by line and I don't see a difference? How come yours works with the same VBA code?
ASKER
Sorry lee - didn't post the coding.
Private Sub chkShow_Click()
Call Adj_Form
End Sub
Private Sub Dept_Name_Click()
Call Adj_Form
End Sub
Private Sub Adj_Form()
Dim sSQL As String
If chkShow.Value = True Then chkShow.Value = False Else chkShow.Value = True
Screen.ActiveForm.Refresh
If [Dept_Name] = "( Select All ) -------------------------" Then
sSQL = "UPDATE [shwPROJ_DPT] SET [Show] = " & chkShow.Value
CurrentDb.Execute sSQL
Me.Refresh
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
capricorn1 is an inspirational genius!
=)
=)
Add the line
after the "If chkShow.Value = True Then..." line. This saves any changes to the record by the If statement.
Lee