ErrMsg: The MS Jet DBEngine stopped the process because you and an SimonW 30.09.2006 08:38:01 Hello I am intercepting the Delete action with KeyPreview and running a process on the selected records. How can I release an object (table) having Set the object to a variable and enumerated the selected records to memory, I have Set the variable to Nothing at the end of my code but I still receive the above message once when I hand back to the BeforeDelConfirm event. TIA Simon PS. I have to do it this way because Access removes the selection immediately OnDelete. i.e. If I were using DAO (which I'm not) I would use: Set rst = Nothing rst.Close

Re: ErrMsg: The MS Jet DBEngine stopped the process because you and an "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 30.09.2006 11:01:15 How did you set the reference in the first place? Presumably you've got a Set statement somewhere: you need to set that same object to nothing. BTW, what you've got is incorrect for DAO. You're setting the reference to nothing and then you're trying to close it. You have to do it in the opposite order: rst.Close Set rst = Nothing -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message news:C168607E-EA5E-4D60-B265-C361C6F692FD[ at ]microsoft.com... [Quoted Text] > Hello > I am intercepting the Delete action with KeyPreview and running a process > on > the selected records. How can I release an object (table) having Set the > object to a variable and enumerated the selected records to memory, I > have > Set the variable to Nothing at the end of my code but I still receive the > above message once when I hand back to the BeforeDelConfirm event. TIA > Simon > > PS. I have to do it this way because Access removes the selection > immediately OnDelete. > > i.e. If I were using DAO (which I'm not) I would use: > Set rst = Nothing > rst.Close

Re: ErrMsg: The MS Jet DBEngine stopped the process because you an SimonW 30.09.2006 15:04:01 Thanks Douglas, here's what I'm doing Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) 'intercept delete action and run some code to update the selected records Select Case KeyCode Case 46 'If KeyCode is Delete x = UpdateSelectedRecs(Form_sfdtls) End Select End Sub Private Function UpdateSelectedRecs(f As Form) Dim i As Long Dim RS As Object Dim Criteria As String 'Get the form and its recordset. Set RS = f.RecordsetClone If RS.RecordCount = 0 Then Set RS = Nothing Exit Function End If ' Move to the first record in the recordset. RS.MoveFirst 'Move to the first selected record. RS.Move f.SelTop - 1 ' Build the string For i = 1 To f.SelHeight If Criteria <> "" Then Criteria = Criteria & " OR " End If Criteria = Criteria & "[itemid]=" & RS.itemid RS.MoveNext Next i DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE items SET items.[free] = True WHERE " & Criteria, 0 DoCmd.SetWarnings True Set RS = Nothing End Function Funny thing is, when the UpdateSelectedRecs has run and the Delete action resumes, the records are locked and I receive the "you and another user are attempting to change the same records at the same time" message. When I OK the message and hit Delete again, all works fine. I have found another solution but would be interested to learn if there is some fix that would free the object. TIA, Simon "Douglas J. Steele" wrote: [Quoted Text] > How did you set the reference in the first place? Presumably you've got a > Set statement somewhere: you need to set that same object to nothing. > > BTW, what you've got is incorrect for DAO. You're setting the reference to > nothing and then you're trying to close it. You have to do it in the > opposite order: > > rst.Close > Set rst = Nothing > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message > news:C168607E-EA5E-4D60-B265-C361C6F692FD[ at ]microsoft.com... > > Hello > > I am intercepting the Delete action with KeyPreview and running a process > > on > > the selected records. How can I release an object (table) having Set the > > object to a variable and enumerated the selected records to memory, I > > have > > Set the variable to Nothing at the end of my code but I still receive the > > above message once when I hand back to the BeforeDelConfirm event. TIA > > Simon > > > > PS. I have to do it this way because Access removes the selection > > immediately OnDelete. > > > > i.e. If I were using DAO (which I'm not) I would use: > > Set rst = Nothing > > rst.Close > > >

Re: ErrMsg: The MS Jet DBEngine stopped the process because you an "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 30.09.2006 17:35:05 To be honest, I think it's just timing. Try putting in a DoEvents after you set RS to nothing. BTW, despite what you said in your first post, that's DAO code you're using! -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message news:ACF9AFD7-0900-42B9-B0A2-7170C1057283[ at ]microsoft.com... [Quoted Text] > Thanks Douglas, here's what I'm doing > > Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) > > 'intercept delete action and run some code to update the selected > records > > Select Case KeyCode > Case 46 > 'If KeyCode is Delete > x = UpdateSelectedRecs(Form_sfdtls) > End Select > > End Sub > > Private Function UpdateSelectedRecs(f As Form) > Dim i As Long > Dim RS As Object > Dim Criteria As String > > 'Get the form and its recordset. > > Set RS = f.RecordsetClone > > If RS.RecordCount = 0 Then > Set RS = Nothing > Exit Function > End If > > ' Move to the first record in the recordset. > RS.MoveFirst > > 'Move to the first selected record. > RS.Move f.SelTop - 1 > > ' Build the string > For i = 1 To f.SelHeight > > If Criteria <> "" Then > Criteria = Criteria & " OR " > End If > Criteria = Criteria & "[itemid]=" & RS.itemid > RS.MoveNext > Next i > > DoCmd.SetWarnings False > DoCmd.RunSQL "UPDATE items SET items.[free] = True WHERE " & > Criteria, 0 > DoCmd.SetWarnings True > > Set RS = Nothing > > End Function > > Funny thing is, when the UpdateSelectedRecs has run and the Delete action > resumes, the records are locked and I receive the "you and another user > are > attempting to change the same records at the same time" message. When I > OK > the message and hit Delete again, all works fine. I have found another > solution but would be interested to learn if there is some fix that would > free the object. > > TIA, Simon > > "Douglas J. Steele" wrote: > >> How did you set the reference in the first place? Presumably you've got a >> Set statement somewhere: you need to set that same object to nothing. >> >> BTW, what you've got is incorrect for DAO. You're setting the reference >> to >> nothing and then you're trying to close it. You have to do it in the >> opposite order: >> >> rst.Close >> Set rst = Nothing >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no private e-mails, please) >> >> >> "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message >> news:C168607E-EA5E-4D60-B265-C361C6F692FD[ at ]microsoft.com... >> > Hello >> > I am intercepting the Delete action with KeyPreview and running a >> > process >> > on >> > the selected records. How can I release an object (table) having Set >> > the >> > object to a variable and enumerated the selected records to memory, I >> > have >> > Set the variable to Nothing at the end of my code but I still receive >> > the >> > above message once when I hand back to the BeforeDelConfirm event. TIA >> > Simon >> > >> > PS. I have to do it this way because Access removes the selection >> > immediately OnDelete. >> > >> > i.e. If I were using DAO (which I'm not) I would use: >> > Set rst = Nothing >> > rst.Close >> >> >>

Re: ErrMsg: The MS Jet DBEngine stopped the process because you an SimonW 30.09.2006 18:07:01 Thanks again. A DoEvents didn't change the behaviour. I think I'll switch KeyCode 46 to 0 and delete the records with a DELETE query instead... Thanks again, Simon "Douglas J. Steele" wrote: [Quoted Text] > To be honest, I think it's just timing. > > Try putting in a DoEvents after you set RS to nothing. > > BTW, despite what you said in your first post, that's DAO code you're using! > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message > news:ACF9AFD7-0900-42B9-B0A2-7170C1057283[ at ]microsoft.com... > > Thanks Douglas, here's what I'm doing > > > > Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) > > > > 'intercept delete action and run some code to update the selected > > records > > > > Select Case KeyCode > > Case 46 > > 'If KeyCode is Delete > > x = UpdateSelectedRecs(Form_sfdtls) > > End Select > > > > End Sub > > > > Private Function UpdateSelectedRecs(f As Form) > > Dim i As Long > > Dim RS As Object > > Dim Criteria As String > > > > 'Get the form and its recordset. > > > > Set RS = f.RecordsetClone > > > > If RS.RecordCount = 0 Then > > Set RS = Nothing > > Exit Function > > End If > > > > ' Move to the first record in the recordset. > > RS.MoveFirst > > > > 'Move to the first selected record. > > RS.Move f.SelTop - 1 > > > > ' Build the string > > For i = 1 To f.SelHeight > > > > If Criteria <> "" Then > > Criteria = Criteria & " OR " > > End If > > Criteria = Criteria & "[itemid]=" & RS.itemid > > RS.MoveNext > > Next i > > > > DoCmd.SetWarnings False > > DoCmd.RunSQL "UPDATE items SET items.[free] = True WHERE " & > > Criteria, 0 > > DoCmd.SetWarnings True > > > > Set RS = Nothing > > > > End Function > > > > Funny thing is, when the UpdateSelectedRecs has run and the Delete action > > resumes, the records are locked and I receive the "you and another user > > are > > attempting to change the same records at the same time" message. When I > > OK > > the message and hit Delete again, all works fine. I have found another > > solution but would be interested to learn if there is some fix that would > > free the object. > > > > TIA, Simon > > > > "Douglas J. Steele" wrote: > > > >> How did you set the reference in the first place? Presumably you've got a > >> Set statement somewhere: you need to set that same object to nothing. > >> > >> BTW, what you've got is incorrect for DAO. You're setting the reference > >> to > >> nothing and then you're trying to close it. You have to do it in the > >> opposite order: > >> > >> rst.Close > >> Set rst = Nothing > >> > >> -- > >> Doug Steele, Microsoft Access MVP > >> http://I.Am/DougSteele > >> (no private e-mails, please) > >> > >> > >> "SimonW" <SimonW[ at ]discussions.microsoft.com> wrote in message > >> news:C168607E-EA5E-4D60-B265-C361C6F692FD[ at ]microsoft.com... > >> > Hello > >> > I am intercepting the Delete action with KeyPreview and running a > >> > process > >> > on > >> > the selected records. How can I release an object (table) having Set > >> > the > >> > object to a variable and enumerated the selected records to memory, I > >> > have > >> > Set the variable to Nothing at the end of my code but I still receive > >> > the > >> > above message once when I hand back to the BeforeDelConfirm event. TIA > >> > Simon > >> > > >> > PS. I have to do it this way because Access removes the selection > >> > immediately OnDelete. > >> > > >> > i.e. If I were using DAO (which I'm not) I would use: > >> > Set rst = Nothing > >> > rst.Close > >> > >> > >> > > >