Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / September 2006

Tip: Looking for answers? Try searching our database.

ErrMsg: The MS Jet DBEngine stopped the process because you and an

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SimonW - 30 Sep 2006 09:38 GMT
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
Douglas J. Steele - 30 Sep 2006 12:01 GMT
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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello
> I am intercepting the Delete action with KeyPreview and running a process
[quoted text clipped - 12 lines]
> Set rst = Nothing
> rst.Close
SimonW - 30 Sep 2006 16:04 GMT
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

> 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.
[quoted text clipped - 22 lines]
> > Set rst = Nothing
> > rst.Close
Douglas J. Steele - 30 Sep 2006 18:35 GMT
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!

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks Douglas, here's what I'm doing
>
[quoted text clipped - 91 lines]
>> > Set rst = Nothing
>> > rst.Close
SimonW - 30 Sep 2006 19:07 GMT
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  

> To be honest, I think it's just timing.
>
[quoted text clipped - 97 lines]
> >> > Set rst = Nothing
> >> > rst.Close
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.