link microsoft word title to a value in access

K

keeneeng

I have set up a database for inventory. I have a button setup in my
form but I don't know how to get it to do what i want. I have a report
number automatically generated by access and i also have a bunch of
files in a folder using that auto number as the title. I was wondering
if there was a way to write some code that did that following: on
click, the program would take the auto number from the current form,
and look in a default folder for a file with the title being the same
number as the current forms number and then have the file display on
the screen. I've been using hyperlinks but they take alot of time to
setup before the person can use the form.
 
S

SusanV

Sure - assuming the file is in C:\temp, use something like:

Dim filename As String
filename = "C:\temp\" & Me.YourAutonumberControl
Application.FollowHyperlink filename

If the file is in a shared folder, use the UNC:
Dim filename As String
filename = \\Servername\sharename\" & Me.YourAutonumberControl
Application.FollowHyperlink filename
 
E

enginerd477

I still didn't get it to work. i put in the following code:

Private Sub Open_report_Click()
On Error GoTo Err_Open_report_Click

Dim filename As String
filename = \\averill\public$\Engineering\Part_Database\" &
Me.Report#
Application.FollowHyperlink filename

End Sub

and got an error message "compile error: Expected: expression"

am i missing something in my code?

I've tried playing around with it but I haven't been able to get
anywhere with it.
 
S

SusanV

Hmmm, I use this a bunch and haven't had trouble. What is Me.Report#
resolving to? Does the Me.Report# include the file extension? If not, add
it:

Private Sub Open_report_Click()
On Error GoTo Err_Open_report_Click

Dim filename As String
filename = \\averill\public$\Engineering\Part_Database\" & Me.Report# &
".doc"
Application.FollowHyperlink filename

End Sub

Also, make sure you aren't breaking the middle of a line of code to a new
line in the VBE - the entire Application.followhyperlink line needs to be
unbroken.

If those don't help, do you know how to use the immediate window? Or you can
insert a breakpoint on the application.followhyperlink line, then mouseover
the filename variable to see what exactly is being populated.
Are you able to open the file by putting exactly the filename string into
Start>>Run?
 
R

Rob Parker

If you've cut/pasted directly for your code, the problem is probably a
missing " in the filename = ... line. Try:
filename = "\\averill\public$\Engineering\Part_Database\" & Me.Report#

Rob
 
E

enginerd477

Rob,

I added the quote but now whenever I try to run the button i get a
message saying my formats wrong with the "Report#" part highlighted.
so i made a new button and found that after you put in the period after
the me a scroll box appears with all these commands like "afterinsert"
"afterupdate" and so on.
 
R

Rob Parker

What exactly does the message say, and when does it appear? Is this in the
VBA editor? If so, it's probably that Report# is not the name of the
control on your form holding the filename portion of the report's name.
BTW, using the # character in field names, etc is not recommended.

I've just been re-reading the previous thread on this: If Report# is, as
you originally said, an autonumber which corresponds to the string which is
the filename of the external report, you may need
filename = "\\averill\public$\Engineering\Part_Database\" &
cstr(Me.Report#)

If your external files are named something like "1.doc", or "2247.txt"
(rather than just "1", or "2247"), you will need to append the file
extension to the end of the filename string you are building for the
FollowHyperlink method, in this manner:
filename = "\\averill\public$\Engineering\Part_Database\" &
cstr(Me.Report#) & ".doc"
(the last bit is the extension you are actually using - the files must all
have the same extension (unless you change your data setup to include the
full filename)).

If there is not a control named Report# on the form, but Report# is a field
in the form's recordset, you will need to refer to it as Me!Report#, rather
than Me.Report#

The scrollbox you refer to sounds like the "intellisense" helper in the VBA
editor. And, if Report# does not appear in that list, then it is not a
control on the form, and you will need the ! operator, rather than the .

HTH,

Rob

PS. snipping the existing thread in a new posting to that thread, when it is
relevant to what you're asking about, makes life much more inconvenient for
those attempting to assist you.
 
E

enginerd477

Yes it finally works. Thank you for the help. After adding the
cstr(Me!Report) it worked. i found my control was also just report as
well.

I was wondering if by changing the ".doc" to some other suffix would it
open up the document in the appropriate application.

thanks again for the help.
-----------------------------------------------------------------------------------------------------------------------------
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

enginerd477

Rob,

Thanks for the help. I just have one more question. I have multiple
data types like .doc and .JPG files and i was hoping to have the code
search through for and open all of the files with that name as once
because i have documents and JPEG with data that i would like to view
all at once. I'm new to programming in VBA but i've done other types
of programming.

i tried to do a if statement with an if else and then and else but
wasn't too successful with it. i was wondering what the syntax is that
i should be using for this or if there is a better way to do what i
want.

thanks again.

------------------------------------------------------------------------------------------------------------------------------
 
R

Rob Parker

It will certainly be possible to do that; however, I don't have any code
sample available to do so (it's not the sort of thing that people normally
want to do), and it's not exactly trivial. You'd need to open the desired
folder(s), retrieve all the filenames matching your file specification (in
this case, it would be something like cstr(Me.Report) & ".*") into an array,
then loop through the contents of that array and open each one. If you
really want to do that, I'd suggest you post that question in a new thread,
with a suitable descriptive subject line.

If you want some code examples to show you how to deal with opening files,
browsing folders, etc, try The Access Web, particularly the section on
APIs:
http://www.mvps.org/access/

There's lots of reference information, and links to other sites, at Jeff
Conrad's Access Junkie site:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Also Doug Steele's site:
http://www.accessmvp.com/djsteele/AccessIndex.html

And Allan Browne's site:
http://www.allenbrowne.com/

And don't forget the Access Help facility - except, perhaps, for A2000,
which seems to be remarkable for the amount of stuff that isn't there, and
is often cited as an excellent example of how a Help system should not be
;-). Browse the sections on programming in VBA to get a basic grasp of the
various contructs available - there's not much you can't do.

HTH,

Rob
 
Top