Write Conflict when back to main form from subform

[uoghk]

I create a form for vendor bill with subform for bill's product details as show in photo.


When data entry, start from main form, then subform, then click the Save button, it works fine.
But if after subform data entry, I back to main form (e.g. change the issue date), then click the Save button. It shows Write Conflict error.
Do I have something wrong? How to solve it?
Thanks.

[ranman256]

1. Is the sub form key linked in the properties: link master field & link child field?
2. is this app shared by many users on a network? Two people cannot edit the same record.
3 is there vb code in the form to enter data to the field?

[Micron]

If 1) = yes and 2) = no, you are probably the "other user". Likely the problem is in your code. Can you post a zipped copy of the db?

[uoghk]

I am the only one user and master & child keys are linked as below:


This is main form coding:

Dim HasError As Boolean


Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "SELECT * FROM Bill WHERE BillId = " & gblKeyId
    If gblNewKeyId Then
        Me.lblHeader.Caption = "Add Bill"
    Else
        Me.lblHeader.Caption = "Edit Bill"
    End If
    If Me.BillStatus = "Active" Then
        Me.BillStatus.BackColor = gblActiveColor
        Me.btnExit.Caption = "Save and Exit"
        Me.btnSave.Enabled = True
        Me.IssueDate.Locked = False
        Me.VendNo.Locked = False
        Me.VendRefNo.Locked = False
        Me.LicenseNo.Locked = False
        Me.CurCode.Locked = False
        Me.frmBillItem.Locked = False
    Else
        Me.BillStatus.BackColor = gblVoidColor
        Me.btnExit.Caption = "Exit"
        Me.btnSave.Enabled = False
        Me.IssueDate.Locked = True
        Me.VendNo.Locked = True
        Me.VendRefNo.Locked = True
        Me.LicenseNo.Locked = True
        Me.CurCode.Locked = True
        Me.frmBillItem.Locked = True
    End If
    Me.IssueDate.SetFocus
End Sub


Private Sub ExitForm()
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "frmBillList"
End Sub


Private Sub btnVendNo_Click()
    Dim PreviousListForm As Boolean
    PreviousListForm = gblOpenListForm
    gblOpenListForm = False
    DoCmd.OpenForm "frmVendorList", , , , , acDialog, 5
    Me.VendId = Val(Forms!frmVendorList.Tag)
    DoCmd.Close acForm, "frmVendorList"
    gblOpenListForm = PreviousListForm
    If Me.VendId > 0 Then
        Call OpenDatabase("select * from Vendor where VendId=" & Me.VendId)
        Me.VendNo = gblRst!VendNo
        Me.VendName = gblRst!VendName
        Call CloseDatabase
        Me.VendRefNo.SetFocus
    End If
End Sub


Private Sub CheckError()
    HasError = False
    If Nz(Me.VendNo) = "" Then
        Call ErrorBox(Me.lblVendNo.Caption & " is missing. Please enter again.")
        Me.VendNo.SetFocus
        HasError = True
        Exit Sub
    End If
    If Nz(Me.VendRefNo) = "" Then
        Call ErrorBox(Me.lblVendRefNo.Caption & " is missing. Please enter again.")
        Me.VendRefNo.SetFocus
        HasError = True
        Exit Sub
    End If
End Sub


Private Sub SaveRecord()
    DoCmd.RunSQL ("update logfile set totalamt=" & Me.TotalAmt & " where billid=" & Me.BillId)
    Call NoticeBox(Me.lblBillNo.Caption & " " & Me.BillNo & " has been saved successfully.")
End Sub


Private Sub btnSave_Click()
    Dim DocNo As String
    Call CheckError
    If HasError Then
        Exit Sub
    End If
    Call SaveRecord
    DocNo = NewDocumentNo("Bill")
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me.BillNo = DocNo
    Me.IssueDate = Now()
    Me.BillStatus = "Active"
    Me.LastUser = "JOE"
    Me.CurCode = "HKD"
    Me.Refresh
    gblKeyId = Me.BillId
    Call Form_Open(0)
End Sub


Private Sub btnExit_Click()
    If Me.btnExit.Caption = "Save and Exit" Then
        Call CheckError
        If HasError Then
            Exit Sub
        End If
        Call SaveRecord
    End If
    Call ExitForm
End Sub

This is subform coding:

Private Sub btnFind_Click()
    Dim PreviousListForm As Boolean
    PreviousListForm = gblOpenListForm
    gblOpenListForm = False
    DoCmd.OpenForm "frmProductList", , , , , acDialog, 5
    Me.ProdId = Val(Forms!frmProductList.Tag)
    DoCmd.Close acForm, "frmProductList"
    gblOpenListForm = PreviousListForm
    If Me.ProdId > 0 Then
        Call OpenDatabase("select * from Product where ProdId=" & Me.ProdId)
        Me.ProdNo = gblRst!ProdNo
        Call CloseDatabase
        Me.Price.SetFocus
    End If
End Sub


Private Sub btnRemove_Click()
    If ConfirmBox("Do you want to DELETE " & Me.lblProdNo.Caption & " " & Me.ProdNo & " ?") Then
        DoCmd.RunCommand acCmdDeleteRecord
    End If
End Sub


Private Sub Price_AfterUpdate()
    Me.Amt = Nz(Me.Price, 0) * Nz(Me.Qty, 0)
End Sub


Private Sub Qty_AfterUpdate()
    Me.Amt = Nz(Me.Price, 0) * Nz(Me.Qty, 0)
End Sub


Private Sub ProdNo_AfterUpdate()
    Me.ProdId = Me.ProdNo.Column(3)
    Me.Price.SetFocus
End Sub

[kd2017]

I for one can't go through your code at the moment. But I'd suggest still uploading the db file so we can execute and step through the code.

One more suggestion would be to have a textbox on the parent form for billid and rename it something like txtBillID, then link the subform to the textbox instead of the billid field directly. It's important to rename the textbox something other than the field name here. The child fields should remain as foreign key field name. So something like:
Link master fields: txtBillId
Link child fields: BillId

http://www.fmsinc.com/microsoftaccess/forms/subform/master-link-fields.asp

[orange]

I agree with KD2017 --post a database showing the issue and instructions to reproduce the issue.
It would also be helpful if you would clearly describe your expectation. That is - what would be the result you need.

[uoghk]

I try KD2017 suggestion, but it still show the write conflict error.
That is, after entering data in subform, back to masterform to change some data, then press the Save button, it shows the error.
I currently use Linked Table to backend SQL database.
I convert the Linked Tables to Local Tables.
I test again, it can save the record without error.
What wrong to my database or masterform/subform setting?
How can I upload it for you to test?
Thanks KD2017, orange and anyone viewed my post.

[uoghk]

I attach masterform & subform setting for reference.

[Micron]

How can I upload it for you to test?

Follow the link at the top of the forum page - How to attach files.

[Gicu]

You use

Call OpenDatabase("Select...")

twice so it looks like it is not the built in DbEngine.OpenDatabase method. What is it, can you show us the code? And also maybe your module with the global variables.

If you say the warning shows up only with SQL tables and not with Access ones the issue might be related to the absence of a TimeStamp field in the back-end table. I used to get this exact issue with older versions of SQL server (2008) and ODBC drivers, which required each SQL table to have a TimeStamp field. Then the field needed to be included in the recordsource of the form. While you are at it check to make sure all your Bit fields in SQL (the equivalent of the Yes\No fields in Access) are set to Allow Null=False and set their Default property accordingly (either 0 or 1). If you do find some that are set to allow Nulls then you also need to run Update queries to Update them to either 0 or 1 depending on your logic\needs.

Here is some reading on this:
https://stackoverflow.com/questions/...write-conflict
https://stackoverflow.com/questions/...ing-sql-server

Cheers,

[uoghk]

Thanks Gicu.
I also found an article https://answers.microsoft.com/en-us/...8-2a6f9305f25f yesterday.
According to the suggestion, I add a timestamp column in SQL database tables and re-link them again. It works now.