[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Write concflict in MS Access

Posted on 2013-05-15
10
Medium Priority
?
304 Views
Last Modified: 2013-12-20
I have a form in MS Access with  subform that has radio buttons and textboxes. When I try to modify the subform by changing the radio button or entering text into the textbox, it is displaying the "write conflict" error at the following line in one of the functions.

If Me.Dirty Then
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 End If

Frankly, I have no idea about how it is validating. Please let me know what other details I need to provide.
0
Comment
Question by:Angel02
  • 5
  • 4
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39168845
Try adding the following code to the  subform control's Enter event

Docmd.runcommand  acCmdSaveRecord
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39168938
Usually that means you have some other event or code that started changing the record as well.  You will need to evaluated all code you have attached to the form.

ET
0
 

Author Comment

by:Angel02
ID: 39169335
@mbizup
I added it but it did not change anything.

I first click on a "edit" button which loads the records (rows) that are to be edited. The fields values of the rows are populated into textboxes and radiobuttons.  Below is the function that does that:

Private Sub cmdEditRt_Click()
Dim strSource As String
'Steps
'1.Check if not exist
Dim rte As Integer
Dim Dt As Date
Dim flgOK As Boolean
   strSource = "SELECT tbl1.* FROM tbl1 " _
    & " WHERE (((Rt)=" & 0 & "))" _
    & " AND location= " & Location & ";"

Me.RecordSource = strSource

If Me.txtRt <> "" And Me.txtDate <> "" Then
    rte = txtRt
    Dt = txtDate
'Check if valid RT
     Select Case RtStatus(rte, Dt)
     
      Case 0
            MsgBox "not found!", , "Validation error"
      Case 1, 2
            flgOK = True
      Case 3
             MsgBox "started!", , "Validation error"
     Case Else
            If AppMode <> 4 Then
                MsgBox "already processed !", , "Validation error"
            Else 'just for diadnostic
                flgOK = True
            End If
     End Select
     If flgOK Then
         strSource = "SELECT tbl1.* FROM tbl1 " _
        & " WHERE ((Rt_Number)=" & rte _
        & " AND (finish_date)=#" & Format(Dt, "yyyy-mm-dd") & "#)" _
        & " AND location= " & Location & ";"

                Me.RecordSource = strSource
       End If
 
 Else ' rt is missing
        MsgBox "Please provide rt #!", , "Validation error"
 End If


End Sub

'********************

I then try to modify the textbox to update the row in the table. It is when I get the write conflict. The function that is called when I try to edit is below:
Public Sub ChangePayment()
If Me.fraPaymentType.Value = 0 Then
    Me.AmtTender_pay_cash = ""
    Me.AmtTender_pay_cash.SetFocus
    Me.AmtTender_pay_check = ""
    Me.AmtTender_pay_credit = ""
   
ElseIf Me.fraPaymentType.Value = 1 Then
    Me.AmtTender_pay_cash = ""
     Me.AmtTender_pay_check = ""
    Me.AmtTender_pay_credit = Me.SubTotal_forStop
ElseIf Me.fraPaymentType.Value = 2 Then
    Me.AmtTender_pay_cash = Null
    Me.AmtTender_pay_check = Me.SubTotal_forStop
    Me.AmtTender_pay_credit = ""
End If
If Me.Dirty Then
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'FAILS HERE End If

End Sub

Is the cmdEditRt_Click function loacking the record? How do I check and if so what do I do to avoid?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39170260
I believe you are getting that error because Access sees your manual edits and your code edits as two separate 'people' editing the record at the same time.

The key to resolving this type of error is to save the record between different types of edits.  The code I suggested earlier will force-save a record (so will me.Refresh as shown below), but try placing it immediately before your edit code instead.  This is assuming that the textbox you are editing is located on the same form (or subform) as your code.

Public Sub ChangePayment()

Me.refresh

If Me.fraPaymentType.Value = 0 Then
    Me.AmtTender_pay_cash = ""
    Me.AmtTender_pay_cash.SetFocus
    Me.AmtTender_pay_check = ""
    Me.AmtTender_pay_credit = ""
    
ElseIf Me.fraPaymentType.Value = 1 Then
    Me.AmtTender_pay_cash = ""
     Me.AmtTender_pay_check = ""
    Me.AmtTender_pay_credit = Me.SubTotal_forStop
ElseIf Me.fraPaymentType.Value = 2 Then
    Me.AmtTender_pay_cash = Null
    Me.AmtTender_pay_check = Me.SubTotal_forStop
    Me.AmtTender_pay_credit = ""
End If

docmd.runcommand accmdsaverecord

End Sub

Open in new window

0
 

Author Comment

by:Angel02
ID: 39175870
I placed Me.refresh immediately before the edit code as you mentioned. The runtime error now occurs at Me.Refresh in ChangePayment(). So this means it has to be saved even before getting to this function? I have Docmd.runcommand  acCmdSaveRecord at  subform Enter() like you suggested. So the record is being accessed somewhere in between?
0
 

Author Comment

by:Angel02
ID: 39181204
I wanted to add that this application used to work perfectly last week. I changed the connection string and it started throwing the error. I then changed the connection string back to the older one but it still would throw the write conflict.
What could be the cause?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39181222
Is your data stored in Access, SQL Server, or something else?
0
 

Author Comment

by:Angel02
ID: 39181444
Data is stored in MySQL.
It is accessed via Access.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 2000 total points
ID: 39181593
Try this -

For any bit fields in the table related to the form(s) where you are having this issue:

1.  Ensure that NULLS are not allowed
2.  Ensure that any existing nulls are updated with FALSE.
0
 

Author Comment

by:Angel02
ID: 39181626
I checked all the four tables related to the form and apparently none of them have even one bit field.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question