How do I use a parameter in a file name (path)?

[BJP]

I have a Access macro containing a Tranferspreadsheet step. How do I use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[Ken Snell MVP]

If the "parameter" value is found in a textbox or combobox or listbox on a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for the path
argument.

 

[BJP]

Thanks Ken, but I just want a prompt to enter the folder name and the file
name. C:\Documents and Settings\Administrator\My
Documents\TPA\OVNT\NEW\UPGFw090406.xlsx
where OVNT is replaced by something like 'Enter name of folder' and where
UPGFw090406 is replaced by something like 'Enter file name'. Now I run the
macro many times and for each time I have to edit the macro changing the
folder name and the file name. So instead of having to edit the macro each
time for the folder name and file name, I would just have to type in the
folder name and the file name.

Thanks again,
BJP
(e-mail address removed)

 

[Ken Snell MVP]

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
InputBox("Enter folder name:") & "\NEW\" &
InputBox("Enter filename:") & ".xlsx"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[BJP]

Thank you Ken for that quick response. Your solution works perfectly. Much
appreciated.

Thanks again,
BJP

 

[slickdock]

Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] & "application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

 

[Ken Snell MVP]

Show examples of user login names... any special characters in them? And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] & "application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

If the "parameter" value is found in a textbox or combobox or listbox on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[slickdock]

Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never any
special characters. However, I do notice that when I put that field in a
query, it shows up right justified for some reason. When I transfer that
field to msWord for other purposes, I have to put a CStr(format([wsLogin]) to
have the field transfer over and be understood by msWord.

Show examples of user login names... any special characters in them? And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] & "application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

If the "parameter" value is found in a textbox or combobox or listbox on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step. How do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[Ken Snell MVP]

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) & "application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never any
special characters. However, I do notice that when I put that field in a
query, it shows up right justified for some reason. When I transfer that
field to msWord for other purposes, I have to put a CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

Show examples of user login names... any special characters in them? And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] & "application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step. How do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[slickdock]

Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application data\tempfile\merge.txt is not
a valid path.

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) & "application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never any
special characters. However, I do notice that when I put that field in a
query, it shows up right justified for some reason. When I transfer that
field to msWord for other purposes, I have to put a CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

Show examples of user login names... any special characters in them? And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] & "application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step. How do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[Ken Snell MVP]

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user name. By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application data\tempfile\merge.txt is
not
a valid path.

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never any
special characters. However, I do notice that when I put that field in
a
query, it shows up right justified for some reason. When I transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters in them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step. How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[slickdock]

Bingo! Thanks for staying with me.
How do you know when to use ].[ instead of ]![ ?

Now that I've put us through all that, I started thinking of workarounds in
the interim. It occurs to me that I'd be safer to assign the %appdata% path
to this field, followed by \tempfile. Yes, this field is, as you guessed, a
field in the form's RecordSource. But maybe it doesn't have to be. It could
be unbound and have the default value of %appdata%\tempfile.

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user name. By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application data\tempfile\merge.txt is
not
a valid path.

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never any
special characters. However, I do notice that when I put that field in
a
query, it shows up right justified for some reason. When I transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters in them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step. How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[Ken Snell MVP]

In a form, all fields in the RecordSource are referenced only through the
dot notation because the fields are seen as part of the form's Properties
collection.

All controls can be referenced through either the bang or dot notation
because ACCESS automatically adds each control to the form's Properties
collection. When you have a field and control named the same, the use of the
dot notation can cause confusion as to which object (field or control) is to
be referenced.

You could use either a field or unbound textbox for the default path. If the
former, you could use the field's DefaultValue to assign the path. If the
latter, I'd set the ControlSource of the textbox to an expression that gives
you the default path, and I'd make the textbox invisible on the form.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Bingo! Thanks for staying with me.
How do you know when to use ].[ instead of ]![ ?

Now that I've put us through all that, I started thinking of workarounds
in
the interim. It occurs to me that I'd be safer to assign the %appdata%
path
to this field, followed by \tempfile. Yes, this field is, as you guessed,
a
field in the form's RecordSource. But maybe it doesn't have to be. It
could
be unbound and have the default value of %appdata%\tempfile.

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user name. By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application data\tempfile\merge.txt
is
not
a valid path.


:

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never
any
special characters. However, I do notice that when I put that field
in
a
query, it shows up right justified for some reason. When I transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters in
them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after
reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression
for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step.
How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[slickdock]

Thank you Ken. What you suggested is my usual technique...to set the
ControlSource of a textbox to an expression or value, making the textbox
invisible on the switchboard form, which I know is always open. Can you tell
me how I can set the textbox's default value to %appdata%\tempfile?

In a form, all fields in the RecordSource are referenced only through the
dot notation because the fields are seen as part of the form's Properties
collection.

All controls can be referenced through either the bang or dot notation
because ACCESS automatically adds each control to the form's Properties
collection. When you have a field and control named the same, the use of the
dot notation can cause confusion as to which object (field or control) is to
be referenced.

You could use either a field or unbound textbox for the default path. If the
former, you could use the field's DefaultValue to assign the path. If the
latter, I'd set the ControlSource of the textbox to an expression that gives
you the default path, and I'd make the textbox invisible on the form.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Bingo! Thanks for staying with me.
How do you know when to use ].[ instead of ]![ ?

Now that I've put us through all that, I started thinking of workarounds
in
the interim. It occurs to me that I'd be safer to assign the %appdata%
path
to this field, followed by \tempfile. Yes, this field is, as you guessed,
a
field in the form's RecordSource. But maybe it doesn't have to be. It
could
be unbound and have the default value of %appdata%\tempfile.

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user name. By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application data\tempfile\merge.txt
is
not
a valid path.


:

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken. Yes the form is open. The wsLogin name is Kathy. Never
any
special characters. However, I do notice that when I put that field
in
a
query, it shows up right justified for some reason. When I transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters in
them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access to
TransferDatabase to the %appdata% \tempfile folder. So after
reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression
for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step.
How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[Ken Snell MVP]

In design view of form, click on textbox and open Properties window. Go to
Data tab and click in Default Value box. Type your desired string in the
box.

I've not worked with %appdata% references; do you need to use that
literally? Or replace it with something?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you Ken. What you suggested is my usual technique...to set the
ControlSource of a textbox to an expression or value, making the textbox
invisible on the switchboard form, which I know is always open. Can you
tell
me how I can set the textbox's default value to %appdata%\tempfile?

In a form, all fields in the RecordSource are referenced only through the
dot notation because the fields are seen as part of the form's Properties
collection.

All controls can be referenced through either the bang or dot notation
because ACCESS automatically adds each control to the form's Properties
collection. When you have a field and control named the same, the use of
the
dot notation can cause confusion as to which object (field or control) is
to
be referenced.

You could use either a field or unbound textbox for the default path. If
the
former, you could use the field's DefaultValue to assign the path. If the
latter, I'd set the ControlSource of the textbox to an expression that
gives
you the default path, and I'd make the textbox invisible on the form.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Bingo! Thanks for staying with me.
How do you know when to use ].[ instead of ]![ ?

Now that I've put us through all that, I started thinking of
workarounds
in
the interim. It occurs to me that I'd be safer to assign the %appdata%
path
to this field, followed by \tempfile. Yes, this field is, as you
guessed,
a
field in the form's RecordSource. But maybe it doesn't have to be. It
could
be unbound and have the default value of %appdata%\tempfile.

:

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user name.
By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application
data\tempfile\merge.txt
is
not
a valid path.


:

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken. Yes the form is open. The wsLogin name is Kathy.
Never
any
special characters. However, I do notice that when I put that
field
in
a
query, it shows up right justified for some reason. When I
transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters in
them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access
to
TransferDatabase to the %appdata% \tempfile folder. So after
reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\"
&
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the
expression
for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step.
How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[slickdock]

I have to use that literally. I know how to set a default value in a textbox.
I do not know how to set that value to be =%appdata%.

In design view of form, click on textbox and open Properties window. Go to
Data tab and click in Default Value box. Type your desired string in the
box.

I've not worked with %appdata% references; do you need to use that
literally? Or replace it with something?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you Ken. What you suggested is my usual technique...to set the
ControlSource of a textbox to an expression or value, making the textbox
invisible on the switchboard form, which I know is always open. Can you
tell
me how I can set the textbox's default value to %appdata%\tempfile?

In a form, all fields in the RecordSource are referenced only through the
dot notation because the fields are seen as part of the form's Properties
collection.

All controls can be referenced through either the bang or dot notation
because ACCESS automatically adds each control to the form's Properties
collection. When you have a field and control named the same, the use of
the
dot notation can cause confusion as to which object (field or control) is
to
be referenced.

You could use either a field or unbound textbox for the default path. If
the
former, you could use the field's DefaultValue to assign the path. If the
latter, I'd set the ControlSource of the textbox to an expression that
gives
you the default path, and I'd make the textbox invisible on the form.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Bingo! Thanks for staying with me.
How do you know when to use ].[ instead of ]![ ?

Now that I've put us through all that, I started thinking of
workarounds
in
the interim. It occurs to me that I'd be safer to assign the %appdata%
path
to this field, followed by \tempfile. Yes, this field is, as you
guessed,
a
field in the form's RecordSource. But maybe it doesn't have to be. It
could
be unbound and have the default value of %appdata%\tempfile.

:

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user name.
By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application
data\tempfile\merge.txt
is
not
a valid path.


:

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken. Yes the form is open. The wsLogin name is Kathy.
Never
any
special characters. However, I do notice that when I put that
field
in
a
query, it shows up right justified for some reason. When I
transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters in
them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
Hi Ken,
I'm in a similar, yet simpler situation. I want to tell Access
to
TransferDatabase to the %appdata% \tempfile folder. So after
reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro. The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\"
&
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the
expression
for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet step.
How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[Douglas J. Steele]

The equivalent of %appdata% in Access would be Environ("appdata")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have to use that literally. I know how to set a default value in a
textbox.
I do not know how to set that value to be =%appdata%.

In design view of form, click on textbox and open Properties window. Go
to
Data tab and click in Default Value box. Type your desired string in the
box.

I've not worked with %appdata% references; do you need to use that
literally? Or replace it with something?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you Ken. What you suggested is my usual technique...to set the
ControlSource of a textbox to an expression or value, making the
textbox
invisible on the switchboard form, which I know is always open. Can you
tell
me how I can set the textbox's default value to %appdata%\tempfile?

:

In a form, all fields in the RecordSource are referenced only through
the
dot notation because the fields are seen as part of the form's
Properties
collection.

All controls can be referenced through either the bang or dot notation
because ACCESS automatically adds each control to the form's
Properties
collection. When you have a field and control named the same, the use
of
the
dot notation can cause confusion as to which object (field or control)
is
to
be referenced.

You could use either a field or unbound textbox for the default path.
If
the
former, you could use the field's DefaultValue to assign the path. If
the
latter, I'd set the ControlSource of the textbox to an expression that
gives
you the default path, and I'd make the textbox invisible on the form.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Bingo! Thanks for staying with me.
How do you know when to use ].[ instead of ]![ ?

Now that I've put us through all that, I started thinking of
workarounds
in
the interim. It occurs to me that I'd be safer to assign the
%appdata%
path
to this field, followed by \tempfile. Yes, this field is, as you
guessed,
a
field in the form's RecordSource. But maybe it doesn't have to be.
It
could
be unbound and have the default value of %appdata%\tempfile.

:

Then that suggests to me that [Forms]![aa]![wsLogin] is not a valid
reference to the form's control where you have stored the user
name.
By
chance, is wsLogin a field in the form's RecordSource? If yes, try
[Forms]![aa].[wsLogin] as the reference.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thank you Ken. I did try that before even bothering you. I get:
c:\documents and settings\Error 0\application
data\tempfile\merge.txt
is
not
a valid path.


:

Try this, then:

="c:\documents and settings\" & CStr([Forms]![aa]![wsLogin]) &
"application
data\tempfile\merge.txt"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Thanks Ken. Yes the form is open. The wsLogin name is Kathy.
Never
any
special characters. However, I do notice that when I put that
field
in
a
query, it shows up right justified for some reason. When I
transfer
that
field to msWord for other purposes, I have to put a
CStr(format([wsLogin])
to
have the field transfer over and be understood by msWord.

:

Show examples of user login names... any special characters
in
them?
And
perhaps obvious issue: is the form open when the macro runs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
Hi Ken,
I'm in a similar, yet simpler situation. I want to tell
Access
to
TransferDatabase to the %appdata% \tempfile folder. So
after
reading
your
advice, I tried this in the Filename argument:

="c:\documents and settings\" & [Forms]![aa]![wsLogin] &
"application
data\tempfile\merge.txt"

I'm getting a Type Mismatch error when I run the macro.
The
forms!aa!wsLogin field contains the user's login name.

I'd appreciate any help you can give.
TIA,
Kathy

:

If the "parameter" value is found in a textbox or combobox
or
listbox
on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My
Documents\TPA\"
&
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the
expression
for
the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a Access macro containing a Tranferspreadsheet
step.
How
do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.

 

[KKennedy]

I used these instructions to output a query to an .xls file, using the
InputBox function just for the filename, but Access returned error message
"can't save the output data to the file you selected" with 3 messages "file
may be open", "if you are using a template" and "if the file isn't open ...
disk space". I didn't have the file open (and did not choose to launch Excel
on the OutputTo, didn't use a template, and have more than enough disk space.
The macro opens the query, ouputs the query and closes. I've rearranged these
elements and have chosed different options, but always run up against this
error message. I am running Access 2000 SP-3, v. 9.0.8961 on Windows XP Pro.

 

[Ken Snell MVP]

Typo in my expression ( sigh, sorry 'bout that ) -- won't work when there is
an extra x at end of file extension:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
InputBox("Enter folder name:") & "\NEW\" &
InputBox("Enter filename:") & ".xls"

 

[KKennedy]

Oh, I caught the typo, but that wasn't the problem. I had spaces in the
command. Removing them worked. Oddly enough, the spaces didn't affect the
command when I tested it on a laptop w/ version 9.0.8968. Thanks for the help!