Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pauseing a Macro (2000)

    I have a Macro that calls a form. The form has a Combo Box from which the user selects which Department they wish to see.
    Unfortunately, the Macro keeps on trucking while the selection is being made. How do I get the Macro to pause for input before continueing.
    This same Macro worked fine with a dialog box, but a dialog box doesn't give me the 'choose from a list' capability.
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pauseing a Macro (2000)

    I don't think you can do that in a macro. I tried setting the WindowMode argument of the OpenForm action to Dialog, but that doesn't stop the macro from continuing. If you convert the macro to VBA, the code will pause if you open a form with WindowMode:=acDialog until the user closes the form. You can convert a macro to VBA by selecting it in the database window and selecting File/Save As... from the menu.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    If you can use VBA code then do so. It provides error handling and greater readability and flexibilty. Try to avoid macros, there are really legacy functionality.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    Okay, but I don't know VBA. Don't know it at all. I'm starting to learn a bit. If there is a better way to do this I'm all ears, but I would have to be led in baby steps.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    Everyone has to start somewhere. Basically if you can code a macro then you can use the same logic for the VBA coding. One thing to help you is look at the code the wizards produce for performing actions. Also there are plenty of code snippets on this site.
    It'll be worth your while to get a book or two. For Access specifically look for the Access Developers Handbook by Litwin, Getz and Gilbert. Reading it will keep you occupied for about the lifetime of the universe! (It's a monster). Having said that you probably won't have to ask any more questions here for quite some time if you understand it.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Pauseing a Macro (2000)

    Do what Hans suggests and you can then compare what your macro was doing in VBA.
    VBA is no demon, really, it just takes time to learn like most things do.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    And if you don't want to go VBA right now, what you can do is split your macro into two -- have one that opens the form, and another that runs the information after you have made a selection from the list (or, better yet, from a button that says "okay, process my choice now," or words to that effect. You can call your macro from the "event" properties of the button ("onclick") or list selection ("afterupdate"). I agree w/ everybody, though about how much more control you have in VBA, but this might get you by for right now.
    thx
    Pat

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    And if you don't want to go VBA right now, what you can do is split your macro into two -(cut)

    Will try this tomorrow. I really want to learn VBA, but, as usual, time is a factor.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    It's not that difficult to code in VBA. You would be better in the long run to take the plunge and try all your next coding in VBA. People here will give comments and help if you get stuck.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    Okay, I'm game to try.
    Let's start with Hans answer. This can't be all you type. What -exactly - do I need to type in the Code window to 1. Turn off Warnings, 2. Open my form and wait for input, 3. Run my query, and 4. Export to Excel.
    Yes, I know I could do a report. But this is what my supervisor wants. He likes to see the results in an Excel spreadsheet.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Pauseing a Macro (2000)

    What you need to do first is to set up a button on the form that you has the Department number ComboBox.
    Make a button on this form, set it up to print a report, any report just so it generates code to print the report and the error coding.
    You need to go into the code window, if you don't know how to do this just click on the button you have just generated and go into properties, then go to the OnClick event and click just to the right of the wording event procedure and it will open up the VBE (Visual Basic Editor) Window.
    Mark and delete the code:

    stDocname=...
    DoCmd.OpenReport...

    and enter the following code:

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Your Query Name", "Path and Fielname of your Excel Spreadsheet", True
    DoCmd.SetWarnings True

    That's all there is to it.
    Don't forget to substitute your Query name for "Your Query Name", and the path ans filename for your spreadsheet (eg. c:Path...YourSpreadsheetName.xls).

    Post back if you have any problems.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    DoCmd.SetWarnings False
    This looks like it will accomplish step one

    Step 2 is to Open the Form and wait for input. A button on the form wont really work for this

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Your Query Name", "Path and Fielname of your Excel
    Does this run the Query before exporting?? If not, I need the code to run the query before exporting it

    Looks like step one and step four may be solved, now -- how do I run this module (Is that the right word?) Since a button on the form wont really do it.

    Can't tell you how much I appreciate your time.

    Spreadsheet", True
    DoCmd.SetWarnings True

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Pauseing a Macro (2000)

    You mention about opening a form, does this form require you to input something for the query, if so, then you may as well put the button here. Unless I misunderstood something about the forms you are talking about.

    You don't need to run the query as it's run in the DoCmd.TransferSpreadsheet statement.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pauseing a Macro (2000)

    Pat,
    This is the same form/query that we are working on to get it to show all my accounts when I don't chose a department. I just thought I'd truck on to the next step while trying to get that right.
    The goal here, (per my supervisor) is to have a seamless operation. Choose a requirement (Maybe from a menu?) and Access opens the correct form, runs the correct Query, and exports the results to Excel.
    He isn't unhappy with what I've done so far. He just wants this to be usable by TRUE Access novices, who wouldn't know a Query from a Grocery List.
    Yes, you saw that right. Creating a menu - switchboard? - will be my next chore.

    Could not have gotten as far as I have without the help of this list. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Pauseing a Macro (2000)

    <P ID="edit" class=small>(Edited by patt on 13-Feb-03 12:54. Include database)</P>Have a look at this database and you will see what I mean by the form doing all the work.
    You will have to redirect the output of the DoCmd.TransferSpreadsheet statement, because as it stands it will go to c:data as the directory.

    If you have any questions, please post.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •