Solved Write Conflict Issue (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
Looks like a few folks have issues with the Write Conflict popup.
I'm going to join that group today.

I have a form that is open from another form. (F_Project has button to open F_Contract. See below)
When a DateofAward is entered in the SF_ContractInfo (subform to F_Contract), I want the ProjectPriority to show "OBL" and the Award to be checked, in the F_Project.

Below I've entered a date in the DateofAward and it changes the ProjectPriority and the Award is now checked. This is all good.
I can close the F_Contract, and all is still good. Albeit, the F_Project is still dirty.
When I close or try to edit the F_Project, I get the Write Conflict popup.

How do prevent this Write Conflict from popping up?

1654287629515.png




1654287806749.png



Here's my code for the AfterUpdate on the DateofAward text box in the SF_ContractInfo.


Code:
Private Sub txtDateofAward_AfterUpdate()
   DoCmd.RefreshRecord
'This will automatically change the Active Award in the F_Project.
Dim projID As Variant

'get the projectid
projID = Nz(DLookup("ProjectID", "TJ_ProjectContract", "ContractID = " & Me.ContractID), 0)
'check if form is open
If SysCmd(acSysCmdGetObjectState, acForm, "F_Project") <> 0 Then
    'form is open
    'find the projID on F_Project and change the ActiveAward and ProjectPriority
    Set rs = [Forms]![F_Project].Form.RecordsetClone
    With rs
        .FindFirst "ProjectID = " & projID
        If Not .NoMatch Then
            .Edit
            !ActiveAwarded = Not IsNull(Me!DateofAward)
                If Not IsNull(Me.DateofAward) And CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = "OBL"
                    Forms!F_Project.Form.CheckActiveAwarded = True

                            
                ElseIf CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = ""
                    Forms!F_Project.Form.CheckActiveAwarded = False
                    
                End If
            .Update
        End If
    End With
    Set rs = Nothing
  
Else
    'form not open, use SQL update query
    CurrentDb.Execute "update T_Project Set ActiveAwarded = " & (Not IsNull(Me!DateofAward)) & " Where ProjectID = " & projID
End If
  
End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:49
Joined
Oct 29, 2018
Messages
21,358
You could try saving the record changes immediately after making them, so the form won't be dirty when you try to edit it.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
So I tried the following but, now it won't put the "OBL" in the ProjectPriority text box.
Where I'm going wrong?

Code:
Private Sub txtDateofAward_AfterUpdate()
   DoCmd.RefreshRecord
'This will automatically change the Active Award in the F_Project.
Dim projID As Variant

'get the projectid
projID = Nz(DLookup("ProjectID", "TJ_ProjectContract", "ContractID = " & Me.ContractID), 0)
'check if form is open
If SysCmd(acSysCmdGetObjectState, acForm, "F_Project") <> 0 Then
    'form is open
    'find the projID on F_Project and change the ActiveAward and ProjectPriority
    Set rs = [Forms]![F_Project].Form.RecordsetClone
    With rs
        .FindFirst "ProjectID = " & projID
        If Not .NoMatch Then
            .Edit
            !ActiveAwarded = Not IsNull(Me!DateofAward)
                If Not IsNull(Me.DateofAward) And CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = "OBL"
                    Forms!F_Project.Form.CheckActiveAwarded = True
                    If Forms!F_Project.Dirty Then
                        Forms!F_Project.Dirty = False
                    End If
                    
                            
                ElseIf CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = ""
                    Forms!F_Project.Form.CheckActiveAwarded = False
                    If Forms!F_Project.Dirty Then
                        Forms!F_Project.Dirty = False
                    End If
                End If
            .Update
        End If
    End With
    Set rs = Nothing
  
Else
    'form not open, use SQL update query
    CurrentDb.Execute "update T_Project Set ActiveAwarded = " & (Not IsNull(Me!DateofAward)) & " Where ProjectID = " & projID
End If
  
End Sub

1654292710391.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:49
Joined
Feb 19, 2002
Messages
42,981
Something is wrong with your logic if you are changing a value on form A and that must also update form B. In a relational database we do not store the same piece of data in multiple places.

The error message you are getting is because YOU are conflicting with YOURSELF. As theDBguy suggested, ALWAYS save the current form to ensure it is not dirty before opening any other form or report or running an action query.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
Sometimes logic is not what the boss wants....so I'm trying to make it happen for him even though it might not follow good database practices.
So are you saying it's not possible or not preferred?

I still consider myself a novice at database development, so I would really appreciate some add'l help.

I'm thinking I am saving the current form (F_Contract) when I docmd.refreshrecord prior to the code calling for the change in the F_Project.
I don't have to close the every form prior to opening another form, do I?

Thanks for the help.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:49
Joined
Oct 29, 2018
Messages
21,358
I'm thinking I am saving the current form (F_Contract) when I docmd.refreshrecord prior to the code calling for the change in the F_Project.
I'm not sure that saves the record, but maybe it does. Otherwise, it's better to be explicit about it. You can use DoCmd.SaveRecord (I think), but I use Me.Dirty=False to do it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:49
Joined
Feb 19, 2002
Messages
42,981
So are you saying it's not possible or not preferred?
You can do almost anything you want. I told you how to avoid the error message.

Immediately before form A opens form B, form A must save its current record using
DoCmd.RunCommand acCmdSaveRecord

Otherwise when the code in form B tries to modify form A, if form A is dirty, you get this error message. No matter how poor the practice is, you can still do lots of bad things.

You seem to have interpreted that as having to save form B before updating form A.

A better solution would be to fix the schema to not duplicate the data but you can still do what you are doing.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
If I take the saving of the F_Project out (turned it to a comment), then it executes the "OBL". Of course, I get the Writing Conflict again.
How do I fix this?
It seems to skip the Forms!F_Project.Form.txtPropertyPriority = "OBL"

Code:
            !ActiveAwarded = Not IsNull(Me!DateofAward)
                If Not IsNull(Me.DateofAward) And CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = "OBL"
                    Forms!F_Project.Form.CheckActiveAwarded = True
                    'If Forms!F_Project.Dirty Then
                        'Forms!F_Project.Dirty = False
                    'End If
                    
                            
                ElseIf CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = ""
                    Forms!F_Project.Form.CheckActiveAwarded = False
                    If Forms!F_Project.Dirty Then
                        Forms!F_Project.Dirty = False
                    End If
                End If
 

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
You can do almost anything you want. I told you how to avoid the error message.

Immediately before form A opens form B, form A must save its current record using
DoCmd.RunCommand acCmdSaveRecord

Otherwise when the code in form B tries to modify form A, if form A is dirty, you get this error message. No matter how poor the practice is, you can still do lots of bad things.

You seem to have interpreted that as having to save form B before updating form A.

A better solution would be to fix the schema to not duplicate the data but you can still do what you are doing.
Thanks Pat. I will give that a try.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
Pat,
After reading your response again and looking at my snip, let me clarify something.
Form A is not dirty when Form B opens. The picture may look like that but that's not what is happening.
Form A goes dirty when I update the DateofAward in Form B.
Form A - F_Project
Form B - F_Contract

Then when I close Form B, Form A remains dirty. Then when a try to close or edit Form A, I get the Write Conflict.

If I add the following Forms!F_Project.Dirty = False in the code below, to save the Form A immediately after I update it, then the Forms!F_Project.Form.txtProjectPriority = "OBL" does not work.

Does that help clarify the problem?


This code is in Form B on the AfterUpdate event of Date of Award text box.
Code:
Private Sub txtDateofAward_AfterUpdate()
Me.Dirty = False
  
'This will automatically change the Active Award in the F_Project.
Dim projID As Variant

'get the projectid
projID = Nz(DLookup("ProjectID", "TJ_ProjectContract", "ContractID = " & Me.ContractID), 0)
'check if form is open
If SysCmd(acSysCmdGetObjectState, acForm, "F_Project") <> 0 Then
    'form is open
    'find the projID on F_Project and change the ActiveAward and ProjectPriority
    Set rs = [Forms]![F_Project].Form.RecordsetClone
    With rs
        .FindFirst "ProjectID = " & projID
        If Not .NoMatch Then
            .Edit
            !ActiveAwarded = Not IsNull(Me!DateofAward)
                If Not IsNull(Me.DateofAward) And CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = "OBL"
                    Forms!F_Project.Form.CheckActiveAwarded = True
                    Forms!F_Project.Dirty = False
                            
                ElseIf CurrentProject.AllForms("F_Project").IsLoaded Then
                    Forms!F_Project.Form.txtProjectPriority = ""
                    Forms!F_Project.Form.CheckActiveAwarded = False
                    Forms!F_Project.Dirty = False
                End If
            .Update
        End If
    End With
    Set rs = Nothing
  
Else
    'form not open, use SQL update query
    CurrentDb.Execute "update T_Project Set ActiveAwarded = " & (Not IsNull(Me!DateofAward)) & " Where ProjectID = " & projID
End If
  
End Sub
 

Attachments

  • 1654305455418.png
    1654305455418.png
    63.2 KB · Views: 128

Weekleyba

Registered User.
Local time
Yesterday, 23:49
Joined
Oct 10, 2013
Messages
586
I found the error.
I had added the !ActiveAwarded = Not IsNull(Me!DateofAward) earlier.
I removed that from the code and everything works as expected. It was left over from before. Not sure why I couldn't see it before.
Thanks for all your help!

Code:
Private Sub txtDateofAward_AfterUpdate()
Me.Dirty = False
  
'This will automatically change the Active Award in the F_Project.
Dim projID As Variant

'get the projectid
projID = Nz(DLookup("ProjectID", "TJ_ProjectContract", "ContractID = " & Me.ContractID), 0)
'check if form is open
If SysCmd(acSysCmdGetObjectState, acForm, "F_Project") <> 0 Then
    'form is open
    'find the projID on F_Project and change the ActiveAward and ProjectPriority
    Set rs = [Forms]![F_Project].Form.RecordsetClone
    With rs
        .FindFirst "ProjectID = " & projID
        If Not .NoMatch Then
            .Edit
            '!ActiveAwarded = Not IsNull(Me!DateofAward)
            If Not IsNull(Me.DateofAward) And CurrentProject.AllForms("F_Project").IsLoaded Then
                Forms!F_Project.Form.txtProjectPriority = "OBL"
                Forms!F_Project.Form.CheckActiveAwarded = True
                Forms!F_Project.Dirty = False
                        
            ElseIf CurrentProject.AllForms("F_Project").IsLoaded Then
                Forms!F_Project.Form.txtProjectPriority = ""
                Forms!F_Project.Form.CheckActiveAwarded = False
                Forms!F_Project.Dirty = False
            End If
            .Update
        End If
    End With
    Set rs = Nothing
  
Else
    'form not open, use SQL update query
    CurrentDb.Execute "update T_Project Set ActiveAwarded = " & (Not IsNull(Me!DateofAward)) & " Where ProjectID = " & projID
End If
  
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:49
Joined
Oct 29, 2018
Messages
21,358
Glad to hear you got it sorted out.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:49
Joined
Feb 19, 2002
Messages
42,981
If the "boss" is the user, he doesn't get to tell you how to do your job. You don't tell him how to run the company. He doesn't tell you how to design a database. If the "boss" is technical, we can blame the error in the schema on him but it is still there and will always be a problem. Maybe, you can earn some points by figuring it out and offering a solution.
 

Users who are viewing this thread

Top Bottom