The Delete Event/Proc & "Save_Fields_In _Form_Header" Event/Proc in
my form were working perfectly.
However, after I added a call to the "Save_Fields_In _Form_Header"
Event/Proc in the "Current" Event of my form, and I now try to Delete
a record, I get ... Run-Time Error 3218 - Could not Update; Currently
Locked. My Access application then effectively freezes forcing me to
shut Access down & re-start.
The record DOES get deleted successfully.
What's happening is as follows:
1) I click the Delete command button on the form
2) The record gets deleted
3) The Current event gets fired & my Sub
"Save_Fields_In _Form_Header" gets called.
4) During the SQL update in the above Sub:
"Save_Fields_In _Form_Header", the above-mentioned error is triggered
on the foll statement: CurrentDb.Execu te StrSql, dbFailOnError
I'm presuming here, the record is still locked by the "Delete" Event &
that's why a SQL UPDATE cannot lock the file.
I'm posting my 2 Subs here hoping someone can point out any mistakes /
workarounds.
Thx & Best Rgds,
Prakash.
----------------------------
Private Sub Save_Fields_In_ Form_Header()
Dim StrSql As String
StrSql = "UPDATE JVTable SET JVTable.INV_DAT E = " & _
IIf(IsNull(Me.T xt_Date),
"Null", DMY(Me.Txt_Date )) & ", " & _
"JVTable.RcdFm_ PdTo = " & _
IIf(IsNull(Me.t xt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_Pd To &
"""") & ", " & _
"JVTable.Ch q_No = " & _
IIf(IsNull(Me.t xt_Chq_No),
"Null", """" & Me.txt_Chq_No & """") & ", " & _
"JVTable.Chq_Da te = " & _
IIf(IsNull(Me.t xt_Chq_Date), "Null", DMY(Me.txt_Chq_ Date)) & ", " & _
"JVTable.Ba nk = " & _
IIf(IsNull(Me.t xt_Bank),
"Null", """" & Me.txt_Bank & """") & ", " & _
"JVTable.Settle d_Bill_Nos = " & _
IIf(IsNull(Me.t xt_Settled_Bill _Nos), "Null", """" &
Me.txt_Settled_ Bill_Nos & """")
CurrentDb.Execu te StrSql, dbFailOnError
End Sub
............... ............... .............
Private Sub Cmd_Delete_Clic k()
On Error GoTo Err_Cmd_Delete_ Click
Dim stn As Byte, strControl As String
strControl = Screen.Previous Control.Name
'MsgBox strControl
Application.Ech o False
DoCmd.SetWarnin gs False
DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnin gs True
Me.DrTotal = DSum("DEBIT", "JVTable") ' After deleting a Record
Totals shd be re-calculated
Me.CrTotal = DSum("CREDIT", "JVTable")
stn = Sub_Tran_No - 1 'Saving Current Record Pointer
Position
Call Cmd_ReNumber_Cl ick 'Re-Number the sub_tran_no field if
any record is DELETED
Me.Recordset.Fi ndFirst "[sub_tran_no]=" & stn
If Me.Recordset.No Match Then
Me.Recordset.Mo veLast
End If
Me.Controls(str Control).SetFoc us
Application.Ech o True
Exit_Cmd_Delete _Click:
Exit Sub
Err_Cmd_Delete_ Click:
If Err.Number = 3021 Then 'user tries to delete an EOF or BOF
record
'MsgBox "No More Records To Delete !", vbOKOnly +
vbInformation, "Message... "
Me.Controls(str Control).SetFoc us
Application.Ech o True
Else
MsgBox Err.Description & " " & Err.Number
End If
Resume Exit_Cmd_Delete _Click
End Sub
............... ............... .............
my form were working perfectly.
However, after I added a call to the "Save_Fields_In _Form_Header"
Event/Proc in the "Current" Event of my form, and I now try to Delete
a record, I get ... Run-Time Error 3218 - Could not Update; Currently
Locked. My Access application then effectively freezes forcing me to
shut Access down & re-start.
The record DOES get deleted successfully.
What's happening is as follows:
1) I click the Delete command button on the form
2) The record gets deleted
3) The Current event gets fired & my Sub
"Save_Fields_In _Form_Header" gets called.
4) During the SQL update in the above Sub:
"Save_Fields_In _Form_Header", the above-mentioned error is triggered
on the foll statement: CurrentDb.Execu te StrSql, dbFailOnError
I'm presuming here, the record is still locked by the "Delete" Event &
that's why a SQL UPDATE cannot lock the file.
I'm posting my 2 Subs here hoping someone can point out any mistakes /
workarounds.
Thx & Best Rgds,
Prakash.
----------------------------
Private Sub Save_Fields_In_ Form_Header()
Dim StrSql As String
StrSql = "UPDATE JVTable SET JVTable.INV_DAT E = " & _
IIf(IsNull(Me.T xt_Date),
"Null", DMY(Me.Txt_Date )) & ", " & _
"JVTable.RcdFm_ PdTo = " & _
IIf(IsNull(Me.t xt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_Pd To &
"""") & ", " & _
"JVTable.Ch q_No = " & _
IIf(IsNull(Me.t xt_Chq_No),
"Null", """" & Me.txt_Chq_No & """") & ", " & _
"JVTable.Chq_Da te = " & _
IIf(IsNull(Me.t xt_Chq_Date), "Null", DMY(Me.txt_Chq_ Date)) & ", " & _
"JVTable.Ba nk = " & _
IIf(IsNull(Me.t xt_Bank),
"Null", """" & Me.txt_Bank & """") & ", " & _
"JVTable.Settle d_Bill_Nos = " & _
IIf(IsNull(Me.t xt_Settled_Bill _Nos), "Null", """" &
Me.txt_Settled_ Bill_Nos & """")
CurrentDb.Execu te StrSql, dbFailOnError
End Sub
............... ............... .............
Private Sub Cmd_Delete_Clic k()
On Error GoTo Err_Cmd_Delete_ Click
Dim stn As Byte, strControl As String
strControl = Screen.Previous Control.Name
'MsgBox strControl
Application.Ech o False
DoCmd.SetWarnin gs False
DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnin gs True
Me.DrTotal = DSum("DEBIT", "JVTable") ' After deleting a Record
Totals shd be re-calculated
Me.CrTotal = DSum("CREDIT", "JVTable")
stn = Sub_Tran_No - 1 'Saving Current Record Pointer
Position
Call Cmd_ReNumber_Cl ick 'Re-Number the sub_tran_no field if
any record is DELETED
Me.Recordset.Fi ndFirst "[sub_tran_no]=" & stn
If Me.Recordset.No Match Then
Me.Recordset.Mo veLast
End If
Me.Controls(str Control).SetFoc us
Application.Ech o True
Exit_Cmd_Delete _Click:
Exit Sub
Err_Cmd_Delete_ Click:
If Err.Number = 3021 Then 'user tries to delete an EOF or BOF
record
'MsgBox "No More Records To Delete !", vbOKOnly +
vbInformation, "Message... "
Me.Controls(str Control).SetFoc us
Application.Ech o True
Else
MsgBox Err.Description & " " & Err.Number
End If
Resume Exit_Cmd_Delete _Click
End Sub
............... ............... .............
Comment