May 9 2006, 09:26 AM
I'm not sure this is the right forum catagory to post this, but since it had to do with queries, I thought I'd start here. I have a query that I use for a report. Well my boss wants to copy/paste the query results into Excel, but wants to do it at the "touch of a button." I thought that I could use the Switchboard to accomplish this, but I don't see where it will allow me to run queries. So I used the query to make a datasheet formatted form, expect when I used the Switchboard to open that form, it changed it from a datasheet form to a tabular form--not what I wanted. Does anyone have any ideas or suggestions?
May 9 2006, 09:28 AM
The simplest way to do this is to develop a macro that opens the form in the desired manner. Then modify the switchboard button to run that macro.
You could also convert the macro to a module, modify the line that opens the form, and have the button run the code.
May 9 2006, 09:31 AM
if you use the switchboard you have to tell access to specifically open the form in datasheet mode.
So create a new module...add a function in it e.g.:
Public Function TestIt()
DoCmd.OpenForm "YourForm", acFormDS
Then choose the option to run code in the switchbord manager and use TestIt as the name of the function you want to execute. Now it should open in DS view, BUT...why so complicated...you can export queries directly to excel with the OutputTo method
or the TransferSpreadsheet method
. No need to open any form, or have your boss copy/paste things manually!
May 9 2006, 09:38 AM
Thank you both. I thought that the answer was going to end up with a macro or some kind of visual basic module. The problem is that I am not fluent in macro or visual basic. I haven't had time or opportunity to learn. I know barely enough to even be dangerous. Which one would be easiest and fastest to learn and do--a macro or a module?
May 9 2006, 09:39 AM
Well if you will spend the time to do one or the other then learn VBA...macros are very very limited and cannot handle error handling.
May 9 2006, 09:40 AM
The automatic code the switchboard creates defaults to opening the form in normal mode; you would need to alter that line of code to open it up in datasheet mode. You could also alter that line of code to open a query instead of a form.
However, I would consider Freakazeud's suggestion here -- don't even bother with this, just send the data straight to Excel.
May 9 2006, 09:57 AM
Easiest and fastest to learn is a macro by far. However, Access macros are pretty wimpy. This one, tho, does not need to be particularly strong to do what you want to do.
Click Macros in the list, then New. Set the Action column to OpenForm. At the bottom you will see the Action Arguments box. Set those as you need them to be. At least the name of the form and the View, which sounds like it needs to be datasheet. Save the macro. Modify the switchboard to open the macro instead of the form.
May 9 2006, 10:13 AM
Okay, guys. I'm gonna try this and I'll let ya know how I do.
May 9 2006, 10:21 AM
Good luck and let us know if you run into any walls.
May 9 2006, 11:12 AM
Okay, I sorta combined them...kinda. I found OutputTo listed among the macro action list, so instead of using the form I created (for nothing, and will now delete) I set the macro up to run the query and send the results to Excel.
If my boss doesn't like it, I'm gonna use the OutputTo function on him!
Thanks a bunch! Kathy
May 9 2006, 11:14 AM
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here