Results 1 to 10 of 10
  1. #1
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65

    Using a Global Variable to Populate a Text Box in Multiple Reports

    Hello,



    I am updating a database used to gather sales and expenses data for tax purposes. Every year I update multiple Report headers with the new year and with the current IRS mileage reimbursement figure. The year just goes into the headers, but the mileage rate will go into a label and into a calculation.

    I want to automate these updates, so I created a set-up Form with combo boxes to select the year and the rate. I had a problem displaying the rate to the required 3 digits on the drop-down list, but have that figured out. I also incorporated some coding to ensure that the database always opens to the last year and mileage rate selected so I don't have to do that every day throughout the year.

    I created a general module to capture the Year and the Mileage Rate as global variables, but can't figure out how to pass those into the reports (about 12 of them). Am pretty sure that would need to be an OnLoad whenever the macro is run to open the report. Any ideas?

    Thank you

    Click image for larger version. 

Name:	Advertising.PNG 
Views:	19 
Size:	5.9 KB 
ID:	40583Click image for larger version. 

Name:	Totals.PNG 
Views:	19 
Size:	11.4 KB 
ID:	40584Click image for larger version. 

Name:	SetUp.PNG 
Views:	19 
Size:	25.2 KB 
ID:	40585

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,875
    No VBA required.

    Textboxes in report can reference controls on form.

    If data is saved into table, include table in report RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,879
    Textboxes in report can reference controls on form.
    True, but the picture looks like a navigation form. If that is the case and a user sets up the form on the Setup Page tab then goes to the Reports tab, the values in the setup form are gone, yes? In that case, TempVars or Public variables might be required, although I seem to recall someone had a different way to pass values to another form or report when switching nav form tabs.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    Uncle Gizmo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    I answered a question about generating Multiple Reports and this was the answer I provided:-

    https://www.niftyaccess.com/generate-multiple-reports/

    It's not a direct answer to your question, however some of the techniques used might be of interest to you.

    Basically you add some custom properties to the Report. When you open the report, you pass into the report the name of the form that opened it. The report uses the passed in Form name to interrogate the Form that opened it. The reports load event (I Think) extracts the information like the date range, the report title, record source, things like this.

    Sent from my Pixel 3a using Tapatalk

  5. #5
    Uncle Gizmo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    25
    This is the video explanation of the process:-

    https://youtu.be/Tl-O3DabPz8

    Sent from my Pixel 3a using Tapatalk

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    Can maybe use Dlookup in a textbox on report.

    DLookup("FieldName" , "TableName")

    Numeric
    DLookup("FieldName" , "TableName" , "Criteria = n")

    String
    DLookup("FieldName" , "TableName" , "Criteria= 'string'")TIP: Notice the single apostrophe before and after the string value.

    Date

    DLookup("FieldName" , "TableName" , "Criteria= #date#")TIP: Notice the # symbol before and after the date value.

  7. #7
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Quote Originally Posted by Micron View Post
    True, but the picture looks like a navigation form. If that is the case and a user sets up the form on the Setup Page tab then goes to the Reports tab, the values in the setup form are gone, yes? In that case, TempVars or Public variables might be required, although I seem to recall someone had a different way to pass values to another form or report when switching nav form tabs.
    Hello,

    Yes, I posted the Set Up Form and examples of two different reports. The Set Up form contains two combo boxes with selections for the Tax Year and the mileage rate. To get them to persist, I have saved each in a separate table that the form calls on loading.

    The reports (all are different and not based on a template with variables) have a label in the header with "XXXX Year to Date". I wanted to replace the XXXX with the Year variable as selected in the Set Up form, but the table that the variable is in is not part of the record source that the data in the detail section of the report comes from. So that's why I was thinking a global variable to be used in all the report headers.

  8. #8
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Quote Originally Posted by June7 View Post
    No VBA required.

    Textboxes in report can reference controls on form.

    If data is saved into table, include table in report RecordSource.
    Hello June7,

    Since the Report Record Source for the detail data is a separate table, and the TaxYear table with the saved year variable is just one field/one row, is there any way to combine them in the report, or would I have to make a sub-report for the header to use the variable?

    Thank you

  9. #9
    PinkDuster's Avatar
    PinkDuster is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2017
    Location
    Cleveland area
    Posts
    65
    Quote Originally Posted by Bulzie View Post
    Can maybe use Dlookup in a textbox on report.

    DLookup("FieldName" , "TableName")

    Numeric
    DLookup("FieldName" , "TableName" , "Criteria = n")

    String
    DLookup("FieldName" , "TableName" , "Criteria= 'string'")TIP: Notice the single apostrophe before and after the string value.

    Date

    DLookup("FieldName" , "TableName" , "Criteria= #date#")TIP: Notice the # symbol before and after the date value.
    I tried the DLookUp and that works! Thank you, and thank you to all who answered and made suggestions.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,875
    DLookup() probably best.

    An option could be a Cartesian query. This is a query without JOIN clause. Every record of each table associates with every record of other table. So the 1-record from TaxYear table would show on every record of query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Global Variable
    By gtg430i in forum Programming
    Replies: 11
    Last Post: 03-10-2014, 05:00 PM
  2. Can't See Global Variable
    By CementCarver in forum Programming
    Replies: 12
    Last Post: 09-19-2013, 12:28 PM
  3. Global variable
    By ramdandi in forum Queries
    Replies: 3
    Last Post: 12-18-2011, 01:01 AM
  4. Username as Global Variable
    By imintrouble in forum Access
    Replies: 3
    Last Post: 10-10-2011, 10:45 AM
  5. Variable across multiple forms/reports
    By Poyan in forum Access
    Replies: 1
    Last Post: 05-31-2011, 11:03 AM

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