Results 1 to 10 of 10
  1. #1
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13

    Showing Report Total Box in Form

    I am putting together a Lease Management Database. I have a report that shows the amount of Active Leases that the client has and another that shows the amount of Inactive Leases. I used the ''=Count(*)" function that comes with Access to generate the total amount of Leases for both reports at the end of each report.

    On my navigation form, I would like to constantly show the amount of active and inactive leases when you open the database based on these two aforementioned text boxes. I created two unbound textboxes and I wanted to use the control source to reference the reports' total textboxes on my form. For example, on the Active Lease Report it shows a total of "39" at the end and I want to write "You have [39] Active Leases" on my form. That number is subject to changes therefore I don't want to have to write it everytime. I know this possible, but for some reason when I enter this:



    Code:
    [Reports]![ActiveLeasesReport].[TxtboxTotalActive]
    I see "#Error".

    Strangely, at some point before I was able to make it work and the number "39" appeared in my textbox. I don't know what I did wrong after that.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Whatever code is used to get the value on the report, run that code in the OnOpen event of the navigation form to get those values at that time. The error is probably because you are trying to reference the report which is not open yet?

  3. #3
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by Bulzie View Post
    Whatever code is used to get the value on the report, run that code in the OnOpen event of the navigation form to get those values at that time. The error is probably because you are trying to reference the report which is not open yet?
    You are completely right. I ran it when the forms where opened and it worked! If I run the code in the OnOpen event of the navigation form, do the forms still need to be open for the textbox to show the value? If not, is there a way for the results to appear with the reports being unopened? Sorry, I am still new to Access.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you mean it worked when the 'report' was opened? If you do not reference report to calculate the value then of course the report does not have to be open.

    If a textbox in form footer with a Sum() function expression cannot be relied on to provide the desired output, perhaps an expression using DSum() domain aggregate function could produce the same result as the report.
    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.

  5. #5
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by June7 View Post
    Do you mean it worked when the 'report' was opened? If you do not reference report to calculate the value then of course the report does not have to be open.

    If a textbox in form footer with a Sum() function expression cannot be relied on to provide the desired output, perhaps an expression using DSum() domain aggregate function could produce the same result as the report.
    Thank you for answering. Yes, you are correct - I meant when the "reports" were opened the values would show on the form.

    The textbox in the form function works fine. It is when I want to replicate that same textbox value on my navigation form that I ran into issues. I am not familiar with the DSum() function however. How should I use it?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't use Navigation form, don't like it. Review https://www.accessforums.net/showthread.php?t=32053

    You said 'textbox in the form'- did you mean report again? But why is the Navigation form causing an issue in this case?

    Don't know your data structure so really can't advise specifics on using DSum(). Suggest you search Access Help or MS knowledge base for info on domain aggregate functions. Make an attempt and when you encounter issue, post question with the attempt.
    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.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If I read your issue right, you have a Form(Navigation) and Report and on the report you have some totals. You want those totals to be on the Navigation form as well(regardless if the report is open or not). If that is the case, put an unbound field on the form and use the same formula or code you use in the report (put in OnOpen event on form) to calculate this same value in the field(on the form). If data changes and you want the form to update, maybe do a timer to requery the form.

  8. #8
    awurah is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by Bulzie View Post
    If I read your issue right, you have a Form(Navigation) and Report and on the report you have some totals. You want those totals to be on the Navigation form as well(regardless if the report is open or not). If that is the case, put an unbound field on the form and use the same formula or code you use in the report (put in OnOpen event on form) to calculate this same value in the field(on the form). If data changes and you want the form to update, maybe do a timer to requery the form.
    YES! Thank you. That's exactly right. I put the unbound field on the form, but the issue is there is no actual formula used in the report. It simply is "=Count(*)"(through the "Totals" section in the Report Design).

    I tried this in the OnOpen event of the form:

    Code:
    Me!Textbox44 = Me![Reports]![ExpiredLeasesReport].[TxtboxTotalExpired]
    Textbox44= Unbound field on the form
    ExpiredLeasesReport=Report
    TxtboxTotalExpired=Field on the report

    It says that "Microsoft Access can't find the field "Reports" referred to in your expression. I am pretty sure the syntax is off, but I have been trying all morning and nothing seems to work. I am obviously quite novice at this.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Forget about the report. You have to figure out the formula to calculate that same report totals value. Can you come up with the same total the report shows in a query. Maybe list your tables, fields and some sample data and the total value you are trying to get.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Count() function can be used in textbox in form footer. Again, we really don't know your structure well enough. Maybe will need DCount() domain aggregate function.
    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. Replies: 9
    Last Post: 08-08-2016, 02:57 PM
  2. total sales showing on a form
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 12-01-2015, 04:20 PM
  3. Replies: 4
    Last Post: 04-06-2015, 10:22 PM
  4. Replies: 15
    Last Post: 04-16-2014, 01:15 PM
  5. Replies: 4
    Last Post: 03-14-2011, 11:12 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
  •  
Other Forums: Microsoft Office Forums