Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Error from No Value on Report, Need to Ignore or Circumvent


    I have an intricate bunch of switchboards that opens different forms and reports.

    When I get to reports, you first click a button to open reports instead of forms, then the report of your choice, then a form that has a start and end date, then finally the report selected.

    Everything was working perfect until I added the start date as part of the report's query criteria (which it gets from the form with the start and end date).

    The issue is that the report is actually opened and hidden before it is shown. When the report loads there are certain fields, that if blank, are grayed out. However, because it now wants a date from the form with a start and end date, it gives an error since that form has yet to come up.

    *I need a way to either ignore the error and press forward, or a different way to tell is to stay hidden but ignore the VBA for the load event until the button on the date form has been clicked.

    Here is my code in the load event:

    If Len(Me.Textbox & "") = 0 Then
    Me.Textbox.BackColor = vbBlack
    End If

    I've tried "On Error Resume Next", but maybe I'm not putting it in the right place. I tried above and/or below, and even after the first line of the If statement with no success. If anyone has an idea as to how to get around this, please help!

    Thanks.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When I get to reports, you first click a button to open reports instead of forms, then the report of your choice, then a form that has a start and end date, then finally the report selected.
    To me, this is backwards. The first thing is to open the parameter form, enter/select the parameters (dates, etc), select the report, then open the report.


    The issue is that the report is actually opened and hidden before it is shown. When the report loads there are certain fields, that if blank, are grayed out. However, because it now wants a date from the form with a start and end date, it gives an error since that form has yet to come up.
    Why open the report, then hide it? The parameter form must be open for the query to get the dates or any other parameter(s) it requires. Then the controls (not fields) can be formatted (BG set to gray/red/whatever)

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    The report needs to be selected and opened first because there is actually only one date form. The date form has IsLoaded statements that state if a report is loaded then make it visible.

    I want to avoid making multiple forms if I can reuse just one.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Two options, one of which you've been given elsewhere. I use a single criteria form, and I use OpenArgs to pass the name of the report to be opened to it. Second, you can open the form in dialog mode from the open event of the report. Your button on the form just hides it, which allows the report to finish opening. Since the form is then open but hidden, it's available for the query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    @pbaldy thanks.

    I would like to try the OpenArgs, but how do I get them to pass the value to the report?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Pass the name of the report in OpenArgs, then in your form code

    DoCmd.OpenReport Me.OpenArgs,...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Maybe I don't understand. I was thinking the code gets placed in the form somewhere since it would open first, then somewhere in the report.

    What I really don't get is how the value transfers from form-to-report (or report-to-form). Do I specify the start date/end date from the date form in the OpenArgs, then apply those to the date field in my report?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, my presumption was that you have buttons on some other form to open Report1, Report2, etc. Behind each button you would open the criteria form and include the report name in OpenArgs, like "Report1". Then behind a "continue" button or whatever on that criteria form you would open the report, as noted above. Presuming the query behind the report is looking to the form for its criteria, you just hide it so the query can find it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Then the OpenArgs seem unnecessary if my thinking is correct. If I open the date form first, then hide it, then the values for the query are already present. I should then just be able to select a report and have it come up...

    I am still interested in OpenArgs, can you point me to any examples on the net that could help?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Keep in mind with that method the first button is opening the criteria form, not the report, so OpenArgs is how you tell the criteria form which report to open.

    Perhaps you want the other method, where your first button opens the report, and the open event of the report opens the criteria form in dialog mode.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    I think I had got a bit confused.

    So, The main form (which chooses which report) opens the criteria (date) form which has OpenArgs. The criteria form button then reveals the hidden report with the dates from the criteria form.

    Then the main form would have the OpenArgs in a button for each specific form, while the criteria form would have to have all possible forms in OpenArgs. Yet, only the specific form would open since only that button from the main form was chosen.

    Is this correct?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't think so, no. You say "reveals the hidden report" but the report won't have been opened yet. And there aren't a bunch of forms. Let's reduce the database to 2 forms and several reports. The main form has buttons for each report. Those buttons open the criteria form, not the report, passing the name of the desired report in OpenArgs. A button on the criteria form opens the report specified in OpenArgs and hides the criteria form. I hadn't mentioned it before, but you would also close the criteria form in the close event of the report.

    The more I think about it the more I think you'll like the other method better.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    I think I just had another break through on OpenArgs. You specify the open argument in the first form, and in the second form refer to it as OpenArgs, which could then essentially be anything from the first form.

  14. #14
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    So I ended up not needing the OpenArgs, I just had to make sure code was rearranged properly. Thanks for the help and understanding of OpenArgs.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. I assume you're not opening the report hidden first now?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search to ignore null fields
    By tommy93 in forum Queries
    Replies: 10
    Last Post: 02-07-2012, 10:58 AM
  2. How to Ignore Err 2501
    By Jojojo in forum Reports
    Replies: 3
    Last Post: 11-11-2011, 06:30 PM
  3. ignore delete error
    By smahdih in forum Access
    Replies: 1
    Last Post: 11-05-2011, 03:42 PM
  4. Ignore last two digits in formula
    By anstasija in forum Access
    Replies: 6
    Last Post: 11-02-2010, 08:57 PM
  5. On Click - Ignore Required Fields
    By amy in forum Forms
    Replies: 1
    Last Post: 08-18-2009, 07:42 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