Results 1 to 11 of 11
  1. #1
    uoghk is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    72

    Write Conflict when back to main form from subform


    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.
    Click image for larger version. 

Name:	writeconflict.png 
Views:	26 
Size:	50.2 KB 
ID:	47039

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,188
    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?

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,644
    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?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    uoghk is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    72
    I am the only one user and master & child keys are linked as below:
    Click image for larger version. 

Name:	writeconflict1.png 
Views:	19 
Size:	8.0 KB 
ID:	47049

    This is main form coding:
    Code:
    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:
    Code:
    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

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    984
    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

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,070
    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.

  7. #7
    uoghk is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    72
    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.

  8. #8
    uoghk is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    72
    I attach masterform & subform setting for reference.
    Click image for larger version. 

Name:	form.png 
Views:	18 
Size:	83.0 KB 
ID:	47051

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,644
    How can I upload it for you to test?
    Follow the link at the top of the forum page - How to attach files.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,174
    You use
    Code:
    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,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    uoghk is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    72
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Write Conflict By Checkbox In Continuous Form
    By Wappervliegje in forum Access
    Replies: 9
    Last Post: 11-14-2017, 04:49 AM
  2. Form write conflict
    By loop123 in forum Forms
    Replies: 4
    Last Post: 02-05-2017, 11:28 AM
  3. sub form write conflict error
    By Slush in forum Forms
    Replies: 4
    Last Post: 09-23-2016, 02:02 PM
  4. Write Conflict in subform
    By RayMilhon in forum Access
    Replies: 11
    Last Post: 09-16-2016, 01:50 PM
  5. Replies: 3
    Last Post: 08-06-2012, 03:29 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums