Results 1 to 12 of 12
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Passing report's textbox value through global function into reports' source query

    I'd like my reporting dashboard to have a variable date range as defined by the textbox. I built a public function in a general module not in the scope of the report's module:



    Code:
    Public dateRange As Integer
    
    Public Function returnDateRange()
        returnDateRange = [Reports]![rptReturnsStatistics].[txtMonthRange]
    End Function
    To allow passing this variable into the query date filter easily, which is:

    Code:
    >=DateAdd("m",-returnDateRange(),Date())
    This works when I set returnDateRange to a static integer, but its failing to update the reports page when I enter something in the txtMonthRange and press "go". The code for clicking on Go is:

    Code:
    Public Sub Go_Click()    returnDateRange
        Me.Requery
    End Sub
    So it should be fairly simple, but I must be missing something.

    The reason I can't just put the reference string into the criteria of the WHERE term is Access says its too complicated of an expression.
    Last edited by Pawtang; 04-15-2021 at 08:28 AM. Reason: redacted data

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can't update an open report field if that's what you're trying to do. You have to close and reopen the report AFAIK. Reports are static when opened. Maybe if you explained what you are trying to do in general terms rather than explaining what you've created and how it works (or doesn't) someone might suggest a better approach. Or modify your code to close and reopen the report with the value passed as an OpenArg parameter. What I'm not getting is if a query is involved and it correctly performs the calculation in a field, why can't you just include that field value on the report so that it's there when you open it the first time?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Micron View Post
    You can't update an open report field if that's what you're trying to do. You have to close and reopen the report AFAIK. Reports are static when opened. Maybe if you explained what you are trying to do in general terms rather than explaining what you've created and how it works (or doesn't) someone might suggest a better approach. Or modify your code to close and reopen the report with the value passed as an OpenArg parameter. What I'm not getting is if a query is involved and it correctly performs the calculation in a field, why can't you just include that field value on the report so that it's there when you open it the first time?
    Sure thing. I want to have a semi-dynamic dashboard displaying these four charts. Each chart is looking at the quantity of returned parts versus some other criteria over a dynamic period of months before the current data.

    Because of the ability to update more readily, I have switched over to using a form to display the charts.

    So in the textbox at the top of the form, I'd like the user to be able to input an integer that represents how many months before the current month to include in the charts. Clicking go would apply this change of the criteria, and the charts would update accordingly.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the recordsource of the Form? Like micron, I'm not sure I'm following your approach.

  5. #5
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by orange View Post
    What is the recordsource of the Form? Like micron, I'm not sure I'm following your approach.
    The form has no data source, each individual chart has a Row source. Maybe this is why Me.Requery fails.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Me refers to the form or report. Requery applies only to a bound form or control. If there is no record source, there is nothing to requery. You can force calculated controls to recalculate using Me.Recalc. Perhaps that is all you need. However up to this point I don't even see where you said what control you're using to display the charts. Me and Access charts parted ways a long time ago, so I might not be much help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Micron View Post
    Me refers to the form or report. Requery applies only to a bound form or control. If there is no record source, there is nothing to requery. You can force calculated controls to recalculate using Me.Recalc. Perhaps that is all you need. However up to this point I don't even see where you said what control you're using to display the charts. Me and Access charts parted ways a long time ago, so I might not be much help.
    Each chart is based on its own query. The form is just a container for them to exist in with no data source of its own. Charts seem to be able to exist in this isolated way with their own data source definitions. Here's one example of the first chart's "Row Source":


    Code:
    SELECT qryTopCustomersByDateRange.[customerNumber], Sum(qryTopCustomersByDateRange.[CountOfpartNumber]) AS SumOfCountOfpartNumber FROM qryTopCustomersByDateRange GROUP BY qryTopCustomersByDateRange.[customerNumber] ORDER BY Sum(qryTopCustomersByDateRange.[CountOfpartNumber]) DESC;
    Obviously Access is just not a great charting environment. Unfortunately doing manual exports to Excel to then build charts with static data is not a great solution, as the goal here is to have dynamic charts that are always up to date when new data is added. I've done this before using Microsoft's Power BI but... getting deeper into the soup of Microsoft's obscure product lineup always feels counterproductive. I bet there's a way to tie the back end data to an Excel doc somehow though that doesn't require manual updating.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I believe if you re-write and re-assign the control source it will refresh. Not sure, but it works for other cases.
    Closest thing I did to this was have Excel chart based on dynamic named range. Access pushed data via automation because it needed to be over-written each time. IIRC, transferspreadsheet appends, not over-writes, plus you can't use a range going from Access to Excel, so more reasons to use automation. If the chart range had 10 or 100 rows it didn't matter, it still worked because of the dynamic range.

    Or you could research how to update the chart control. I've said here and elsewhere that I'd rather rub sand in my eyes than use Access charts so you can imagine why my knowledge in this area is somewhat limited. Excel charts run circles around Access charts.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My guess is that you would use the value in the textbox and after validating the value, modify your query sql to include the date range, then open your form. You will have to update the sql underlying each of the charts.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you want to post a compacted and zipped db with just enough to replicate the issue, I imagine someone can come up with a solution.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    I did end up getting this working. I'll lay out an outline of what I did:

    Code on the Form's module:
    Code:
    Option Compare Database
    
    Private Sub Go_Click()
        dateRange = Me!txtMonthRange.Value
        returnDateRange
        Me.Recalc
        Me.Refresh
    End Sub
    Public function in a public module:

    Code:
    Public dateRange As Integer
    
    Public Function returnDateRange()
        returnDateRange = dateRange
    End Function
    Criteria for the date in the source query for each chart:
    Code:
    >=DateAdd("m",-returnDateRange(),Date())

    The reason for using the global pass-through function is that having the whole reference expression inside of the DateAdd function gives you an error; Access considers it to be too complicated. This reduces the complexity of the expression to an acceptable level...

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

Similar Threads

  1. Replies: 3
    Last Post: 03-11-2021, 01:30 PM
  2. Replies: 3
    Last Post: 11-20-2019, 01:52 PM
  3. Passing Global Constants to controls on a report
    By Carbontrader in forum Reports
    Replies: 1
    Last Post: 05-10-2017, 12:17 PM
  4. Passing global variables question
    By newbieX in forum Programming
    Replies: 6
    Last Post: 10-03-2014, 02:09 PM
  5. Replies: 4
    Last Post: 06-18-2014, 11:47 AM

Posting Permissions

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