Help !!!! ... please :-) User Defind function, Access Session not closing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi TWIMC

I have a query that is the record source of form. On the Form_Current event I set a recordset to Me.RecordSetClone see the following code. I've removed/commented out the rest of the code but problem still exists. Now the problem is for unknown reason if I close the Access session it re - opens and the only way to close the Access sesson is to use End Task
I've wittled it down to the code line Set rstMyRecords = Me.RecordsetClone in that if I comment this line and only declare the recordset and then set it to nothing everything is OK, so what is wrong with the line of code. I have other forms that do exactly the same. The only difference being the the record source for the other forms is normally just one table but for this form there are 4 tables and criteria which uses user defind functions not sure if this effects a recordset. Any ideas on why the Access session doesn't close would be much appriciated. I think something related to the recordset is obviously staying active and is not be released when either rstMyRecords.Closs or Set rstMyRecords = Nothing are being used

Private Sub Form_Current(
On Error GoTo Form_Current_Er
Dim rstMyRecords As DAO.Recordse

Set rstMyRecords = Me.RecordsetClon

Form_Current_Exit
On Error Resume Nex
rstMyRecords.Clos
Set rstMyRecords = Nothin
Exit Su

Form_Current_Err
MsgBox Err & ": " & Error$, vbCritical, "Form_frmAddEditData.Form_Current
Resume Form_Current_Exi
Resume Nex
Resum
End Su

OK I found that it relates to my user defind functions so can someone tell me what is wrong with the following piece of code or identify which object remains open that forces the Access session not to close

Thanks again
KM
 
Kevin McCartney said:
Hi TWIMC,

I have a query that is the record source of form. On the Form_Current
Click to expand...
event I set a recordset to Me.RecordSetClone see the following code. I've
removed/commented out the rest of the code but problem still exists. Now the
problem is for unknown reason if I close the Access session it re - opens
and the only way to close the Access sesson is to use End Task.
I've wittled it down to the code line Set rstMyRecords = Me.RecordsetClone
Click to expand...
in that if I comment this line and only declare the recordset and then set
it to nothing everything is OK, so what is wrong with the line of code. I
have other forms that do exactly the same. The only difference being the the
record source for the other forms is normally just one table but for this
form there are 4 tables and criteria which uses user defind functions not
sure if this effects a recordset. Any ideas on why the Access session
doesn't close would be much appriciated. I think something related to the
recordset is obviously staying active and is not be released when either
rstMyRecords.Closs or Set rstMyRecords = Nothing are being used.
Private Sub Form_Current()
On Error GoTo Form_Current_Err
Dim rstMyRecords As DAO.Recordset

Set rstMyRecords = Me.RecordsetClone

Form_Current_Exit:
On Error Resume Next
rstMyRecords.Close
Set rstMyRecords = Nothing
Exit Sub

Form_Current_Err:
MsgBox Err & ": " & Error$, vbCritical, "Form_frmAddEditData.Form_Current"
Resume Form_Current_Exit
Resume Next
Resume
End Sub


OK I found that it relates to my user defind functions so can someone tell
Click to expand...
me what is wrong with the following piece of code or identify which object
remains open that forces the Access session not to close.Hi KM,

I might first suspect some type of corruption.

Have you tried creating a new blank db and importing everything
from this db into it?

Next, I might suspect the user-defined functions.
Can you post them here along with the SQL for the form?

The Form_Current event can fire "many times." Is it possible
that the combination of many firings and user-defined functions
that take "too long" to complete is creating a "backlog"?

A simple pre and aft Msgbox might give you some clues.

MsgBox "About to set rst."
Set rstMyRecords = Me.RecordsetClone
MsgBox "Finished setting rst."

I remember seeing some strange behavior with RecordSetClone
but that had to do with a checkbox. Do have all the latest updates?

I apologise that I cannot zero in on your problem, but maybe above
will be a starting point.

Gary Walter
 
Hi Kevin,

Maybe you have already figured this out,
but I have to ask.....

Is there a chance that somewhere in that form's
code you have something like

If Me.chkbx1 Then

End If

instead of

If Me.chkbx1 = True Then

End If
 
Back
Top