Getting a write conflict

[ChrisLeicester]

Hi All

i have a main form "RefundShowProdFM" which has a continuous subform "RefundProdTransSub", which lists the 'many' items purchased in a given transaction'

If a customer returned one or more of the items, I have a command button on each item line on the subform called refund which opens a modal form to capture the qty being returned. Once the qty has been entered and confirmed my VBA sets the quantity refunded field, the refund date and refund time field in the continuous subform record and then closes the qty capture form.

All those changes are made to the subform.

I also have one more vba line that changes the 'current status' field in the main form from 'Paid' to 'Part refunded.

heres my code

Private Sub ConfirmQTYBTN_Click()

Dim QTYEntered As Integer         'The number entered in the textbox
Dim OrigQTY As Integer            'The QTY on the original transaction
Dim RefAlreadyQTY As Integer      'The qty already refunded, if any
Dim AvailableQTY As Integer       'The qty left that can be refunded
Dim RefundOK As Integer           'The yes/no to allow refund

    'Set the variables
    QTYEntered = Nz([Forms]![RefundGetQTYFM]![GetQTY], 0)
    OrigQTY = [Forms]![RefundShowSaleFM]![RefundTransProdSub]![ItemQTYpurchased]
    RefAlreadyQTY = Nz([Forms]![RefundShowSaleFM]![RefundTransProdSub]![QTYRefunded], 0)
    AvailableQTY = OrigQTY - RefAlreadyQTY 'original qty less any qty already refunded
    RefundOK = 1
   
        'Validate qty entered
       
        'Look to check not refunding more than originally purchased
        If QTYEntered > OrigQTY Then
        MsgBox "You cannot refund more items than originally purchased", vbCritical, "UNABLE TO REFUND"
        RefundOK = 0
        End If
       
        'check QTY entered is not null or zero
        If QTYEntered = 0 Then
        MsgBox "You must enter a quantity you wish to refund.", vbCritical, "UNABLE TO REFUND"
        RefundOK = 0
        End If
       
            'Checks complete, if still ok to refund then conduct the refund
            [Forms]![RefundShowSaleFM]![RefundTransProdSub]![QTYRefunded] = QTYEntered 'Sets the qty refunded in items record
            [Forms]![RefundShowSaleFM]![RefundTransProdSub]![ItemRefDate] = Date       'Sets the date this refund was completed
            [Forms]![RefundShowSaleFM]![RefundTransProdSub]![ItemRefTime] = Time()     'Sets the time this refund was completed
            [Forms]![RefundShowSaleFM]![CurrentStatus] = 5                             'Sets the sales status to 5 - part refunded
           
            'Finish off the refund item
            DoCmd.Save
            DoCmd.Close acForm, "RefundGetQTYFM", acSaveYes
            [Forms]![RefundShowSaleFM].SetFocus
            DoCmd.Save
           
           
           
           
           
           
           
           
     
       


End Sub

When I test it, the fields update as required but when I go to close the form I get a write error saying another user has made changes to the record and do I want to save or discard my changes,

What is causing this conflict, as I cant see where it is happening.

Thanks in advance

 

[Gasman]

I believe the Save is not doing what you think it is.

DoCmd.Save method (Access)

Office VBA reference topic

learn.microsoft.com

Seems you do it quite often? even after being told what DoCmd.Save does?

 

[DHookom]

You most likely have user entered updates along with code updates. This will cause your error.

 

[ChrisLeicester]

Hi
I now get the save, and changed it but my issue isnt with the save command.

As DHookom has mentioned, the issue is with the pop up changing a value on the main form. if i take that out it runs fine and updates and saves the subform record.

I need to come up with a way to update a field in the main form and not cause the write conflict

 

[theDBguy]

Hi
I now get the save, and changed it but my issue isnt with the save command.

As DHookom has mentioned, the issue is with the pop up changing a value on the main form. if i take that out it runs fine and updates and saves the subform record.

I need to come up with a way to update a field in the main form and not cause the write conflict

I didn't read the entire thread, so this question may have already been answered. How exactly is the popup form changing the data? Is it updating the table directly, or is it simply changing the value in the first form?

 

[Pat Hartman]

I need to come up with a way to update a field in the main form and not cause the write conflict

Easy. Best practice is to ALWAYS save the current record BEFORE opening another form or report. You are conflicting with yourself.
Then since you are potentially updating the visible record on the form that called the popup, always Requery the current form to ensure the update is visible. Put this in the click event that opens the model form on the line AFTER the OpenForm

DoCmd.RunCmd acCommandSaveRecord
OpenForm ...
Me.Requery

 

[ChrisLeicester]

Hi
Thanks

Yes DBguy, the vba is updating fields directly on the subform and one field directly on the main form, which is already open and below the modal form.

Pat
I will separate each form updates and include the refresh