Results 1 to 8 of 8
  1. #1
    oleander is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    16

    Hitting Cancel and Stopping a Macro

    Hi everyone!

    When I click a button on one of my forms, it runs a macro that opens a query. The query asks for criteria to be input by the user, and if you hit cancel you get a new popup that says "Macro Single Step" where the user can click Stop All Macros.

    How can I get the cancel button to cancel the macro at that point and not show the additional pop up box?



    The current macro in its entirety is:

    Set Propery
    Control Name: Text41
    Property: Value
    Value: 5

    OpenQuery
    Query: Name qry_Name
    View: Datasheet
    Data Mode: Read Only

    SetPropery
    Control Name: Text41
    Property: Value
    Value:

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont run it in a macro,
    run it in code.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think you can avoid the cancel macro dialog with this scenario. But I never use macros so could be mistaken.

    My first advice is to not use popup inputs - reference controls on form for user input.

    My second advice is to not use dynamic parameterized queries at all - I don't. Apply filter criteria to form or report.

    Why open query? Why not open a form or report?

    Is Text41 a textbox on form? Why are you setting its value?
    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.

  4. #4
    oleander is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    16
    Essentially the database contains student information and I want other staff members to be able to generate a list of student e-mail addresses so they can send out mass e-mails as necessary. There are three regions, each region has 6 schools, each school has four grade levels, so just to keep things simple I'm just having them enter the school and grade level they want rather than choose from drop down boxes or something. The value being set in the text box is the staff member's region number (they click a button for their region, the value in the text box gets set, the query runs using that criteria), and then the pop up boxes ask for the school and grade level the staff what's e-mail addresses for. Staff can then copy the datasheet into excel to do a mass e-mail.

    The query is set to read only, so they can't accidentally change any data. All they do is copy and paste.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why involve Excel? Why not email from Access?

    So with popup boxes, how do you deal with situation where user accidentally or otherwise enters non-valid input? Or as you have discovered, aborts process? You can't. This just results in user aggravation and frustration - things I try to program to avoid.

    Use form with comboboxes so you can better control user input. People do understand them so how does it not 'keep things simple'?

    Why is the macro setting Text41 with static value? You originally said the query prompts user input. Now you say the query uses textbox with user input. But you are setting textbox to static value. I am confused.
    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.

  6. #6
    oleander is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    16
    You can e-mail from Access?

    So the problem I'm having with combo boxes is figuring out how to allow multi-select and having the query accept the multiple inputs as criteria. Do you know how I can do that?

    Also, the static value is for that person's specific region. They click on the button for their specific region, that button populates the value, the query gets run with that value as one of the criteria.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, email can be sent from Access. Many threads discussing methods.

    What do you mean be 'multiple inputs'? WHERE Region = 4 Or Region = 5 or WHERE Region = 4 AND OrderDate = Date() ?

    Review http://allenbrowne.com/ser-62.html and http://allenbrowne.com/ser-50.html

    If you are setting the textbox to 5 how are you using the user input? Still not making sense to me.

    Post the query SQL statement.
    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.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So the problem I'm having with combo boxes is figuring out how to allow multi-select
    Forget that. Multi select combos are only for multi value fields (2010 and up I believe), which from what little I know exist mainly for use with SharePoint lists. Other than that, there are few reasons to use mvf's and most of us who troll here would not. Aside from the 2 good suggestions given, you could consider what's commonly referred to as cascading combo boxes (I prefer the term dependant combo's, but that's a battle I would lose).

    In other words, the school combo list depends on the district combo selection. I don't see a need for a 3rd level, rather a means of selecting the grade range. Maybe spinner controls if not text boxes. You could enforce 2 values for a grade range, or enforce the first and if the user doesn't provide a second value, default to the first so that the range becomes FROM 3 TO 3.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 05-28-2016, 07:35 AM
  2. Cancel button in Quit Macro
    By ashu.doc in forum Forms
    Replies: 3
    Last Post: 09-19-2012, 04:40 PM
  3. Replies: 2
    Last Post: 05-05-2012, 02:34 AM
  4. Help Stopping AutoExec Macro
    By drewetzel in forum Access
    Replies: 2
    Last Post: 10-03-2011, 10:51 AM
  5. VBA to cancel a macro
    By GraemeG in forum Programming
    Replies: 1
    Last Post: 03-26-2011, 04:50 PM

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