External Document Selection in Access - & - VBA to rename external

I am working on a form where one of the checkboxes asks if there is a
document associated with the record? If you user clicks to check it
(i.e., "Yes"), I would like these events to occur:

1. A browser displays giving the user the chance to drill down to her
working directory and select the document. If she uses a common
directory it should automatically open to that directory in the
future.

2. When she clicks (i.e., selects) the document file she should be
asked if she wants to change the filename of the document to match the
users account number? If the user selects "yes" the filename should
be changed to the account number (e.g., 1234-080416.DOC). And a text
box (hyperlink) on the form should be updated to the location of the
document.

Concerning #1 above. Is there a method of drilling down to a file --
not to open the file, but to capture it's location to update a
hyperlink on your form?

Concerning #2 above. Is there a method using VB Scripting that can be
used to rename the external file name (e.g., MyLetter.DOC) to a field
value in the form)? (e.g. 1234-080416.DOC)

I appreciate any suggestions!

RBollinger

Re: External Document Selection in Access - & - VBA to rename

Without a lot of explanation (using Access 2003)

1. Is there a method of drilling down to a file --
not to open the file, but to write it's path to a
hyperlink on your form?

2. Related to #1 -- is there a method, using VB Scripting that can
be
used to rename an external file name. Example. You are at
account A27 on a form. You drill down to a document
(MyLetter.DOC) using the above method and click on the file. A
message-box should pop-up asking you: "Do you want to change
the name of this file to the account number?" If you select
yes, it renames MyLetter.DOC to A27.DOC.

Thanks for any help with this.

RBollinger

Re: External Document Selection in Access - & - VBA to rename

On Apr 17, 7:38 am, robboll wrote:
> Without a lot of explanation (using Access 2003)
>
> 1. Is there a method of drilling down to a file --
> not to open the file, but to write it's path to a
> hyperlink on your form?
>
> 2. Related to #1 -- is there a method, using VB Scripting that can
> be
> used to rename an external file name. Example. You are at
> account A27 on a form. You drill down to a document
> (MyLetter.DOC) using the above method and click on the file. A
> message-box should pop-up asking you: "Do you want to change
> the name of this file to the account number?" If you select
> yes, it renames MyLetter.DOC to A27.DOC.
>
> Thanks for any help with this.
>
> RBollinger

You can use the standard windows file open dialog to _select_ the
file. Details at

http://www.mvps.org/access/api/api0001.htm

Once you've done that, set your hyperlinked textbox to the path
returned by the dialog. Then prompt the user as to whether they want
to rename the file. If so, then use the Name statement to rename the
file. You'll want to use some string functions to parse out the path,
filename, and extension in order to rename it., i.e.
you'll want to retain the original path and extension and change only
the filename.

Bruce

Re: External Document Selection in Access - & - VBA to rename

Very nice. Thanks for pointing me in the right direction.

Re: External Document Selection in Access - & - VBA to rename

This works very well. Many thanks.

RBollinger