Access 2010 - Opening a Word Document from a Form - Attn: als315

1. I think you only need code in the command button.

2. As long as LableH is a control on your form you would not need to change that.

Also, this is how I open files using FollowHyperlink

Application.FollowHyperlink Me.Field1, , True

Select allOpen in new window

change Me.Field1 as neccessary

Thanks aebea --- Using your approach, do I need to replace Me.Field1 with a full path or just the relevant field name from a table with a hyperlink field?   Or something else?

Oh sorry, If the value in Me.Field1 has the full path to the file you want to open then you don't need to do anything else. However, if the full path is not in Me.Field1 you will need to build it.

What would be in Me.Field1?

Right now, I have the full path to a Word document in the first (and only) record of a one-field table.

Ok. So it sounds as long as the full path in in that control (Me.Field1) then you can use

Application.FollowHyperlink Me.Field1, , True

Select allOpen in new window

Let me know if you have issues with that and I will try to help.

BTW --- I'm just starting to learn VBA --- just a raw novice.  

Is the term 'Me' a part of standard VBA language?  If so, what does it signify or mean?

Thank you so much for your help?   WallyTee

'Me' refers to the form. So when you say 'Me.Field1', you're telling VBA to refer to the control titled 'Field1' on the form that your sub procedure is in.

Also, you can only use 'Me' if the sub procedure is in the form object. If you are calling a function outside of the form object you can't use 'Me', you would have to tell VBA which form you want to use.

Many thanks.  I believe I understand.  I'm going to try your approach and see if I can get it to work.  I need to break off now, but I'll be back here in one to two hours.   I really appreciate your help.   WallyTee.

No problem, WallyTee. We're here to help.

To aebea:  Still having difficulty.  Symptoms re in paragraph 8.  Here's the story:

1.  I have a one-field table.  Its name is tbl DB DESCRIPTION.
2.  The field is named DB Description.  Type is Hyperlink.
3.  Table has only one record.  Info in the one field is:
    C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc
4.  I have a form named: frm Open tbl DB DESCRIPTION hyperlink TEST
5.  The form has only one command button:  Command9  (Caption is called ‘Open’)
6.  In the Build Event (code option) for the button, I entered the following:
_____________________________________________________________________________
Option Compare Database
_____________________________________________________
Private Sub Command9_Click()
Application.FollowHyperlink Me.DB Description, , True
End Sub
______________________________________________________
Private Sub DB Description_Click()
End Sub
________________________________________________________________________________
7.  The property sheet for the button shows:

Selection Type: Command Button
Drop down menu:  Command9
Data Tab:  Enabled, Yes
Event Tab: On Click=[Event Procedure]

8.  When I click on the button in form view, I get an error message saying that the expression in On Click produced the following error: Invalid character.  The Help notes seemed to indicate that the program is looking for the NAME of a macro or event.  

Should I give the code a name?   Any other thoughts or suggestions?   Thanks again.  WallyTee

Also, the property sheet for the form shows the selection is "form" and the data source is the tbl DB DESCCRIPTION.  

Also, when I'm in table, in datasheet view, I can click on the record (the path) and the Word doc opens OK.

WallyTee,

I noticed you the control on your form is called 'DB Description'. And I assume that is a textbox or combobox.
Whenever you have a space in your control name or field name, you need to wrap the name in brackets [ ] in your code. This way VBA knows that everything inbetween the brackets is one name/item/control/field, etc.

Replace the code in your button with this...

Private Sub Command9_Click()
Application.FollowHyperlink Me.[DB Description], , True
End Sub

Select allOpen in new window

Also, am i correct in understanding that 'DB Description' is the name of the textbox/combobox on your form?

Hi again --- The only control on the form right now is the one command button I mentioned above.

'DB Description' is the name of the field in the table tbl DB DESCRIPTION.  

Question:  Should i be using a field/record in a table as the hyperlink?  Or can I somehow put the hyperlink in the form itself?

Just tried the brackets fix.  Did nothing else.  Same results.

If you are just starting out, now would be a good time to learn about using a naming convention, and apply the appropriate prefixes to objects such as forms and controls.  This makes your code much more readable.  See my Wikipedia article on the Leszynski Naming Convention for Access:

http://en.wikipedia.org/wiki/Leszynski_naming_convention

Thanks Helen.  Will do.    WallyTee

Ok, so 'DB Description' is the name of the field in your table. What is the name of the textbox/combobox on your form that holds the value of 'DB Description'?

The reason I ask is this:

Me.texboxName tells VBA to refer to the value in textBox name of the current form. You can't say Me.fieldInTable. If you want to get the value from the 'DB Description' of your table we might have to try another approach .

If 'DB Description' (which holds the path to your file) is not a value in any control on your form you will need to add a control to your form to hold that value, then you can use Me.nameOfControlThatHoldsDB_DescriptionValue.

Otherwise, you have to get the value from the table itself. In order to help you out in this case I would need to know the definition of the table [DB Description], ie (all the field names and data types).

I don't have a textbox/combo box on the form, therefore I don't have a name.  I only have one item (control??), i.e., the Command Button (Command9).

I'd be glad to put a text or combo box on the form if that will help.  I somehow thought that the button was all that was needed on the form.  I thought the button would activate the code and get to the table where the field DB Description with the hyperlink is located.

Is there a way to abandon the table and just use controls on the form --- supported by the VB code?

Thoughts?

I don't think there's a need to abandon the table, especially if its used in other processes or queries, etc. Also, its fine if there's not a control on your form that holds the value of your field in the 'DB Description' table. However, in order to get that value out of that table I'll need to know what the name of that field in that table is.  

Another question. How often will the path in the field of that table will change?

If that table is not being used by any other objects I think I have a solution for you.

1.  I have a one-field table.  Its name is tbl DB DESCRIPTION.
2.  The field is named DB Description.  Type is Hyperlink.
3.  Table has only one record.  Info in the one field is:
    C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc
4.  I have a form named: frm Open tbl DB DESCRIPTION hyperlink TEST
5.  The form has only one command button:  Command9  (Caption is called ‘Open’)

Also, the property sheet for the form shows the selection is "form" and the data source is the tbl DB DESCRIPTION.  

Also, when I'm in table, in datasheet view, I can click on the record (the path) and the Word doc opens OK.

Opening the Word doc is the only function of the table.

Also ,I don't foresee changing the path name once I put this DB into production.  And the table will not be used by other objects --- only the form we're discussing.   Tnx again.

WallyTee,

Thanks for your response. However, I need to know what the name of all the fields in your table, 'DB DESCRIPTION'.


"Opening the Word doc is the only function of the table." How often is the path to this word doc going to change? If the tables only function is to open this word doc, I would suggest doing this in code for your command button

'declare a string variable to hold the path of the word document
string wordDocPath as string
'populate the string variable with the full path of the word document
wordDocPath = 'enter the full path of the word document here'
'open the word document
Application.FollowHyperlink wordDocPath, , True

Select allOpen in new window

The table does not have any other fields.  It has only one field (hyperlink type) and one record, and I don't anticipate changing this structure if I stay with the table approach.  I designed it this way only because it was the only way I knew of to hyperlink to the Word doc from within the DB.  I then thought I could get to the table/hyperlink by using a command button on a form, which would make it easier for the ultimate users.

Your last code sample makes a lot of sense. I'll give it a try.  Do I need to put the path in brackets?  (Step 4)

Oops --- Step 6.   Brackets??

Ok. That's fine. But what is the name of the field in your table? I need to know that in order to look up the value that it holds. Also, this table will always only hold 1 record, yes?

"Oops --- Step 6.   Brackets??" <-- what happened at step 6? Did you get an error message? If so, what is the description or number of the error?

"Your last code sample makes a lot of sense. I'll give it a try.  Do I need to put the path in brackets?  (Step 4)" <<-- no, just wrap the path in quotations. Example, "c:\users\aebea\test.txt"

Got it !!!!   I think we're getting close.  Does this approach need the table at all?

If you have hard-coded the path into a string variable (as I suggest at Posted on 2012-04-10 at 12:22:39ID: 37829489), then no. And it sounds like you went with this approach, so, no. You do not need the table :-)

Same results.  Same error message.  I used the code below.  Do I need to change any property values?  

string wordDocPath as string
wordDocPath = “C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc”
Application.FollowHyperlink wordDocPath, , True

I notice we still have 'hyperlink' in the last line of code.  If we're bypassing the table, how does the program know how to find the hyperlink?   Or are we no longer using a hyperlink?

can you tell me what the error message and error number are?

Also, can you copy and paste the entire code including the function header of the button_click event?

that last line will treat any string after 'Application.FollowHyperlink' as a hyperlink. You should even be able to do this...

Application.FollowHyperlink “C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc”, , True

Select allOpen in new window

The error msg doesn't have a number.  Headline says, "The expression On Click you entered as the event property setting produced the following error: Invalid Character."

I l'll work on the copy/paste you requested right now.

This is the current code for "Command9"  (Note: be sure to see my P.S. after the code.)
_____________________________________________________________________________________

Option Compare Database
_____________________________________________________

Private Sub Command9_Click()
string wordDocPath as string
wordDocPath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"
Application.FollowHyperlink wordDocPath, , True

End Sub

________________________________________________________________________
Private Sub DB Description_Click()
End Sub

P.S.  I'm not sure what "the function header of the button_click event" is.   Is that something different from above?

Private Sub Command9_Click() and Private Sub DB Description_Click() are examples of function headers.

Ok, so what is the name of the button that you click on your form? Is it Command9 or is it 'DB Description'

Right now the name is Command9.   (caption is Open, but I don't think that matters, does it?)

MORE INFO ON ERROR MESSAGE:

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated.  For example, if the OnOpen property from a form is set =[Field], this error occurs because a macro or event name is expected to run when the event occurs.

OH MAN! My bad! I just realized I was mixing up c++ and VBA syntax when declaring that string variable!! haha

use this...it should work now :-)

Private Sub Command9_Click()
dim wordDocPath as string 
wordDocPath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"
Application.FollowHyperlink wordDocPath, , True
End Sub

Select allOpen in new window

NP.   Will do.

"Right now the name is Command9.   (caption is Open, but I don't think that matters, does it?)"

No, the caption is not of importance for what we're trying to do.

Tried it.  Copied and pasted your code and got same error.  Should I be doing anything in the property sheet, or checking any settings there?

Strange.

Have you tried debugging to see what line you're getting the error on. Do you know how to set breakpoints and step through code?

If not, within the VBA code editor,
1. place your cursor on the first line in the code
2. press F9, you should see that line turn red after this
3. Open your form and click your button
4. The code execution should pause at the redline
5. From there, press F8 to step through each line individually.
6. Do this until you get your error message then let me know what line causes the error.

thanks!

Will do.  Sorry I'm taking up so much of your time.  I may just be in over my head with the use of VBA.  i'm not a programmer and not even an advanced Access user.  I may have to reassess the overall approach.  I'm just trying to make it easy for the ultimate user to open a Word doc that describes the DB and provides a few instructions for its use.   I will try the debugging though before I give up.

It's ok. I'm willing to help. Opening a word document using application.followHyperlink is normally a really easy task to accomplish. I'm not sure why we're having so much trouble.

If you could and are willing, please provide me with the following:
- a screen shot of the properties for the Command9 button
- a screen shot of the events tab of the properties window for the Command9 button
- and (again) a copy of the Command9_Click() event code

First three steps went as you indicated.  When I clicked the button though, I immediately got the error message.  

1. place your cursor on the first line in the code
2. press F9, you should see that line turn red after this
3. Open your form and click your button  --- GOT ERROR MESSAGE HERE.

So you got the error when you clicked the button? Yes?

Also, did the execution ever hit your breakpoint in the Command9_Click() event? Or did it not get hit at all?

Re your 20:50:26ID: 37829984  --- I'll see what I can do.

re your 2012-04-10 at 20:53:58ID: 37830006

Yes, got error message as soon as I clicked the button.

I never did see the code step thru.  I only saw first line turn red when I pressed F9.

Ok. So that tells me there's nothing wrong with the code itself because execution errored out before it got to our code. This is where screen shots of the properties and event properties of the Command9 button will help me help you :-)

Here's your item 3.  I'm going to try to come up with the others now.  

Option Compare Database
_____________________________________________________

Private Sub Command9_Click()
Dim wordDocPath As String
wordDocPath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"
Application.FollowHyperlink wordDocPath, , True
End Sub

________________________________________________________________________
Private Sub DB Description_Click()
End Sub

try removing from your code or at least comment it out

Private Sub DB Description_Click()
End Sub

to comment it out just place an apostrophe in front of each line so it looks like

'Private Sub DB Description_Click()
'End Sub

I'm not good at screenshots (I'm ashamed to say), but I'm going to try.  In the meantime I'm looking at the property sheet for Command9.  The Event tab has only one entry, On Click.  It simply says [Event Procedure].  Similarly, the Data tab has only one entry; it says Enabled / Yes.

Other tab says Name: Command9, Default No and a few others that don't appear too consequential.

More to follow.

re your 2012-04-10 at 21:02:46ID: 37830049 ---- will do.

Tried it.  No help

Ok. The screen shots may not be needed. See my post from 2012-04-10 at 14:02:46ID: 37830049. Try what I suggested in that post. If it's still not working then I'll need the screen shots.

Also, a tip for creating screen shot:
holding down 'Alt' while pressing 'Print Scrn' will capture only the active window. This is good to do when you don't want to capture the entire screen, just the active window.

"The Event tab has only one entry, On Click.  It simply says [Event Procedure]. " <---what section of code does it take you to when you click on the event procedure for that event?

Re your 2012-04-10 at 21:14:58ID: 37830102

When I click on the ellipse to the far right I get this:  

Private Sub Command9_Click()
Dim wordDocPath As String
wordDocPath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"
Application.FollowHyperlink wordDocPath, , True
End Sub

And you removed /commented-out that other code?

Private Sub DB Description_Click()
End Sub

See if this works.  See attachment.

put this in your onClick event for the button. Let me know if any of the message boxes open when you click the button. I'm guessing they wont because it doesn't sound like the onClick is triggering properly.

Private Sub Command9_Click()
msgbox "success fully executed: Private Sub Command9_Click()"
Dim wordDocPath As String
msgbox "success fully executed: Dim wordDocPath As String"
wordDocPath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"
msgbox "success fully executed: wordDocPath = 'C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc'"
Application.FollowHyperlink wordDocPath, , True
msgbox "success fully executed: Application.FollowHyperlink wordDocPath, , True"
End Sub

Select allOpen in new window

re your 2012-04-10 at 21:23:18ID: 37830139

Yes.  Removed altogether.  No help.

"See if this works.  See attachment." no attachment added.

Also, i will have to wait till i get home to open an attachment.

Re your 2012-04-10 at 21:26:38ID: 37830153 ---- We just can't get a break.  I put the code below in the OnClick Event by clicking on the far right ellipsis which opened up the VAB Editor.   I got the same old error message.  
_____________________________________________________________________________

Option Compare Database
_____________________________________________________

Private Sub Command9_Click()
MsgBox "success fully executed: Private Sub Command9_Click()"
Dim wordDocPath As String
MsgBox "success fully executed: Dim wordDocPath As String"
wordDocPath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"
MsgBox "success fully executed: wordDocPath = 'C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc'"
Application.FollowHyperlink wordDocPath, , True
MsgBox "success fully executed: Application.FollowHyperlink wordDocPath, , True"
End Sub

Private Sub Form_Click()

End Sub

AGAIN ---- Re your 2012-04-10 at 21:26:38ID: 37830153 (AGAIN) --- I never saw any of the message boxes.  Got the error message as soon as I clicked on the one button on the form.   WallyTee.

What if you comment out the body of Private Sub Command9_Click()? Try commenting out all the code between Private Sub Command9_Click() and End Sub and see if you still get the error.

I think I have the problem solved.  Here's what I did:

I deleted the form and recreated it from scratch.  This time I used a text box instead of a command box.  In the property sheet sheet I made sure of the following:

1.  The data source for the form was tbl DB DESCRIPTION; (this is the one-field table that -- in its one and only record -- contains the hyperlink to the Word document.  The one field is named DB Description.)  
2.   In the Data tab, the source was DB Description.  
3.  The Name field in the 'Other' tab showed DB Description.  

I then went back to form view, clicked on the text box, and it worked!

Tomorrow I'll test it several times.  If I  see that it's stable, I'll close out the question.  Many, many thanks for your help thru this 'learning process.'  --- WallyTee  

P.S.  I'd still like to know why I had such a hard time with the button approach and such an easy time with the text box approach.

Good morning 'aebea' ------ I sure will give it a try.  I would much rather use a button than a text box because -- in my view -- they are more intuitive and more eye-appealing.   I have used buttons a number of times before, but never with hyperlinks or with VBA code.  So I'm learning.

I'll let you know how your latest suggestion works out.  I'll leave this question open until we get to the bottom of the problem.

I'm beginning to suspect that I just didn't have all the property settings right for the button approach.   Any thoughts on this angle?     WallyTee

Re your 2012-04-11 at 02:46:17ID: 37830927 and my 2012-04-11 at 11:03:29ID: 37831993.  I tried it and it works like a champ.  I really like the concept;  I notice that it does not rely on a dedicated table to hold the filePath as a hyperlink.   Just shows you my ignorance of the power of VBA.  

One last request.  If I  want to use a button --- but stick with the 'hard-code' approach -- is this what the code would look like?

Private Sub btn_openWordDoc_Click()
Dim filePath As String
filePath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"    
Application.FollowHyperlink filePath
End Sub

Cheers --- and thanks again.   WallyTee

Hi again my new friend and colleague.  I just tried my modified version of your latest code and it worked in 'hard-code' mode !!!  Yea !!!   The code is:

Private Sub btn_openWordDoc_Click()
Dim filePath As String
filePath = "C:\Wavecrest\LA DB TEST\REG AND QUOTES DB DESCRIPTION.doc"    
Application.FollowHyperlink filePath
End Sub

You sure 'brought me along' on this one.

WallyTee

Yes, I also think a button is a better, more intuitive approach. And I'm glad that you now have a button that does what you want! As to why it wasn't working before? It's hard for me to know without never have seeing the database itself - it could have been a number of things.

I'm a happy camper this morning.  Solved a problem with your super help and learned quite a bit along the way.   Life is good.  

Thanks again.  Hope to 'see' you again.

WallyTee

aebea was great.  He was very patient and persistent.  He taught me a lot (I'm a real novice at VBA) and came up with a super fix.

WallyTee, i'm glad you stuck around long enough to let me help you. I'm glad you didn't give up like you once mentioned because I would have felt like I failed you!