Error 3167 *Record is deleted* (A2k2, SP2, DAO)

Topic

New Reply

Hi All,
I use the following code on a command button to delete records on a subform (sbfrmProbeData1point1) from the parent form (frmIGICaseNumberData):

‘———————————————————————————————————
strMsg = “You will delete any data you have entered into the Probe Data Field!”
strTitle = “Do you want to delete this data?”
intAnswer = MsgBox(strMsg, vbExclamation + vbOKCancel, strTitle)
If intAnswer = vbOK Then
strSQLDelete = “DELETE * FROM qryProbeData1point1 ”
strSQLDelete = strSQLDelete & “WHERE LabDataPKID = ” & Me!lngLabDataPKID

DoCmd.SetWarnings False
DoCmd.RunSQL strSQLDelete
‘requery so it reflects the newly deleted records
Me.Requery
DoCmd.SetWarnings True
Else ‘other things which work fine
‘———————————————————————————————————-

At the Me.Requery line the code jumps to the OnCurrent of the subform (below), recalculating (updating) probability values. It is then, during the recalculation, when I get Error 3167, “Record is Deleted”. I’ve been able to dig out that this is a record locking thing because I am using the same query during both events. What I’ve read is mostly to just trap the error and resume next. I would much rather find out how to not have the error in the first place.

‘———————————————————————————————————-
Private Sub Form_Current() ‘on the subform in which the data is being deleted from
On Error GoTo HandleErr

If Me.NewRecord Then
Me!dblCummPI = “”
Me!dblCummRMNE = “”
Else
Me!dblCummPI = PIProbabilityProduct(“qryProbeData1point1”, [LabDataPKID]) ‘here is the line on which the error occurs
Me!dblCummRMNE = RMNEProbabilityProduct(“qryProbeData1point1”, [LabDataPKID])
End If

‘———————————————————————————————————–
A little background:
It’s a long story on why but I have two command buttons, 1 button pre-loads, as it were, a list of values into the subform, and then sets the subform to AllowAdditions = False. The other is the button described above. This happens when I *load* the data into the subform and then *unload* (delete) the records right after. If I change to another subform (there are 4 on the main form on tabs) and then change back and then delete the records, the query has been released and I can delete then with nary a grumble from Access. That is the reason I think it’s a Record Locking thing. But that is way too many button clicks for the people who will be using this and the subform full of #DELETES’s causes way too much concern. That is not an assumption on my part, I’ve been told.
How can I get around this? What other method(s) (if available) can I use to sidestep the locking (if that is really the cause) of the records?
If I must, then, well, I’ll trap it and go on.
Now, before I look like this guy Thank You.

gdr

Reply | Quote

Replies

By using a SQL statement to delete the records, you are pulling the rug from under the form, as it were, so it trips up.

You could set the focus to another subform in code, delete the records, then switch back to the first subform. Or you could set the RecordSource property of the subform temporarily to an empty string, and restore it after deleting the records. If you set Application.Echo False before, and Application.Echo True afterwards, the user will hardly notice all this going on.

Reply | Quote

Hi Hans,
Thank you for the little work around. I went with shifting the focus, since I had been doing it that way manually anyway.

gdr

Reply | Quote

Hans,
I apologize but I spoke too soon and I’ve confused myself even more than normal.

I used the Application.Echo False, set the focus to another subform, did the requery, set the focus back to the proper subform and turned the Echo back on. Everything seemed to work just fine.

However (now it comes) I did this trial by opening the main form in question by double clicking on it in the db window. Time and time again everything does work fine, with or without the added code, when the main form is opened in that manner. When the code reaches the Requery, it does_not_go to the OnCurrent event and hence no error.

*But*, when it is called from another form (actually either of 2 other forms) and I do that load and unload thing and it gets to that Requery, the next step is to the OnCurrent of that subform (where I recalculate) and that error returns. This happens whether or not I have added the aforementioned coding.

Why when opened from the db window does it *not* go to the OnCurrent event and when opened from another form it does?!?!?
So I’ll give the other method you suggested a go and see what happens. In the meantime if you can shed any light on why it should matter to the subform as to where and/or how the main form is opened, I would certainly appreciate it.
Thank you again.

gdr

Reply | Quote

Gary,

I don’t know what the custom functions called in the OnCurrent do, so .

Reply | Quote

Hi Hans,
Here you go. The first coding is called in the OnClick of a command button, the second is the OnCurrent event of the subform and the third is the function being called in the OnCurrent event. The FlexRound function called in my function, PIProbabilityProduct, is from Helen Feddema’s accarch94.zip found on her Access Archon archives from WAW http://www.helenfeddema.com/access.htm%5B/url%5D

‘——————————————————————————————-
Dim strSQLDelete As String
Dim strMsg As String
Dim strTitle As String
Dim intAnswer As String

strMsg = “You will delete any data you have entered into the Probe Data Form!”
strTitle = “Do you want to delete this data?”
intAnswer = MsgBox(strMsg, vbExclamation + vbOKCancel, strTitle)

If intAnswer = vbOK Then
strSQLDelete = “DELETE * FROM qryProbeData1point1 ”
strSQLDelete = strSQLDelete & “WHERE LabDataPKID = ” & Me!lngLabDataPKID

DoCmd.SetWarnings False
DoCmd.RunSQL strSQLDelete
‘requery the subform recordset so it reflects the newly deleted records
Me.Requery
DoCmd.SetWarnings True

ElseIf intAnswer = vbCancel Then
Me.sbfrmProbeData1point1.SetFocus
Me.sbfrmProbeData1point1.Form!txtGelNumber.SetFocus
Me.sbfrmProbeData1point1.Form.AllowAdditions = False

End If
‘——————————————————————————————
Private Sub Form_Current()
On Error GoTo HandleErr

If Me.NewRecord Then
Me!dblCummPI = “”
Me!dblCummRMNE = “”
Else
Me!dblCummPI = PIProbabilityProduct(“qryProbeData1point1”, [LabDataPKID])
Me!dblCummRMNE = RMNEProbabilityProduct(“qryProbeData1point1”, [LabDataPKID])
End If

ExitHere:
Exit Sub

HandleErr:
MsgBox “Error ” & Err.Number & “: ” & Err.Description & _
“in procedure Form_Current of VBA Document Form_sbfrmProbeData1point1”, vbOKOnly, “Form_Current”
Resume ExitHere
Resume
End Sub
‘——————————————————————————————
Public Function PIProbabilityProduct(ByVal strQuerySource As String, intLabDataID As Integer) As Double
On Error GoTo HandleErr

Dim db As Database
Dim rst As Recordset
Dim dblProductPI As Double
Dim strSQLCalc As String

strSQLCalc = “SELECT * ”
strSQLCalc = strSQLCalc & “FROM ” & strQuerySource & “”
strSQLCalc = strSQLCalc & ” WHERE LabDataPKID = ” & intLabDataID
strSQLCalc = strSQLCalc & ” And Included = ” & -1

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQLCalc)

dblProductPI = 1

With rst
Do Until .EOF
dblProductPI = dblProductPI * Nz(rst!RoundPI, 1)
.MoveNext
Loop
End With

PIProbabilityProduct = FlexRound(dblProductPI, 4)

ExitHere:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

HandleErr:
MsgBox “Error ” & Err.Number & “: ” & _
” (” & Err.Description & “) in procedure ProbabilityProduct of Module modProductFunction”, _
vbOKOnly, “modProductFunction”
Resume ExitHere
Resume
End Function
‘——————————————————————————————-

Thank you

gdr

Reply | Quote

Frankly, I don’t understand this at all. As a test, I created two linked tables and a main form and subform based on those, with a command button on the main form doing more or less what yours does. Of course, my simulation is extremely simple. The subform is being requeried without problems, without resorting to tricks like setting focus somewhere else. So I suspect there is another factor at play that hasn’t been mentioned yet.

Also, I don’t understand why you have calculations in the OnCurrent event of the subform. If I understand the code correctly, the PIProbabilityProduct function multiplies the RoundPI field for all records in the subform. If that is correct, the result is independent of the current record in the subform, so there is no need to recalculate it when you move from record to record in the subform. You would need to recalculate the product in the following circumstances:

– In the OnCurrent event of the main form, because then a different set of records is loaded into the subform.
– In the AfterUpdate event of the control on the subform that is bound to the RoundPI field, to make sure that the product stays up-to-date when one of its factors changes.
– In the AfterDelConfirm event of the subform, to keep the product up-to-date when the user deletes records.

Reply | Quote

Hi Hans,
Sorry for the delay in getting back to you. I’ve been trying to pare down this db to the essentials and zip it but I cannot get it down under the 100k limit. Too many moving parts.
As far as the calculations in the OnCurrent event, that’s the only way I know, without storing the values, that will keep the 2 resultant textboxes, located in the subform’s footer, filled with the proper data as one moves from record to record. See the attached jpg as to the design of the subform — it is a continuous form. The procedure is to enter in the data (DNA samples), with whatever allele’s (Low and/or High) are present in the ‘gel’ (the recording medium), a match is then either present or not (this is example is dealing with a match allele). An equation (based on common statistical methods) is then chosen depending on which and how many allele’s match. I don’t do that calculation at that time (but these values [RoundPI] and [RoundRMNE] DO get stored), but after all data has been entered. Then clicking each individual [RoundPI] and [RoundRMNE] value is calculated as well as the cummulative values. They are also *forced* to Calculate again if they change any of the data. Perhaps I should just *break down* and begin to store the cummulative values as well? But, that’s why I have it there.
<<<<<<<>>>>>>>
The only thing else I can think of, and I apologize for the oversight, is that this main form can be called from 2 different forms and when closed returms back to the particular calling form . I use OpenArgs to pass the name of the calling form (and set the calliing form Visible = False) so this main form ‘knows” where to return to. Neither of the 2 forms which may be calling this form have any tables or queries which would ‘overlap’, as it were, the recordset of any of the 4 subforms on this main form or of the main form itself. To reiterate, this error comes about whenever it is called from either of these 2 forms and does not when the form is opened via double-clicking on it in the db window.
I hope this helps your understanding, at least a little, of the process I am doing.
btw, even with the OnCurrent commented out, the error still occurs in the same manner.
Thank you very much.

gdr

Reply | Quote

Gary,

No need to apologize – you *did* mention that the problem only occurs when the form is opened from another form, not when it is opened directly from the database window.

I’m afraid I am stumped. I have no idea what causes the problem, let alone what to do about it . Sorry.

Reply | Quote

Hi Hans,
Thanks for your time and energy.
If I happen on an answer this place’ll be the first to know.

gdr

Reply | Quote

Gary,

I still don’t understand why this problem occurs for one subform, but not for the others. The following code gets around it in my tests, probably because it uses the Access interface to delete the records in the subform instead of going behind Access’ back to the Jet engine. But that doesn’t explain why the error only occurs occasionally, and only in one subform…

Here is the modified code for the OnClick event procedure of the button that deletes records in the subform:

Private Sub cmdUnload1point1_Click()
On Error GoTo HandleErr

Dim strMsg As String
Dim strTitle As String
Dim intAnswer As String

strMsg = “You will delete any data you have entered into the Probe Data Field!”
strTitle = “Do you want to delete this data?”
intAnswer = MsgBox(strMsg, vbExclamation + vbOKCancel, strTitle)

If intAnswer = vbOK Then
‘ Set focus to the subform
Me.sbfrmProbeData1point1.SetFocus
On Error Resume Next
‘ Try to set focus to first control in detail section of subform
Me.sbfrmProbeData1point1.Form!dblChildBandsLow.SetFocus
On Error GoTo HandleErr
‘ Select all records (in subform) and delete them
RunCommand acCmdSelectAllRecords
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
‘————————————————————————
‘This is added only to get a recordcount of the underlying recordset
‘————————————————————————
Dim rst As Recordset
Dim intCount As Integer
Set rst = Me.sbfrmProbeData1point1.Form.Recordset
intCount = rst.RecordCount
Set rst = Nothing
‘————————————————————————
‘Returns 0 now (was 8 before)
‘————————————————————————
Else
Me.sbfrmProbeData1point1.SetFocus
Me.sbfrmProbeData1point1.Form!txtGelNumber.SetFocus
Me.sbfrmProbeData1point1.Form.AllowAdditions = False

GoTo ExitHere
End If

ExitHere:
DoCmd.SetWarnings True
Exit Sub

HandleErr:
MsgBox “Error ” & Err.Number & “: ” & Err.Description & _
” in procedure cmdUnload1point1_Click of VBA Document Form_frmLabData”, vbOKOnly, “cmdUnload1point1_Click”
Resume ExitHere
Resume
End Sub

Reply | Quote

Hi Hans,
Actually what concerns me at this time is that I don’t understand why the recordset is not releasing (? if that would be the correct terminology) under one set of circumstances (when the form is opened from the db window) and not the other (when opened from another form).
Your work around is nicer, in that it stays within the module in question (the OnClick event of the command button on the main form) and not waiting until it get to another form’s module (the OnCurrent of the subform) as what mine klutzy work around does.
Thank you for continuing to look at this, I haven’t given up on trying to discern the underlying mechanism of this error.

gdr

Reply | Quote