Pauseing a Macro (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

(Edited by patt on 13-Feb-03 12:54. Include database)

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

Reply | Quote

Yes! I see now how it works. I will build it in my working data base and see how it performs on the real thing.
Now – back to trying to get it to put out all selected accounts when no Department is chosen.
Thank you

Reply | Quote

Hi Pat,
I’m answering here the question you asked on my combo box question, because it really relates to this question. After copying your Form (and related queries and tables) into a new database, to try and get your form to combine with the form to choose a dept. — even if I choose an account on the form, when I click the export button, it pops up a box for me to type in the account. Then a second for the last account.
Sigh—-.

Reply | Quote

This will happen if you haven’t named the form correctly in the criteria of the query.
Are the criteria referring to the correctly named form?
If you cannot solve it post your DB.

<>
Does this involve multiple forms?

Pat

Reply | Quote