Help for Beginner in Access Macros

Mongy

Board Regular
Joined
Dec 9, 2002
Messages
51
Howdy,

Minor experience with Access. Can Set up queries with no major problems.

What I would like a pointer is in creating Macros. I'm use to recording then editing them in Excel.

At the moment, have a form, with a number of buttons linked to queries.

What I would like to have, is one button, where it runs one query, outputs to a set path, then moves to the next query, etc. So in the end, should have a folder with 20 files all output from Access.

Some quick pointers on how would be appreciated, related to the above issues.

Cheers
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

I can help you get started with the macro but you will need to fill in the details.

Create a new macro, and while in the macro design screen click in the 'Action' column select 'OutputTo' from the drop down list and then use the following properties :

Object Type = Query
Object Name = {select your query from the drop down list}
Output Format = {select a format from the drop down list, e.g. .xls or .txt etc)
Output File = {insert a name a like C:\file1.xls or C:\file1.txt etc}
Auto Start = No
Template File = {leave blank}
Encoding = {leave blank}

Repeat the above steps for as many queries as you want to export - so you macro at this point may be 20 lines long.

After you have included all of the queries you want, add a new line to the end of the macro with the 'Action' = MsgBox with the following properties :

Message = File Transfer Complete
Beep = Yes
Type = Information
Title = Done!

Insert one last new line at the very bottom of the macro with the 'Action' = StopMacro.

Save the macro.

Open your form in design view, right click the button you want to attach the macro to, find the 'On Click' property (under the Event tab) and select your macro name from the drop down list. Save and test the form.

While you are in the macro sceen have a look at all of the options availabe in the drop down list - there are quite a few things you can do with macros. After you have experimented with macros for a while you may want to have a look at VBA. Macros are great to start with but given time you will find VBA much more flexible and powerful and the VBA in Access is almost identical to the VBA in Excel (the main difference is how you reference a cell in Excel versus accessing a record in Access).

Post back if you have any questions.

HTH, Andrew
 

Mongy

Board Regular
Joined
Dec 9, 2002
Messages
51
Legend.. That's all I needed.. Just a little push in the right direction. I was using other actions, as wasn't sure which one it was.

Much thanks
 

Forum statistics

Threads
1,136,655
Messages
5,677,015
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top