Continuous form - write conflict and records disappear after requery (1 Viewer)

[TommyLiu]

Hi

I am new to access. I have a continuous form to input records (saved records in a table called wTemp) that I want to update to other several tables. I have two textboxes (txtInvoiceID & txtFabID) to show records (JobNo & FabID) from wTemp, and a checkbox (chkFinished) to indicate if the JobNo is fully finished (also saved as Finished(Yes/No) in wTemp). I then want to achieve:
when I untick the checkbox, I can input multiple FabID with the same JobNo. and when I tick the checkbox back, I will only keep one record with only the JobNo, mark as Finished and without a FabID, all other records will be delete.
I put these codes in chkFinished_AfterUpdate, but the write conflict error will always pop up and i have to drop changes to show the changed in the sub. Meanwhile, the deleted record is still there showing as ****delete**** if I use:
Me.Refresh
If I use Me.Requery instead, all records will disappear while they are actually still in the table. It's unusual as requery should always easily reload the recordsource of the form

Is there anyone can help me out? Many thanks.

Here is the code:
If Me.chkFinished.value Then
Dim rs As DAO.Recordset
Dim pointer As Integer

Set db = CurrentDb
'initated recordset obejct
Set rs = db.OpenRecordset("SELECT * FROM wTemp_Update")
pointer = 1
rs.MoveFirst

Do While Not rs.EOF
'Filter the record
If rs!InvoiceID = Me.txtInvoiceID.value And pointer = 1 Then
'change the first record back to "all finished"
rs.Edit
rs!FabID = ""
rs!Finished = True
rs.Update
pointer = pointer + 1
ElseIf rs!InvoiceID = Me.txtInvoiceID.value Then
'delete other record
rs.Delete
pointer = pointer + 1
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Else
Me.txtFabID.Visible = True
Me.lblFabID.Visible = True
Me.txtFabID.Locked = True
Me.ListFabID.Visible = True
End If
DoCmd.RunCommand acCmdSaveRecord

Me.Refresh
'Me.Requery
End If

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[oleronesoftwares]

Welcome aboard @TommyLiu

 

[arnelgp]

is the recordsource of your continuous form also "wTemp_Update" and you are
manipulating it (using recordset) also?

that is a No, No.

it is indeed "write conflict", you have the table open and editing the record
on the form and you are still editing (deleting?) same record on the recordset.

 

[oleronesoftwares]

write conflict error

It seems you are trying to write to the same record, while another action is also been performed on the same record.

Write Conflict dialog box - Microsoft Support

If you and another write to a record another, pessimistic locking grants ownership to the first person who can: Save Record, Copy to Clipboard, or Drop Changes.

support.microsoft.com