Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2021
    Posts
    4

    By-pass Query Enter Parameter Msg Box


    I have a report named "Finance Dates" - when I select it - a form (Date_Picker) comes up with a date range of two fields asking for date parameter values of Beg and End dates. Also on this form - I have placed a "Cancel" button. When I choose to click on the "Cancel" button, the Enter Parameter Msg. box still pops up asking for the Beg/End dates. How can I stop the Parameter Msg. Box from popping up after attempting to cancel the report with the "Cancel" button or other way? An "Or" Query value maybe? Report VBA string? Appreciate your assistance!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What is the event and the event code that opens the date input form, and does this form open modally? Likely code that runs the query is still being executed, which means it needs to be exited or canceled if the input form cancel button is clicked. Please post that code within code tags (# on posting toolbar). Might need to see the cancel button code (or the rest of the code in the input form) as well. You might be able to simplify this by putting the date fields on the first form and test for values there instead.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jun 2021
    Posts
    4

    Events / Code

    [QUOTE=Micron;477927]What is the event and the event code that opens the date input form, and does this form open modally? Likely code that runs the query is still being executed, which means it needs to be exited or canceled if the input form cancel button is clicked. Please post that code within code tags (# on posting toolbar). Might need to see the cancel button code (or the rest of the code in the input form) as well. You might be able to simplify this by putting the date fields on the first form and test for values there instead.


    Micron - thanks for getting back with me so quickly. In answer to questions:

    -----------------------
    Event: On Open
    -----------------------

    Event Code
    :
    Private Sub Report_Open(Cancel As Integer)

    DoCmd.OpenForm "Date_Picker", , , , , acDialog, "Please Enter Dates."

    End Sub
    ------------------------
    Modally: Yes - it does open in this mode.
    ------------------------
    Cancel Button Code:
    Private Sub Cancel_Click()
    On Error GoTo Err_Cancel_Click

    DoCmd.Close acForm, "Date_Picker"
    DoCmd.CancelEvent

    Exit_Cancel_Click:
    Exit Sub
    Err_Cancel_Click:
    MsgBox Err.Description
    Resume Exit_Cancel_Click
    ----------------------

    Hope this helps....

  4. #4
    Join Date
    Jun 2021
    Posts
    4

    One Other Thing....

    I shut off Modal & Pop up = No. Didn't help - still get Parameter Value box.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I thought the 2nd form was being opened via a first form. Seems that you are opening the date form via the report open event, thus when it tries to open you'll get the date prompts. You have to stop or cancel the report open event. I've never tried to set the event argument from another module (in this case, your date form) so I don't know if it's possible. You cannot stop the report open event with CancelEvent because that's only for cancelling the event that called another event and the click event of the button doesn't fit the bill.

    could try:
    - have a report module level boolean variable. If user cancels on date form, set that to True in the button click event. Insert a line after DoCmd.OpenForm to test if the variable is T or F. If T, cancel the report open event

    In this case, Modal probably won't matter but when it is, whatever called it stops executing that code so sometimes it's the only way to cancel things depending on user action.
    At the risk of repeating myself, code tags...
    Code:
    Private Sub Cancel_Click()
    On Error GoTo Err_Cancel_Click
    
    DoCmd.Close acForm, "Date_Picker"
    DoCmd.CancelEvent
    
    Exit_Cancel_Click:
    Exit Sub
    Err_Cancel_Click:
    MsgBox Err.Description
    Resume Exit_Cancel_Click
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you have all this in the wrong order. Instead of having the code to open the frmDatePicker in the opening event of the report itself you should open it first from wherever you open the report (assuming is a form). On the form, once the dates are entered and validated open the reports using Docmd.OpenReport. Otherwise use Micron's suggestion of checking for a public boolean variable and cancel the report open event (Docmd.CancelEvent does nothing as it runs in the form not the report):
    Code:
    
    Private Sub Report_Open(Cancel As Integer)
    
    
    DoCmd.OpenForm "Date_Picker", , , , , acDialog, "Please Enter Dates."
    If boCanceled = True Then
        Cancel=True
    End if
    
    End Sub
    
    
    'Cancel Button Code:
    Private Sub Cancel_Click()
    On Error GoTo Err_Cancel_Click
    
    
    DoCmd.Close acForm, "Date_Picker"
    'DoCmd.CancelEvent
    boCanceled=True  'Public boCanceled as boolean variable declared in a standard module
    Exit_Cancel_Click:
    Exit Sub
    Err_Cancel_Click:
    MsgBox Err.Description
    Resume Exit_Cancel_Click
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Otherwise use Micron's suggestion of checking for a public boolean variable
    I did say that, but I also said it would be better to just have the date fields on the form used to open the report. It's possible that the date form isn't needed at all. If I had a form for opening reports that used criteria, that form would have the criteria fields, not some form that pops up in between the report chooser and the report. It's far easier to validate the inputs in the same module as opposed to some other form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, somehow I missed all that, 100% in agreement!
    Seems like I need better glasses or stronger coffee....
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    NP. I often lack both as you've no doubt noticed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jun 2021
    Posts
    4

    Order of

    Guys,

    Thanks for all your input. One thing I possibly did not explain very well - when the user chooses to open the Finance report - the report in turn - opens the Date_Picker form to get the parameters.


    On another note - it said to reply with a quote - here's the best one I can come up with: "Confucius say, "Man who fart in church - sit in own pew."

  11. #11
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    The user must first open the Date_Picker module, to get the parameters, and then the financial report.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-23-2017, 06:16 AM
  2. Replies: 6
    Last Post: 11-08-2016, 07:49 AM
  3. Replies: 2
    Last Post: 07-20-2016, 03:32 AM
  4. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  5. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 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