Access 2002 refuses to close (Access 2002/SP3)

Topic

New Reply

Ahh. I figured it all out by myself. I had forgot to declare the rst as Recordset and dbs as Database.

I’m leaving this thread alone in case someone else had the same problem.

Reply | Quote

Replies

Link to MSKB article provided by mod – see Help 19

I’m using Helen Feddema’s Main Form Add-In and I was trying to use a part of her code called FromDate() and ToDate() and tried to use it in a crosstab query and when I did that, now when I open the crosstab query either by itself or in a report, I can’t close the Access program. It would “reopen” to a blank Access window. Only by removing those references in the query, I can stop the behavior.

I found this Microsoft Knowledge Base Article 164455 which pretty much describes my issue. Helen’s original code had “.Close” statements, I changed it to “rst.close” and it didn’t solve it. Can anyone help me fix the code so that it would force the recordset to close so that I can quit Access?

Here’s the code that Helen wrote (original, without my pitiful attempts to fix it).

Public Function ToDate() As Date
‘Written by Helen Feddema 9/14/98
‘Last modified 7-4-2002

On Error GoTo ErrorHandler

‘Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblInfo”, dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], “12/31/2004”)
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit

End Function

Reply | Quote

Link to MSKB article provided by mod – see Help 19

I’m using Helen Feddema’s Main Form Add-In and I was trying to use a part of her code called FromDate() and ToDate() and tried to use it in a crosstab query and when I did that, now when I open the crosstab query either by itself or in a report, I can’t close the Access program. It would “reopen” to a blank Access window. Only by removing those references in the query, I can stop the behavior.

I found this Microsoft Knowledge Base Article 164455 which pretty much describes my issue. Helen’s original code had “.Close” statements, I changed it to “rst.close” and it didn’t solve it. Can anyone help me fix the code so that it would force the recordset to close so that I can quit Access?

Here’s the code that Helen wrote (original, without my pitiful attempts to fix it).

Public Function ToDate() As Date
‘Written by Helen Feddema 9/14/98
‘Last modified 7-4-2002

On Error GoTo ErrorHandler

‘Pick up To date from Info table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblInfo”, dbOpenTable)
With rst
.MoveFirst
ToDate = Nz(![ToDate], “12/31/2004”)
.Close
End With

ErrorHandlerExit:
Exit Function

ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit

End Function

Reply | Quote

1. It is wise to tick ‘Require Variable Declaration’ in Tools | Options… in the Visual Basic Editor. This will add a line Option Explicit at the top of each new module. You will have to add this line manually in existing modules. Being forced to declare all variables explicitly can save you a lot of grief, as you have found.

2. To avoid confusion between DAO and ADO, it is a good idea to declare rst as a DAO recordset:

Dim rst As DAO.Recordset

Although not always necessary, I tend to prefix ALL DAO objects this way for consistency:

Dim dbs As DAO.Database

Reply | Quote

1. It is wise to tick ‘Require Variable Declaration’ in Tools | Options… in the Visual Basic Editor. This will add a line Option Explicit at the top of each new module. You will have to add this line manually in existing modules. Being forced to declare all variables explicitly can save you a lot of grief, as you have found.

2. To avoid confusion between DAO and ADO, it is a good idea to declare rst as a DAO recordset:

Dim rst As DAO.Recordset

Although not always necessary, I tend to prefix ALL DAO objects this way for consistency:

Dim dbs As DAO.Database

Reply | Quote

It’s also a good idea to set both dbs and rs to Nothing at the end of your code. Otherwise memory is not released and memory is gobbled up.

Reply | Quote

Pat,

If I set both dbs and rs to Nothing at the end of the code, wouldn’t this clear out the stored values? That bit of code actually stores the dates that I enter in a form and I pull those dates from the “stored” values and put them in the reports I need.

By setting dbs and rs to Nothing at the end of the code, wouldn’t that cause the FromDate() and ToDate() to “lose” the stored values?

Hans, thanks for your tip.

Reply | Quote

Pat,

If I set both dbs and rs to Nothing at the end of the code, wouldn’t this clear out the stored values? That bit of code actually stores the dates that I enter in a form and I pull those dates from the “stored” values and put them in the reports I need.

By setting dbs and rs to Nothing at the end of the code, wouldn’t that cause the FromDate() and ToDate() to “lose” the stored values?

Hans, thanks for your tip.

Reply | Quote

Setting rst and dbs to Nothing releases the memory used by these objects. It will not affect other variables. Once you have assigned the result of ToDate() to a variable, it will keep its value until you assign another value, or the variable goes out of scope.

Reply | Quote

This is pretty new for me (setting rst and dbs to nothing), what’s the syntax? I tried rst = Nothing and got an error.

Reply | Quote

rst and dbs are object variables. You assign a value to an object variable using the keyword Set. Just like your code has

Set dbs = CurrentDb

you use

Set dbs = Nothing

Reply | Quote

rst and dbs are object variables. You assign a value to an object variable using the keyword Set. Just like your code has

Set dbs = CurrentDb

you use

Set dbs = Nothing

Reply | Quote

This is pretty new for me (setting rst and dbs to nothing), what’s the syntax? I tried rst = Nothing and got an error.

Reply | Quote

Setting rst and dbs to Nothing releases the memory used by these objects. It will not affect other variables. Once you have assigned the result of ToDate() to a variable, it will keep its value until you assign another value, or the variable goes out of scope.

Reply | Quote

It’s also a good idea to set both dbs and rs to Nothing at the end of your code. Otherwise memory is not released and memory is gobbled up.

Reply | Quote