Solving Write Conflict

[kirkm]

My code is

Private Sub txtAlbumCat_AfterUpdate()
    Dim sql As String
    If Nz(txtAlbumCat) > "" And Nz(txtLPRelease) > "" Then
        sql = "UPDATE CDTracks SET CDTracks.AlbumCat = " & Chr$(34) & txtAlbumCat & Chr$(34) & " WHERE CDTracks.TCat = " & ThisDisk() & " AND CDTracks.LPRelease =" & Chr$(34) & txtLPRelease & Chr$(34) & ";"
        CurrentDb.Execute (sql)
    End If
End Sub

This brings up a "write conflict" dialog (which I don't understand).
Clicking any of the three options or closing the dialog sees it runs as intended.

Why it is showing "Write Conflict" and can I stop it doing that ? Thanks.

 

[pbaldy]

Is the form perhaps bound to that table, and the controls to fields?

 

[kirkm]

There is no write conflict really, I think this is an Access bug. In googling for how to suppress "Write Conflict" message I found many people asking just that, and the solution is to add 'Me.Refresh" ahead of the routine. And it works.

 

[kirkm]

Yes Paul, both are.

 

[pbaldy]

The code is simply duplicating what the form is already doing. Comment out the code and see what happens.

 

[Pat Hartman]

If the form is bound to the table (or a query of that table) that the update query is updating, YOU are causing the conflict. In te AfterUpdate event of a control, the form is still dirty since the record has not been saved. So you are trying to update with the query a record that is already being changed by the form.

Me.Refresh as a SIDE EFFECT, saves the current record. That is why Me.Refresh seems to solve the problem but in reality, it is your design that is causing the problem and that is what you should be fixing. Bound forms automatically save dirty records. There is no need to use an update query to do something the form is already doing.

PS - NEVER use Me.Refresh, Me.Requery, or Me.Recalc to save a record. Always use the explicit save command. Every other method saves as a side effect but in reality is intended to do something else and you will find that that something else is frequently annoying when what you actually wanted was to save.

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

 

[kirkm]

I don't quite follow you Paul. If I comment that out won't the After Update event just do nothing?
Is is working as intended after adding the refresh. Although why it thought there was a write conflict to begin with....

 

[kirkm]

Thanks Pat. I had to read your first sentence many times, I think I have it now. Yes, the one record I'm entering manually will update as you describe, but the intent was to update other Records (that need it) to that same update.
I did try to skip that first one in code, but it didn't seem to work.
But I'll change it right away from Refresh to Save.

 

[arnelgp]

You need to add autonumber field on table and add it also in the form.
Change the query to include the autonumber:



Private Sub txtAlbumCat_AfterUpdate()
Dim sql As String
If Nz(txtAlbumCat) > "" And Nz(txtLPRelease) > "" Then
sql = "UPDATE CDTracks SET CDTracks.AlbumCat = " & Chr$(34) & txtAlbumCat & Chr$(34) & " WHERE CDTracks.TCat = " & ThisDisk() & " AND CDTracks.LPRelease =" & Chr$(34) & txtLPRelease & Chr$(34) & " and autonumberfield<> " & me.autonumbertextbox & ";"
CurrentDb.Execute (sql)
End If
End Sub

 

[Pat Hartman]

If changing one record causes you to have to change other records, you have a design flaw. The column you are updating is in the wrong table. It should be in the parent table not the child table.

If you can give us more details we can help resolve the issue of where the column belongs. Post the schema and try to explain what you need to do.

 

[kirkm]

Arnelgp thanks for that suggestion, it is an alternative to what is now working, yes?
Pat, changing one record that changes others is what I want it to do. Saves me typing!
I don't actually have both parent & child tables.

 

[Pat Hartman]

Your 1 table probably needs to be 2 tables so all the data that occurs once goes in the parent table and the data that is different for each row goes into the child table.

It is always best to resolve design issues rather than work around them.

 

[Galaxiom]

I would strongly recommend you post your data structure here before you go any further. Having it checked out could save you a lot of grief in the long run.

Album should be a separate table with an ID. The ID connects the Track to the Album. The album name occurs only in the Album table so you don't update it on all the tracks.

I would probably relate Album to Track in a Many-to-Many relationship so that the same track can appear on multiple albums. Versions of a song aught to be considered too.

 

[kirkm]

Thanks for the suggestions. You are right of course, but this is over 20 yaers old, I didn't originally write it, but I know my way around it now - and it does what's intended very well.

 

[arnelgp]

Yrs, by adding the autonumber on your query guarantee that you dont update the record you are currently editing that is causing the write cobflict.