Dialog Box - Visual Basic relation

[Lisa W.]

I am working on several dialog boxes. I have one that I've set up called
"Available Macros" in which I have a list of macros that the user can choose
from. These macros need the file name under the Action - "Transfer Text"
updated each time they access the macro from the list within the dialog box.

Is there a way for me to create via Visual Basic a code that will allow the
macro to show up in the Design View so that they can change the file name?

Currently I can only figure out "Run Macro" via Visual Basic and have also
set up the command button. Any help with this problem is greatly appreciated.

 

[Klatuu]

That is pretty kludgy.
It would be much easier to do it VBA.
You can start by converting your macro to VBA, then paste the code it
produces into the Click event of your command button. You can put a text box
on your form for the user to enter the file name. Then reference the text
box in the TransferText where the file name goes.

There are more elegant ways to do this, but since you are just beginning to
learn VBA, it would be the easiest for you.

Don't be afraid of VBA. It really isn't that hard and you have much more
control over your application than you ever will with macros. You will find
that almost no professional developers use macros.

 

[Lisa W.]

Thanks so much for the prompt response. I will try the VBA approach. You're
correct -- afraid!! I may have to come back to you, but wanted to say
THANKS!

 

[Klatuu]

Okay, post back with any questions you have.
A couple of things that will be helpful in troubleshooting:

If you get an error, post your code and post the error number and indentify
the line the error happened on.

For development purposes, it is a good idea to open your VBA editor, select
Tools, Options, and on the General tab select Break on All Errors.

 

[Lisa W.]

I'm just getting back to this and it's working with the exception of
referencing the textbox for the file name. Can you assist me with how I
reference the TextBox via VB?

Thanks!
Lisa W.

 

[Klatuu]

post back with the code you have and tell me the name of the text box, please.

 

[Lisa W.]

Klatuu,

Below is the code:

Private Function EditMacro()

On Error GoTo Admin_Branch1_Err

Beep
MsgBox "Remember to Edit File Name - ""TransferText""", vbExclamation,
"TransferText File Name Update"
DoCmd.OpenQuery "Key_Admin_Branch", acViewNormal, acEdit
DoCmd.TransferText acExportDelim, "", "Key_Admin_Branch", "Z:\L.
Walker\MacroTest - VB\", True, ""


Admin_Branch1_Exit:
Exit Function

Admin_Branch1_Err:
MsgBox Error$
Resume Admin_Branch1_Exit

End Function

**The text box can be named "Edit File Path" (if I'm understanding you
correctly). The other problem I am having is that I have a Macro Dialog Box
which lists the macro names that need to be run. Is there a way for me to
reference the Macro Dialog Box versus the separate macro (Admin_Branch) so
that if the user clicks another macro name, they will be reference to the
correct macro and can edit the file path via the textbox?

Thanks,
Lisa W.

 

[Klatuu]

Is there any reason you are opening the query before exporting it?

But, to answer you question, this has nothing to do with a macro. You will
not need a macro for this.
Let's say we have a text box on the form named txtExportTo

Here is how you would code the TransferText line to get the path and file
from a text box:
DoCmd.TransferText acExportDelim, "", "Key_Admin_Branch",
Me.txtExportTo", True, ""

 

[Lisa W.]

I'm going to try this.

Thanks!

Is there any reason you are opening the query before exporting it?

But, to answer you question, this has nothing to do with a macro. You will
not need a macro for this.
Let's say we have a text box on the form named txtExportTo

Here is how you would code the TransferText line to get the path and file
from a text box:
DoCmd.TransferText acExportDelim, "", "Key_Admin_Branch",
Me.txtExportTo", True, ""

 

[Lisa W.]

Is this going to work if I need the user to edit the file/path name???

 

[Lisa W.]

Klatuu,

It works! THANKS!!

One other thing, how do I get this to work if I'm looking to have the user
select the macro name from a list (dialog box)? Currently macro is looking
at one specific macro name, but I would like this to work if from the form
view, they can select from a list and after editing the file name, the csv
will appear with correct data.

Thanks in Advance,
Lisa W.

 

[Lisa W.]

Klatuu,

When I look at the results from the csv file, it only returns one row of
data, when I know the table contains more than one. Can you review the VB
code again to ensure I don't have anything missing? When I run the query
from the "Objects" menu, I get the correct results, but not from VB code.

Thanks in Advance,
Lisa W.