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