Windows variables in macros

[Kathy Webster]

At the moment, I have macros that run the transfer text command.
The action argument File Name is set to c:\temporary_data.txt.
I need to change this location from c:\ to the user's application data
folder.
I understand the windows variable for this is %appdata%.
My question is how to put this in the File Name action argument of MSAccess
XP.

TIA,
Kathy

 

[Douglas J. Steele]

You can use the Environ function to retrieve that value: it would be
Environ("appdata").

To be really sure, though (since environment variables are trivial to reset
or delete), you'd be best off using VBA rather than a macro, and use the
SHGetSpecialFolderLocation API, as illustrated in
http://www.mvps.org/access/api/api0054.htm at "The Access Web"

 

[Kathy Webster]

Thank you. I referenced the article you recommended, but its way over my
head.
I have thus far accomplished everything through macros, so I don't know how
to write VBA, but I am brave, and good at following examples. Can you point
me to some examples, or hold my hand a little?

I'm assuming at that point in the macro I need to use the run code action to
find the value of the user's application folder, then somehow insert that
value into the File Name argument of the TransferText command.

Thanks again,
Kathy

 

[Douglas J. Steele]

Simply create a new module, copy everything between Code Start and Code End
into it and save it (don't name the module fGetSpecialFolderLocation:
modules cannot be named the same as procedures within them!)

You don't need RunCode: you simply put
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) where you want to know the
path (assuming that's the actual path you want: you may have to play with
some of the other constants to get the actual one you want).

 

[Kathy Webster]

Thank you. Could I somehow gather that value when the database launches,
like with an autoexec macro or something? Then somehow call on that value
whenever I need it? Since I'm not clear conceptually on what is happening,
sorry if I'm asking dumb questions.

 

[Douglas J. Steele]

There are a couple of options.

You could store it in a Public variable (although that's not a great
solution).

You could keep a form open at all times (say, your switchboard) and store
the value in a text box on that form. You could then refer to that text box
when you needed the value. (The text box needn't be visible. For that
matter, the form doesn't need to be visible either)

However, unless you're using the value an awful lot of times, simply calling
the function when you need it shouldn't be an issue.

 

[Kathy Webster]

Thank you. I *do* use the technique you describe quite a bit. I keep a lot
of values in my switchboard in non-visible fields. That is the way I have
been able to "work around" a lot of obstacles I come across (those obstacles
being my limited knowledge... )

I will be calling this value a lot, so I will store it there.
Now, I know how to get a value from a table or another form and put it in
that invisible field in the switchboard, but how do I get this
SpecialFolderLocation into that field? Again, I'm sorry if I'm dense, but
whenever I hear the word MODULE or CODE I break into a cold sweat!
-Kathy

 

[Douglas J. Steele]

One way is to set the control's ControlSource property to

=fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

(complete with equal sign)

Another is to use VBA in the form's Load event. Something like:

Private Sub Form_Load()

Me.MyTextBox = fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

End Sub

(where you replace MyTextBox with the name of the actual text box)

 

[Kathy Webster]

The first way seemed clean and easy. So I did that, I set the control's
ControlSource property to =fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA).
When I opened the form, I got a #Name? showing in the field.
I even tried making it the default value of the field, but got the same
result.
BTW, Access changed the string adding brackets to this:
=fGetSpecialFolderLocation([CSIDL_LOCAL_APPDATA])
-kathy

 

[Kathy Webster]

I tried your second way, the form's Load event, and got
"variable not defined" and it highlighted CSIDL_LOCAL_APPDATA.
Do I still need to paste all that between Code Start and Code Endfrom the
article you referenced? I'm sorry, I thought these were alternatives to that
code. Have I been even dumber than I thought?
-kathy

 

[Douglas J. Steele]

Yes, you need that code from between Code Start and Code End.

 

[Kathy Webster]

Well, help is needed here!

-I pasted the code from code start to code end in a new module and saved the
module as "UserPaths".

-I made a field in the switchboard and set its control source to
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

-The field is displaying #Name?

-I tried setting the default value of the field to
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA), but same result: #Name?

Sorry to be so needy!
-kathy

 

[John Vinson]

-I made a field in the switchboard and set its control source to
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

-The field is displaying #Name?

Use

=fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

This assumes that CSIDL_LOCAL_APPDATA is the name of a control on the
switchboard form which contains the information that
fGetSpecialFolderLocation needs.

John W. Vinson[MVP]

 

[Kathy Webster]

Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information. The
name of the field on the switchboard is [UserPathLocation]. I already tried
the equal sign, with the same #Name? result.

 

[John Vinson]

Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information. The
name of the field on the switchboard is [UserPathLocation]. I already tried
the equal sign, with the same #Name? result.

What is CSIDL_LOCAL_APPDATA? What datatype is it, what's its value,
where does that value come from, and what does the function expect?

John W. Vinson[MVP]

 

[Kathy Webster]

If you look back at this thread, you will see Doug Steele's references to
it, and the code where it came from. It should be the equivalent of the
c:\documents and settings\username\application data folder.

Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information.
The
name of the field on the switchboard is [UserPathLocation]. I already
tried
the equal sign, with the same #Name? result.

What is CSIDL_LOCAL_APPDATA? What datatype is it, what's its value,
where does that value come from, and what does the function expect?

John W. Vinson[MVP]

 

[Douglas J. Steele]

What did you name the module, Kathy?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information.
The name of the field on the switchboard is [UserPathLocation]. I already
tried the equal sign, with the same #Name? result.

Use

=fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

This assumes that CSIDL_LOCAL_APPDATA is the name of a control on the
switchboard form which contains the information that
fGetSpecialFolderLocation needs.

John W. Vinson[MVP]

 

[Kathy Webster]

Hi Doug. Glad you are back.
I named it UserPaths.
After pasting the code in, it seems to have added "Option Compare Database"
above the first commented line of the code.

What did you name the module, Kathy?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information.
The name of the field on the switchboard is [UserPathLocation]. I
already tried the equal sign, with the same #Name? result.

On Mon, 16 Oct 2006 17:38:33 GMT, "Kathy Webster"

-I made a field in the switchboard and set its control source to
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

-The field is displaying #Name?

Use

=fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

This assumes that CSIDL_LOCAL_APPDATA is the name of a control on the
switchboard form which contains the information that
fGetSpecialFolderLocation needs.

John W. Vinson[MVP]

 

[Douglas J. Steele]

What happens if you try to compile your application? (While you're in the VB
Editor, it's the first option on the Debug menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug. Glad you are back.
I named it UserPaths.
After pasting the code in, it seems to have added "Option Compare
Database" above the first commented line of the code.

What did you name the module, Kathy?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information.
The name of the field on the switchboard is [UserPathLocation]. I
already tried the equal sign, with the same #Name? result.


On Mon, 16 Oct 2006 17:38:33 GMT, "Kathy Webster"

-I made a field in the switchboard and set its control source to
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

-The field is displaying #Name?

Use

=fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

This assumes that CSIDL_LOCAL_APPDATA is the name of a control on the
switchboard form which contains the information that
fGetSpecialFolderLocation needs.

John W. Vinson[MVP]

 

[Kathy Webster]

I did it, it seemed to work. Now the option is greyed out.

What happens if you try to compile your application? (While you're in the
VB Editor, it's the first option on the Debug menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug. Glad you are back.
I named it UserPaths.
After pasting the code in, it seems to have added "Option Compare
Database" above the first commented line of the code.

What did you name the module, Kathy?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hmm, I'm trying to display the user apps folder name in a field on the
switchboard form, just to see if it is even receiving the information.
The name of the field on the switchboard is [UserPathLocation]. I
already tried the equal sign, with the same #Name? result.


On Mon, 16 Oct 2006 17:38:33 GMT, "Kathy Webster"

-I made a field in the switchboard and set its control source to
fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

-The field is displaying #Name?

Use

=fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA)

This assumes that CSIDL_LOCAL_APPDATA is the name of a control on the
switchboard form which contains the information that
fGetSpecialFolderLocation needs.

John W. Vinson[MVP]