Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where Should DAO Report Commands Go? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    I have the following code in Detail_Format section of Report and it works OK

    Should the first two statements and the last three statements be put in the On Open and On Close section respectively for increased performance?

    Thanks, John

    ============================================
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    lngID = idsKeyToPersonal

    strExpression = "*"
    strDomain = "tblPersonal"
    strCriteria = "[idsPersonalKey] = " & lngID
    Set rst = db.OpenRecordset("SELECT " & strExpression & _
    " FROM " & strDomain & _
    " WHERE " & strCriteria, _
    DB_OPEN_DYNASET)
    If rst.RecordCount = 0 Then
    strLastName = "No"
    strFirstName = "Record"
    strMidInit = "On File"
    lngMemberStatusKey = 0
    Else
    strLastName = Nz(rst("strLastName"), "")
    strFirstName = Nz(rst("strFirstName"), "")
    strMidInit = Nz(rst("strMidInit"), "")
    lngMemberStatusKey = Nz(rst("idsMemberStatusKey"), "")
    End If

    MemberName = IIf(Trim(strMidInit & "") = "", _
    strLastName & ", " & strFirstName, _
    strLastName & ", " & strFirstName & " " & strMidInit)
    MemberStatus = lngMemberStatusKey

    rst.Close
    Set rst = Nothing
    Set db = Nothing
    ============================================

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Where Should DAO Report Commands Go? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7

    Do you mean the lines declaring and destroying the database and recordset objects? The declarations are local to the procedure unless you declare them at a module level. As it is written, you create and destroy those objects every time the detail_format event fires, which is normally at least twice for each record. Moving the declaration to the module level and destroying them in the report_close would certainly be more efficient, but you also need to check for FormatCount =1 before executing the rest of the code each time.
    Charlotte

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

    Re: Where Should DAO Report Commands Go? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7

    Hi John,

    In theory, creating variables only once instead of each time the Detail_Format event occurs is more efficient. It's doubtful whether you'll notice it in practice. But if you want to do so, you should put variable and constant declarations at the top of the report module, before all procedure and function definitions. Then, assign variables where possible in the On Open event, and set object variables to Nothing in the On Close event.

    Note:
    You use several undeclared variables in this code. Perhaps you only posted part of the code, and omitted the declarations here. But if you didn't declare them, please do so, and turn on Require Variable Declaration in Tools | Options... It will save you a lot of trouble, and it makes for more efficient code execution.

    The module could look like this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Option Explicit
    Option Compare Database

    Private db As DAO.Database
    Private rst As DAO.Recordset
    Private lngID As Long
    Private strExpression As String
    ...

    Private Sub Report_Open(Cancel As Integer)
    Set db = CurrentDb
    End Sub

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    strExpression = "*"
    ...
    Set rst = db.OpenRecordset(...)
    ...
    rst.Close
    End Sub

    Private Sub Report_Close()
    Set rst = Nothing
    Set db = Nothing
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Should DAO Report Commands Go? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7

    Hi Hans & Charlotte

    Hans, I added code as suggested.

    Charlotte, I wrapped code within Detail_Format with If FormatCount = 1 Then.

    The report works OK, without any noticable difference:

    Thanks for the clinic

Posting Permissions

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