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.

 

[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

 

[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.

 

[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.