File / Record Lock Error !!

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.Execute 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_DATE = " & _
IIf(IsNull(Me.Txt_Date),
"Null", DMY(Me.Txt_Date)) & ", " & _
"JVTable.RcdFm_PdTo = " & _
>
IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_PdTo &
"""") & ", " & _
"JVTable.Chq_No = " & _
IIf(IsNull(Me.txt_Chq_No),
"Null", """" & Me.txt_Chq_No & """") & ", " & _
"JVTable.Chq_Date = " & _
>
IIf(IsNull(Me.txt_Chq_Date), "Null", DMY(Me.txt_Chq_Date)) & ", " & _
"JVTable.Bank = " & _
IIf(IsNull(Me.txt_Bank),
"Null", """" & Me.txt_Bank & """") & ", " & _
"JVTable.Settled_Bill_Nos = " & _
>
IIf(IsNull(Me.txt_Settled_Bill_Nos), "Null", """" &
Me.txt_Settled_Bill_Nos & """")
>
CurrentDb.Execute StrSql, dbFailOnError
>
End Sub
>
>
..........................................
>
>
Private Sub Cmd_Delete_Click()
On Error GoTo Err_Cmd_Delete_Click
>
Dim stn As Byte, strControl As String
strControl = Screen.PreviousControl.Name
'MsgBox strControl
Application.Echo False
>
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings 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_Click 'Re-Number the sub_tran_no field if
any record is DELETED
>
Me.Recordset.FindFirst "[sub_tran_no]=" & stn
If Me.Recordset.NoMatch Then
Me.Recordset.MoveLast
End If
Me.Controls(strControl).SetFocus
Application.Echo 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(strControl).SetFocus
Application.Echo True
Else
MsgBox Err.Description & " " & Err.Number
End If
Resume Exit_Cmd_Delete_Click
>
End Sub
>
..........................................
>
>

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.Execute 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_DATE = " & _
IIf(IsNull(Me.Txt_Date),
"Null", DMY(Me.Txt_Date)) & ", " & _
"JVTable.RcdFm_PdTo = " & _
>
IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_PdTo &
"""") & ", " & _
"JVTable.Chq_No = " & _
IIf(IsNull(Me.txt_Chq_No),
"Null", """" & Me.txt_Chq_No & """") & ", " & _
"JVTable.Chq_Date = " & _
>
IIf(IsNull(Me.txt_Chq_Date), "Null", DMY(Me.txt_Chq_Date)) & ", " & _
"JVTable.Bank = " & _
IIf(IsNull(Me.txt_Bank),
"Null", """" & Me.txt_Bank & """") & ", " & _
"JVTable.Settled_Bill_Nos = " & _
>
IIf(IsNull(Me.txt_Settled_Bill_Nos), "Null", """" &
Me.txt_Settled_Bill_Nos & """")
>
CurrentDb.Execute StrSql, dbFailOnError
>
End Sub
>
>
..........................................
>
>
Private Sub Cmd_Delete_Click()
On Error GoTo Err_Cmd_Delete_Click
>
Dim stn As Byte, strControl As String
strControl = Screen.PreviousControl.Name
'MsgBox strControl
Application.Echo False
>
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings 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_Click 'Re-Number the sub_tran_no field if
any record is DELETED
>
Me.Recordset.FindFirst "[sub_tran_no]=" & stn
If Me.Recordset.NoMatch Then
Me.Recordset.MoveLast
End If
Me.Controls(strControl).SetFocus
Application.Echo 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(strControl).SetFocus
Application.Echo True
Else
MsgBox Err.Description & " " & Err.Number
End If
Resume Exit_Cmd_Delete_Click
>
End Sub
>
..........................................
>
>

By the way, if you search through the table and focus in on the memo field,
look for #error in the table. This will indicate the locked/bad record.
>
<prakashwadhw...@gmail.comwrote in message
>
news:8f0cdda3-f6d6-45fe-9da8-32d40a54b965@e25g2000prg.googlegroups.com...
>>>>>>>>>>>>>>>>>>>>>>>>>>

On Mar 4, 3:27 am, "ARC" <PCES...@PCESoft.invalidwrote:
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
I don't have any memo fields in my table. Hope you have any other
ideas ??
>
Best Rgds,
Prakash.

On Mar 4, 9:41 am, prakashwadhw...@gmail.com wrote:
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
While stepping through the execution I realized what's causing the
error.
When I click the Delete button the foll statement :
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
>
causes the error:
The command or action 'SelectRecord' isn't available now. 2046
>
At this point my Access Application effectively freezes only allowing
me to shut down the App & Access.
>
I hope someone can point out where I'm going wrong.
>
Best Rgds,
Prakash.

causes the error:
The command or action 'SelectRecord' isn't available now. 2046