Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2002 refuses to close (Access 2002/SP3)

    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.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2002 refuses to close (Access 2002/SP3)

    Link to MSKB article provided by mod - see <!help=19>Help 19<!/help>

    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 <!mskb=164455>Microsoft Knowledge Base Article 164455<!/mskb> 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

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

    Re: Access 2002 refuses to close (Access 2002/SP3)

    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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access 2002 refuses to close (Access 2002/SP3)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2002 refuses to close (Access 2002/SP3)

    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.

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

    Re: Access 2002 refuses to close (Access 2002/SP3)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2002 refuses to close (Access 2002/SP3)

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

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

    Re: Access 2002 refuses to close (Access 2002/SP3)

    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

Posting Permissions

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