Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    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 <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15> Thank You.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?!?!? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Gary,

    I don't know what the custom functions called in the OnCurrent do, so <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

    '-------------------------------------------------------------------------------------------
    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.SetFocu s
    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <Calculate> 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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    <<<<<<<<You said: "So I suspect there is another factor at play that hasn't been mentioned yet." >>>>>>>>
    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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 <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>. Sorry.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi Hans,
    Thanks for your time and energy. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    If I happen on an answer this place'll be the first to know. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    gdr <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.Set Focus
    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.SetFocu s
    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

  12. #12
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •