Write conflict message box appears?

[adh123]

I have a form for updating contact details of customers/suppliers. Upon clicking an edit button the user can update txt boxes (as opposed to updating the table data directly and making a mistake). When they press save the below code runs:

If Not IsNull(Me.txtp_fname.Value) Then
    Dim strFirstName As String
    strFirstName = Me.txtp_fname
    Dim FirstName As String
    FirstName = Replace(strFirstName, "'", "''")
End If

If Not IsNull(Me.txtp_lname.Value) Then
    Dim strLastName As String
    strLastName = Me.txtp_lname
    Dim LastName As String
    LastName = Replace(strLastName, "'", "''")
End If

If Not IsNull(Me.txtp_comments.Value) Then
    Dim strComments As String
    strComments = Me.txtp_comments
    Dim Comments As String
    Comments = Replace(strComments, "'", "''")
End If

If Not IsNull(Me.txtp_add1.Value) Then
    Dim stradd1 As String
    stradd1 = Me.txtp_add1
    Dim add1 As String
    add1 = Replace(stradd1, "'", "''")
End If

If Not IsNull(Me.txtp_add2.Value) Then
    Dim stradd2 As String
    stradd2 = Me.txtp_add2
    Dim add2 As String
    add2 = Replace(stradd2, "'", "''")
End If

If Not IsNull(Me.txtp_add3.Value) Then
Dim stradd3 As String
    stradd3 = Me.txtp_add3
    Dim add3 As String
    add3 = Replace(stradd3, "'", "''")
End If

If Not IsNull(Me.txtp_add4.Value) Then
    Dim stradd4 As String
    stradd4 = Me.txtp_add4
    Dim add4 As String
    add4 = Replace(stradd4, "'", "''")
End If

If Not IsNull(Me.txtp_county.Value) Then
    Dim strcounty As String
    strcounty = Me.txtp_county
    Dim county As String
    county = Replace(strcounty, "'", "''")
End If



If Me.txtRemain < 0 Then

MsgBox "'Comments too long, please update'", vbInformation, "Field length"

Else
    
    DoCmd.SetWarnings False
    
    Dim LResponse As Integer
    
    LResponse = MsgBox("'Update details for '" & Me.txtp_full_name & "' ?'", vbYesNo, "Update details")
        
    If LResponse = vbYes Then
        
    DoCmd.RunSQL "UPDATE tblPeople" & _
        " SET p_active=" & Me.chkActive & _
        ", p_fname='" & FirstName & "'" & _
        ", p_lname='" & LastName & "'" & _
        ", p_role='" & Me.txtp_role & "'" & _
        ", p_tel='" & Me.txtp_tel & "'" & _
        ", p_mob='" & Me.txtp_mob & "'" & _
        ", p_email='" & Me.txtp_email & "'" & _
        ", p_comments='" & Comments & "'" & _
        ", p_add1='" & add1 & "'" & _
        ", p_add2='" & add2 & "'" & _
        ", p_add3='" & add3 & "'" & _
        ", p_add4='" & add4 & "'" & _
        ", p_county='" & county & "'" & _
        ", p_postcode='" & Me.txtp_postcode & "'" & _
        ", p_send_letter='" & Me.chkp_send_letter & "'" & _
        " WHERE p_id=" & Me.p_id

        MsgBox "'Person details updated'", vbInformation, "Complete"
        Me.txtp_full_name = Me.p_full_name
        Me.AllowEdits = False
        Me.btnCancel.Enabled = False
        Me.btnSave.Enabled = False
        Me.btnEdit.Enabled = True
        Me.btnClose.Enabled = True
        
        Else
        MsgBox "'Details not updated'", vbInformation, "No update"
            Me.txtp_fname.Value = Me.p_fname
            Me.txtp_lname.Value = Me.p_lname
            Me.txtp_role.Value = Me.p_role
            Me.txtp_tel.Value = Me.p_tel
            Me.txtp_mob.Value = Me.p_mob
            Me.txtp_email.Value = Me.p_email
            Me.txtp_comments.Value = Me.p_comments
            Me.txtp_add1.Value = Me.p_add1
            Me.txtp_add2.Value = Me.p_add2
            Me.txtp_add3.Value = Me.p_add3
            Me.txtp_add4.Value = Me.p_add4
            Me.txtp_county.Value = Me.p_county
            Me.txtp_postcode.Value = Me.p_postcode
            Me.chkActive.Value = Me.p_active
        
    End If
End If

DoCmd.SetWarnings True

End Sub

Upon closing the form I get a write conflict message, giving me options to save record, copy to clipboard or discard changes. This occurs on different forms as well but this is the example I was able to replicate consistently!

There may be other forms open at the time which may reference the PeopleDetails information, would this have an effect?

If needed the close button code is:

If CurrentProject.AllForms("frmMaster").IsLoaded = True Then
    Forms.frmMaster.Requery
    DoCmd.Close acForm, "frmPeopleDetails", acSaveNo
    ElseIf CurrentProject.AllForms("frmCompanyDetails").IsLoaded = True Then
        Forms.frmCompanyDetails.Requery
        DoCmd.Close acForm, "frmPeopleDetails", acSaveNo
    Else
    DoCmd.OpenForm "frmMaster"
    DoCmd.Close acForm, "frmPeopleDetails", acSaveNo
End If

Thanks for any suggestions!

 

[spikepl]

Why do you do it like this ? When a bound form would do all these things without any code?

Besides, ennumerated fields like add2 would straight away make us uspect data normalization issues. Show a screenshot of the Relations window with all tables expanded fully.

 

[adh123]

I cannot allow the end users to have access to the table directly - they may edit a record unknowingly or delete information which is required, which is why the txt boxes are used (the actual table fields are on the form but hidden). If they make a mistake they can press the cancel button and the form reverts back to the old data, or save and it then updates the table.

The reason for the add2 is due to some names and addresses including " ' ", I found that when saving records this was changing the insert/update code on this and other forms.

Edit: I do not remember this write conflict appearing before today. I have gone through previous backups and now it appears in all previous versions...I do not recall changing anything other than updating/adding in new features to the database so why would this start appearing and then appear in older versions of my database?

 

[spikepl]

What is "direct access to tables"? No application should allow users to write or edit data in tables.

But what you wrote does not in any way preclude use of bound forms and the functionality you mentioned can be obtained with bound forms. So again, why not use them? Do you have any other as yet unmentioned requirements?

Your conflicts stems from trying to update the same data twice - this often happens when people have forms that are not entirely unbound and attempt to update the underlying dataset anyway.

 

[adh123]

OK thanks, how would I go about this? (Sorry, as said before my knowledge of this is very limited!). But once I have one set up correctly I can then sort the rest out myself.

 

[spikepl]

Download the attachment from the last post in this thread: http://www.access-programmers.co.uk/forums/showthread.php?p=1410395 and play with it.

There you have total control over whether or not a bound record gets saved. The key is the BeforeUpdate event - the last event fired when an attempt to save the record is made.

 

[adh123]

Thanks, although I cannot fathom how I can adapt the code to work with the existing form...

On your form I am right in thinking that it updates when you move to a new record? My problem is that these are removed in my form deliberately. The frmPeople form is attached as an example (Yes the fields in the screenshot are unbound, I removed these to display the hidden bound fields behind). I can adapt your code to the save button no problem, but using me.undo on the cancel button does not revert back to the original data (I assume it has already updated the table by this point?
I did suggest to the users having 1 button and they can then select Yes for Save, No for cancel but they do not like this and want to stick to the existing layout?


EDIT: I have tried the below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If btnCancel_Click() Is True Then
Me.Undo
Me.AllowEdits = False
Me.btnEdit.Enabled = True
Me.btnClose.Enabled = True
Me.btnSave.Enabled = False
Me.btnCancel.Enabled = False
End If

It works when I have not made changes to a record. But if I click edit and then save (as opposed to cancel) I get "Compile error: Expected Function or variable"?

 

[spikepl]

On your form I am right in thinking that it updates when you move to a new record?

This is default Access behaviour. The entire db shows you how you can control the saving (or not of data). You have so far chosen the opposite path by throwing away the built-in facilities and developing your own saving/updating.

using me.undo on the cancel button does not revert back to the original data

I don't know what you mean without seeing the code and the action. When a record is saved then it is saved. But before that, the update of a record can be cancelled and what the user did input can be undone, so the original values are again displayed in the form. But this is valid for BOUND form and bound controls (for unbound entitites Access has no clue what the values were prior to change)

btnCancel_Click is the name of an event handler, so you cannot use it as a variable as you did in your code modification. I'm not sure what you are trying to do there.

Decide if you want to learn how Access works and how to use the facilities provided. If you do that, then take the code from the link, carefully investigate how it works, and make changes stepwise, for each step checking if it still works.

If you go for unbound forms, you take upon yourself a lot of extra work, and many more possibilities for error (as this thread exemplifies).

 

[adh123]

You have so far chosen the opposite path by throwing away the built-in facilities and developing your own saving/updating.

Not throwing away, purely unaware. As mentioned earlier vba is completely new to me and entirely self taught. At the time of initially putting the forms together this seemed like a sensible approach based on the information I had gathered at the time (and the write conflict messages were not appearing then).

The beforeUpdate code was:

If btnCancel_click() Is True Then
Me.Undo
Me.AllowEdits = False
Me.btnEdit.Enabled = True
Me.btnClose.Enabled = True
Me.btnSave.Enabled = False
Me.btnCancel.Enabled = False
Else

End If

If I press cancel, this works fine. If I save the changes instead, this caused the error in my previous post.

With your comments and another search based on the error I was receiving I have updated the code to the below and it seems to be handling all requirements (editing record, cancel changes, saving changes, no errors on close!). If there is a smarter way of working I am more than happy to learn, just need a bit of guidance to get started is all!

Option Compare Database
Option Explicit
Public bPreventClose As Boolean


Private Sub btnCancel_click()

Me.Undo
Me.AllowEdits = False
Me.btnEdit.Enabled = True
Me.btnClose.Enabled = True
Me.btnSave.Enabled = False
Me.btnCancel.Enabled = False

End Sub

Private Sub btnClose_Click()

bPreventClose = False

If CurrentProject.AllForms("frmMaster").IsLoaded = True Then
    Forms.frmMaster.Requery
    DoCmd.Close acForm, "frmPeopleDetails", acSaveNo
    ElseIf CurrentProject.AllForms("frmCompanyDetails").IsLoaded = True Then
        Forms.frmCompanyDetails.Requery
        DoCmd.Close acForm, "frmPeopleDetails", acSaveNo
    Else
    DoCmd.OpenForm "frmMaster"
    DoCmd.Close acForm, "frmPeopleDetails"
       
End If

End Sub

Private Sub btnEdit_Click()
bPreventClose = False
Me.AllowEdits = True
Me.p_id.Enabled = False
Me.btnEdit.Enabled = False
Me.btnCancel.Enabled = True
Me.btnSave.Enabled = True
Me.btnClose.Enabled = False

End Sub

Private Sub btnSave_Click()

bPreventClose = True
Call DoCmd.RunCommand(acCmdSaveRecord)
bPreventClose = False

Me.AllowEdits = False
Me.btnEdit.Enabled = True
Me.btnCancel.Enabled = False
Me.btnSave.Enabled = False
Me.btnClose.Enabled = True

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If Not bPreventClose Then
    Cancel = True
    strMsg = "Press Save to update record," & _
            vbNewLine & "or Cancel to undo."
    Call MsgBox(strMsg, , "Before Update")


Me.AllowEdits = False
Me.btnEdit.Enabled = True
Me.btnClose.Enabled = True
Me.btnSave.Enabled = False
Me.btnCancel.Enabled = False


End If

End Sub

Private Sub Form_Load()

Me.btnSave.Enabled = False
Me.btnCancel.Enabled = False
Me.AllowEdits = False

End Sub

Private Sub Form_Undo(Cancel As Integer)
    If bPreventClose = True Then
        Cancel = True
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
If bPreventClose = True Then
    Cancel = True
End If
bPreventClose = False

End Sub

 

[spikepl]

If it does what you want - well done!

Once you get the idea how the system ticks, it should make it easier in next round. The essence is to do minimum required to accomplish what one wants. Unbund forms can be justified in soem cases, but the effective sequence is to consider bound forms first, and go for unbund forms only if the bound ones prove unsuitable.

 

[adh123]

I have gone through the rest of the database and where appropriate used your approach, removing all the write conflict messages in the process, so thanks very much!