Macros and VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

buzz - 03 Nov 2004 08:36 GMT

Is there a way to set a variable within the macro that the macro can use for
different steps?? Specifically, I want to be able to pass a value to a VBA
function. Also, is there a way to take the "output to" command's file
name/path setting and put so type of variable in it to get the file name to
change so I can avoid outputted files overwritting each other. If that can't
be done in a macro, is it possible to use the VBA code to do a statement just
like the "output to" command in the macro??

Reply to this Message

Allen Browne - 03 Nov 2004 09:15 GMT

Macros are not powerful enough to do this.

Use a function instead, e.g. in the On Click property of the command button.
Or use the RunCode action in your macro, and have the function get the
parameter.

Same with OutputTo. In code use:
DoCmd.OutputTo

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Is there a way to set a variable within the macro that the macro can use
> for
[quoted text clipped - 8 lines]
> just
> like the "output to" command in the macro??

Reply to this Message

buzz - 03 Nov 2004 22:59 GMT

That helped a lot. I ended up using the DoCmd.OutputTo command and got it to
work the way I wanted to. Using this command, I've run into another issue
though; I have a good possibility of people using this code that they will
get the runtime error 2302 because this command could be trying to write
files that have special characters based on the user input. I want to be
able to try and catch this if it occurs and run something. I tried using the
"On Error Goto" command just prior to it and it doesn't catch this error. I
was looking through the help files about all types of error commands and I
can't really identify what I should be using. I'm trying to show the users
what not to do but I want to be able to hanlde the error incase it occurs.
Please help.

> Macros are not powerful enough to do this.
>
[quoted text clipped - 17 lines]
> > just
> > like the "output to" command in the macro??

Reply to this Message

Allen Browne - 04 Nov 2004 03:19 GMT

Yes, there are certainly lots of errors that can occur when you let the user
write a file. They will try to write a a CD-ROM drive, a floppy that's
write-protected, an invalid folder, a network resourse that's turned off, a
file that's read-only (so can't be replaced), and so on.

It is therefore essential to use error handling. The "On Error Goto" at the
beginning of the proc is the way to go, and you can see an example of this
in any wizard-generated code. Basically what happens is that the write
fails, control is passed to the error handler, it shows the message (which
gives the user details of what went wrong), and exits the procedure. The
user can then have another go.

We have a basic outline of this in article:
Error Handling in VBA
at:
http://members.iinet.net.au/~allenbrowne/ser-23a.html
The article goes a little further than you need, and shows how to actually
log the errors to a table, so you can look back and see any problems in your
code.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> That helped a lot. I ended up using the DoCmd.OutputTo command and got it
> to
[quoted text clipped - 37 lines]
>> > just
>> > like the "output to" command in the macro??

Reply to this Message