Results 1 to 8 of 8

Help using both a query and recordset when generating a report

  1. #1
    Datadude87 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2016
    Location
    Racine, WI
    Posts
    5

    Help using both a query and recordset when generating a report


    Hello:

    I use a query to gather information for a report. In the query, I use a custom function to generate a date. The function takes the inputted date and calculates a date that is 7 business days prior. It also takes into account holidays that are stored in a table called tblHolidays. In the process of running this query/report, the custom function gets called many times. Until recently this was not a problem. We recently migrated tblHolidays to another database so we have one central location to enter constants, universal dates, etc. The original database now accesses the new location thru a linked table. Now running this report has slowed down to a crawl. I know this is because for each iteration of calling the custom function, it reaches out to the new location, opens the data base, gets the info it needs, then closes the data base.

    I was thinking of trying to open a recordset based on tblHolidays using either the On Open or On Activate event when the report is accessed. The custom function could then reference the recordset for each iteration instead of having to reach out to the other database. Can I open a recordset in one of these events and then close the recordset in either On Close or On Deactivate of the report?

    Thank you for any help.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,146
    Interesting concept! You could also make a local table of the data when the database opens, it doesn't change that often, if at all.

  3. #3
    Datadude87 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2016
    Location
    Racine, WI
    Posts
    5
    aytee111:

    Thank you for the idea. That would be easy enough to do and then any process that needs access to holidays would be able to use it.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    10,837
    There is a function here that may be useful with holiday and ability to Add/Subtract X Business Days.
    Good luck with your project.

  5. #5
    Datadude87 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2016
    Location
    Racine, WI
    Posts
    5
    Quote Originally Posted by aytee111 View Post
    Interesting concept! You could also make a local table of the data when the database opens, it doesn't change that often, if at all.
    Thank you for this suggestion. It went with it and regained the speed that I had previously.

  6. #6
    Datadude87 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2016
    Location
    Racine, WI
    Posts
    5
    To all who read this post: I ended up using the suggestion from aytee111 (Thank you!!!), however, I am still curious about opening a recordset as a reference when opening the report. Can I open a recordset in the On Open or On Activate and then close the recordset in the On Close or On Deactivate events? Would I run the risk of leaving a recordset open when it should not be? Any other thoughts?

  7. #7
    Datadude87 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2016
    Location
    Racine, WI
    Posts
    5
    Thanks orange

    This is the code I came up with for doing my calculation. It uses a counter to count up to 7. First it checks to see if a date is a Sunday or a Saturday. If yes, the counter does not advance. Then it it checks to see if the date is in the holiday list. If yes, the counter does not advance. Then if both of those conditions are not met, it advances the counter. It loops until the counter reaches 7 and returns that date to the query.

    'This variable will be what is evaluated by the WEEKDAY function inside the loop
    Dim TestDate As Date
    'This counter will count the number of workdays between the 1st and last date
    Dim TestDateCounter As Integer
    'This will a Boolean type situation to count if a holiday is found
    Dim HolidayTest As Integer


    'Initialize counter to zero
    TestDateCounter = 0
    HolidayTest = 0
    'Sets TestDate to match FKDate that was pulled into the function.
    TestDate = IdgDate


    Do
    'Make the first date to be tested as one day prior to the full kit date. Note: The actual full kit date
    'should not need to be tested as it SHOULD be a workday if order entry did their job correctly.
    TestDate = DateAdd("d", -1, TestDate)
    If Weekday(TestDate) = 1 Or Weekday(TestDate) = 7 Then
    TestDateCounter = TestDateCounter + 0
    ElseIf HolidayTest = DLookup("count(*)", "tblHolidaysLocal", "HolidayDate = #" & TestDate & "#") >= 1 Then
    TestDateCounter = TestDateCounter + 0
    Else
    TestDateCounter = TestDateCounter + 1
    End If
    Loop Until TestDateCounter = 7
    EngDueDate = TestDate
    End Function

    Thanks orange for offering up the support.

  8. #8
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,115
    There might be something that you can't pass to a function or sub but I don't know what that might be. As long as Access can work with it, I suppose the limitation would be size. You cannot say, well if it's too big to be in the database, it's not an issue. That would be incorrect, for as long as you have the required references, a function or sub could work with anything, such as an entire Excel workbook.

    In your case, the question might not be 'can I pass a recordset to a procedure', rather is there an easier or better way to do something. I might be forgetting the exact purpose of the Weekday function, but I believe it's for getting the number of days between two dates that are not Saturdays or Sundays, so you don't need your function to do that all, and certainly not in a looping fashion ("First it checks to see if a date is a Sunday or a Saturday"). BTW, each time it reaches "If Weekday(...) this function runs against the variable, and if that means remote access, off it goes again across cyberspace. I'm not sure of the exact purpose of your function and what you're doing with it, but if it is to get the number of working days between two dates, the Weekday function returns a number which includes holidays. For that, no remote access required. A query against your holiday table returns the number of holidays between those two dates. The difference is the number of working days between the two dates. That could be a local table if management of this will not be an issue. Obviously some maintenance is required since some holidays move around in the calendar year, meaning a redistribution of the FE containing such a table. The thing to consider is whether or not you're overdoing the reach out to the linked table for your purposes.

    Final note on your question. If a public variable is at the module level, or in the case of a form or report, in the declarations section (top) its scope is either database (former) or form/report (latter) which means it's available if the db is open or the form/report is open. Probably not too useful for a report since it doesn't have any controls you can use to interact with that variable. So at the module level, any form could use it. As long as that variable is alive, the machine memory allocated to it is taken up, so I'd have two ways of closing the recordset and reclaiming the memory space. That is to say, when a single form that uses it is closed (obviously you cannot do this on each form if more than one form needs it) as well as during db shutdown. This means telling people to use the db close button you provide to minimize resource drain (or live with the effects if any) or disable the db close button, which can end up being left disabled for all Access databases. I never had a problem with that beyond maybe having to tell someone to open the db and close it properly since the button was re-enabled during the shutdown routine.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-16-2016, 09:44 AM
  2. Replies: 7
    Last Post: 09-03-2015, 10:31 AM
  3. Using a query dao.recordset when opening a report
    By steven22554 in forum Reports
    Replies: 11
    Last Post: 07-17-2014, 10:43 PM
  4. Query Not generating Chart in report
    By jj1 in forum Access
    Replies: 1
    Last Post: 07-03-2014, 09:59 AM
  5. Help on Generating a Report!
    By ETCallHome in forum Reports
    Replies: 10
    Last Post: 06-22-2011, 12:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums