Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46

    vba MsgBox for some but not all


    I have five reports one of which requires a date range (i.e. Start Date and End Date) that are filled in by the user... To save space on my Form I have the names of the reports in a combo-box. I only want the MsgBox to appear if the user doesn't enter the date for the report that requires it. Below is the code that I Frankenstiened together but the MsgBox appears after every report that is selected.

    Code:
    Private Sub cboReports_AfterUpdate()
       If Not IsNull(cboReports) And cboReports <> "" Then
          DoCmd.OpenReport cboReports, acViewReport
       Else
       End If
       If IsNull(StartDate) = True Then
           MsgBox ("This is a Date Range Report. The report is blank because you must select both a Start-Date and an End-Date to populate this type of report."), vbCritical
         cboReports.SetFocus
       End If
       cboReports = ""
    End Sub
    I can't seem to make this work, any help with this would be great. Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You never test for the report. Something like:

    If Me.cboReports = "Whatever" And IsNull(StartDate) Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have five reports one of which requires a date range (i.e. Start Date and End Date)
    How do you know which report requires the dates?
    Or, more to the point, how does the code know which report requires the dates?

  4. #4
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Fair questions... I should have provided more detail, that is my fault, but...

    The Switch Board has the Combo Box that lists all of the Reports. Underneath the Combo Box are two more boxes with Date Pickers one for Start Date and one for End Date.

    The Combo Box gets its information from the Report, which has a Query in it that retrieves the Start Date and End Date from it (i.e.[Forms]![SwitchBoard]![StartDate] And [Forms]![SwitchBoard]![DueDate])

    The Reports are named:
    Admin Report
    Personnel Report
    Personnel Arrived (Date Range)
    Personnel Phone Number
    Work Center

    I only need the MsgBox to alert the User if they don't select the Start Date and End Date for the report named: Personnel Arrived (Date Range)

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The Combo Box gets its information from the Report, which has a Query in it that retrieves the Start Date and End Date from it
    The combo box gets start and end dates from the report?? You're testing this in the AfterUpdate event, so those values will always be null - the report hasn't opened yet.
    A combo box column should have hidden columns holding the possible parameters you see being applicable (probably just 'nodates', 'both'). Ascertain the parameter and deal with whether or not it was satisfied by examining the date textboxes on the form. If not, exit the sub and don't even open the report. Opening a blank report is not slick. At the very least, use the NoData event to cancel report opening. To simply use the NoData event as noted would only serve to confuse because this would fire if there was no data even if the necessary dates were provided.

    Another way would be to use the report tag properties to flag which required what, and in the report open event, check, cancel if need be and present your message.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Thanks for the speedy responses! (This is interesting... I will look in to the Reporting Tags)

    Okay, I reread my post and I misspoke, the Combo Box on the Switch Board contains the names of the five reports, all of which are all separate and all have different filtering criteria for each. Only one of the reports needs data from the Start Date and End Date boxes. The report "Personnel Arrived (Date Range)" is the only report that requires the data selection which will feed the report query criteria. I am using the AfterUpdate so that the selected report automatically opens for the user without any other actions on their behalf. But the report "Personnel Arrived (Date Range)" is the only one that I want the MsgBox to trigger on if there is no Dates selected.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see post 2?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you see post 2?
    Given the clarification, I agree that would be the answer. However I would test for both dates and message that both are required regardless of which is missing for the sake of simplicity. Personally, I would also verify that the values being passed are dates.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So would I, but the point was the answer being lost in the noise.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I never thought of my responses that way.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was just pointing out that the code, even with Paul's modification, does not differentiate WHICH one of the 5 reports requires the dates.
    Currently, the code will ask for dates for all 5 reports.......

    To reduce noise, I'm stepping away....

  12. #12
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Bring your Noise Back Here!...

    Thanks again for the responses and the patience. Yeah, I got caught in the noise as well and then went down the rabbit hole of "Report Properties Tags" and that was fun. Below is the code that seems to work for my intended purposes but I have no idea if it is as clean as it could be.

    Code:
    Private Sub cboReports_AfterUpdate()
       If Not IsNull(cboReports) And cboReports <> "" Then
          DoCmd.OpenReport cboReports, acViewReport  ' use acViewPreview for print preview
       Else
       End If
       If Me.cboReports = "Personnel Arrived (Date Range)" And IsNull(StartDate) Then
           MsgBox ("This is a Date Range Report. The report is blank because you must select both a Start-Date and an End-Date to populate this type of report."), vbCritical
         cboReports.SetFocus
       End If
       If Me.cboReports = "Personnel Quit (Date Range)" And IsNull(StartDate) Then
           MsgBox ("This is a Date Range Report. The report is blank because you must select both a Start-Date and an End-Date to populate this type of report."), vbCritical
         cboReports.SetFocus
       End If
       cboReports = ""
    End Sub
    I added one more report to see if it works and it does... But it seems that I should be able to find some line of code where I could replace the name of the report with something like... Anything that contains "(Date Range)"

    Code:
       If Me.cboReports = "Any string of lettters (Date Range)" And IsNull(StartDate) Then
    Lots of fun here...

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    If Me.cboReports Like "*(Date Range)*" And IsNull(StartDate) Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    PBALDY! THANKS! That worked like a charm!!!!!!!

  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
    Happy to help! I would restructure the logic if you haven't already. Since this is first, it's going to run any report before it bothers to check if it should:

    If Not IsNull(cboReports) And cboReports <> "" Then

    Put it last and add

    Exit Sub

    when you warn the user, so the report never tries to open.
    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. Msgbox-Combo
    By wickedjc in forum Modules
    Replies: 2
    Last Post: 02-16-2016, 12:02 PM
  2. VBA code for MsgBox
    By mladen273 in forum Programming
    Replies: 8
    Last Post: 01-17-2013, 10:05 PM
  3. Yes No msgbox
    By imintrouble in forum Access
    Replies: 3
    Last Post: 10-14-2011, 02:24 PM
  4. MsgBox
    By Mtyetti in forum Forms
    Replies: 4
    Last Post: 07-27-2011, 01:51 PM
  5. Msgbox with data in
    By AndycompanyZ in forum Programming
    Replies: 9
    Last Post: 06-27-2011, 07:52 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