Write conflict

[MrMode]

I have a form bound to a MySQL linked table, the majority of fields are text fields populated on load via DLookup and there are 6 combo boxes that give the current value of the MySQL table allowing the user to make changes to the record.

This has been working without issues, however, now any changes made bring up the Write Conflict if the user tries to move to another record with Save Record greyed out...

It is not my MySQL database so I was wondering if there was anything I could check or do my end. I have a duplicate of the database that works with local tables only and there are no issues at all with the form.

I know I am being vague, but i have no idea why it is giving the user this issue all of a sudden.

Any and all help greatly appreciated.

 

[dhookom]

Why are you mixing a bound form with populating fields with DLookup()? Are you using code with DLookup()?

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Hmm, good point, I don't remember. Yes the dlookups use code, I guess I can code the combo boxes and make the form unbound. I will let you know if it works.

 

[MrMode]

This form is used to update records in a table via combo boxes which are populated with Primary Key which is 0cm long (hidden from the user) and a corresponding Text field from several other tables.

The DLookUps are used to put information in front of the user for reference from another table linked via CompanyID.

The reason it is bound to a table is so that the combo boxes write directly to the table and the CompanyID is available for the DLookUp text fields. It has worked fine for months and works on some records, but a specific group of records keep giving me the Write Conflict error.

One other form is open that reads from the same table to populate lists and allow navigation via DoubleClick events, again this has been working without issue for months...

How do I track down the issue, I have looked at a couple of things but I am lost as to where to start?

Thanks for being patient

 

[dhookom]

I suspect the records are being edited either by being bound to more than one open form or through code. Do you display the record selector in the forms?

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

No, I turn them off OnLoad.

I have tried closing the list based navigation form once this form opens, so the only thing open is this form... head scratcher

 

[dhookom]

Are there any yes/no (bit) fields in the table(s)? If so, they need to have a default value set.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

I will check and come back to you, thanks!

 

[MrMode]

Hmm

The MySQL table has the Y/N fields incorrectly set up and without default values. I have asked the owner to change them, then I can see if the problem goes away.

Fingers crossed!

 

[MrMode]

The guys who own and manage the MySQL tables are asking if I want Y/N fields to be bit fields or boolean.

Shall I ask them to make them bit fields?

Many thanks

 

[dhookom]

Why not make them tiny int.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Hmm, I asked the owner of the MySQL table to remove the Y/N fields completely, but there is still the Write Conflict error.

I don't where else to look for this issue?

 

[dhookom]

Did you relink the MySQL tables?
Are you still using a combination of bound and unbound controls?
Can you share your code?

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Yes, I relinked the MySQL tables and still using a combination of bound and unbound controls.

This is the code for the form that allows the user to double click on a company and open the form:

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo Err_Form_Load

    Me.List0.RowSource = _
        "SELECT Distinct SCCtblAssessedData.AssessmentNumber, tblMapToBeAssessed.AssessmentStage " & _
        "FROM SCCtblAssessedData INNER JOIN tblMapToBeAssessed ON SCCtblAssessedData.AssessmentNumber = tblMapToBeAssessed.tblMapToBeAssessedID " & _
        "ORDER BY tblMapToBeAssessed.AssessmentStage;"

    With Me
    .NavigationButtons = False
    End With

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
    
End Sub


Private Sub List0_AfterUpdate()
On Error GoTo Err_List0_AfterUpdate

  Me.List2.RowSource = _
        "SELECT SCCtblAssessedData.AssessmentNumber,PortalData.[Company ID], PortalData.[Company Name], PortalData.[Start Date], PortalData.Status, PortalData.[Company Type], tblMapToBeAssessed.AssessmentStage " & _
        "FROM (SCCtblAssessedData INNER JOIN tblMapToBeAssessed ON SCCtblAssessedData.AssessmentNumber = tblMapToBeAssessed.tblMapToBeAssessedID) INNER JOIN PortalData ON SCCtblAssessedData.[Company ID] = PortalData.[Company ID] " & _
        "WHERE SCCtblAssessedData.AssessmentNumber = " & Me.List0 & _
        "order by PortalData.[Company Name];"
        
    Me.Label3.Caption = "There are " & Me.List2.ListCount & " companies that are " & Me.List0.Column(1)

Exit_List0_AfterUpdate:
    Exit Sub

Err_List0_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_List0_AfterUpdate
    
End Sub

Private Sub List2_DblClick(Cancel As Integer)
On Error GoTo Err_List2_DblClick

 DoCmd.OpenForm "frmVerification2CompanyInfo", acNormal, "", "[Company ID]=" & Me.List2.Column(1), , acNormal
 
 
Exit_List2_DblClick:
    Exit Sub

Err_List2_DblClick:
    MsgBox Err.Description
    Resume Exit_List2_DblClick

End Sub

This is all the code on the form where I am having the issues, the combo boxes are bound but not populated through code, all the unbound text boxes are populated via the code below:

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo Err_Form_Load

'This shuts the record navigators off as they could confuse users into believing that the form is based on a table.

    With Me
    .NavigationButtons = False
    End With

'These are the various text boxes on the form
    
     Me.Text83.Value = DLookup("[Company Name]", "PortalData", "[Company ID] = " & Me.Company_ID)

    Me.Text85.Value = DLookup("[Postcode]", "PortalData", "[Company ID] = " & Me.Company_ID)

    Me.Text89.Value = DLookup("[Number of Employees]", "PortalData", "[Company ID] = " & Me.Company_ID)
    
    Me.Text117.Value = DLookup("[Company Type]", "PortalData", "[Company ID] = " & Me.Company_ID)
     
    Me.Text123.Value = DLookup("[AssessmentNumber]", "SCCtblAssessedData", "[Company ID] = " & Me.Company_ID)
    


Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
    
End Sub



'------------------------------------------------------------
' Command0_Click
'
'------------------------------------------------------------
Private Sub Command0_Click()
On Error GoTo Command0_Click_Err

    DoCmd.Close , ""


Command0_Click_Exit:
    Exit Sub

Command0_Click_Err:
    MsgBox Error$
    Resume Command0_Click_Exit

End Sub

 

[dhookom]

Is the form bound to PortalData? Are the text boxes bound to fields from PortalData?

I would not mix bound and unbound.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

The form is bound to SCCtblAssessedData, the unbound text boxes are used to provide additional information from PortalData.

How would I get around mixing bound and unbound? The SCCtblAssessedData only has a few columns with numbers in it (foreign keys), PortalData has text fields that describe the companies to the user, but it is SCCtblAssessedData that I need the user to be able to update via the combo boxes.

 

[dhookom]

Are Text83, Text85, Text89, Text117, and Text123 bound to fields from SCCtblAssessedData?

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

they are bound to portaldata, the combo boxes aee linked to scctblassesseddata as that is the one that needs changing.

 

[dhookom]

You stated "The form is bound to SCCtblAssessedData" so I don't understand how "they are bound to portaldata". "Bound" refers to the Control Source of a text box for a specific Column or Field. "Bound" for the form refers to the Record Source.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Sorry, to clarify:

The form Record Source is SCCtblAssessedData (it is bound to SCCtblAssessedData).

The Text boxes use the [Company ID] key from SCCtblAssessedData to look up values in the PortalData table. The text boxes are unbound.

There are 6 combo boxes on the form. Their Control Sources are SCCtblAssessedData fields.