Solved: Command button to open document.

(Differences Noted in RED)

Can anyone tell me why if I use this code:
[VBA]Private Sub OpenIAG_Click()
On Error GoTo Err_OpenIAG_Click

Dim stAppName As String

stAppName = "explorer.exe " & "M:\Student_Files\My_Students\" & Forms!FrmStudentTracking.Form.Forename.Value & "_" & Forms!FrmStudentTracking.Form.Surname.Value & "\" & Forms!FrmStudentTracking.Form.Forename.Value & "_" & Forms!FrmStudentTracking.Form.Surname.Value & "_-_IAG.xls"
Call Shell(stAppName, 1)

Exit_OpenIAG_Click:
Exit Sub

Err_OpenIAG_Click:
MsgBox Err.Description
Resume Exit_OpenIAG_Click

End Sub[/VBA]to call a file (M:\Student_Files\My Students\Stephen Jones\Stephen Jones - IAG.xls) I get a dialogue box asking if I want to open, save or cancel; yet if I use this code

[VBA]Private Sub OpenIAG_Click()
On Error GoTo Err_OpenIAG_Click

Dim stAppName As String

stAppName = "excel.exe " & "M:\Student_Files\My_Students\" & Forms!FrmStudentTracking.Form.Forename.Value & "_" & Forms!FrmStudentTracking.Form.Surname.Value & "\" & Forms!FrmStudentTracking.Form.Forename.Value & "_" & Forms!FrmStudentTracking.Form.Surname.Value & "_-_IAG.xls"
Call Shell(stAppName, 1)

Exit_OpenIAG_Click:
Exit Sub

Err_OpenIAG_Click:
MsgBox Err.Description
Resume Exit_OpenIAG_Click

End Sub[/VBA]I get the message:

'M;\Student_Files\My_Students\Stephen_Jones\Stephen_Jones_-_IAG.xls' could not be found.

And if I remove the Underscores the message is similar but is broken at every space giving me 7 error messages.

Any ideas, folks?!

Cross posted here

I don't know, but why not just "FollowHyperlink" to open the files?

FollowHyperlink would be the simplest way... but here's some alternate code for opening a document via Word:

[VBA]Dim objWord As Word.Application
Dim objDoc As Word.Document

Set objWord = New Word.Application
objWord.Visible = True
Set objDoc = objWord.Documents.Open(strFilePath)

'code to close and release
objWord.Application.Quit False
Set objDoc = Nothing
Set objWord = Nothing[/VBA]

Quote:

---

Originally Posted by OBP

I don't know, but why not just "FollowHyperlink" to open the files?

---

How would I be able to change the path of the hyperlink based on the forname and surname fields in the form? What would the coding be then?

Quote:

---

How would I be able to change the path of the hyperlink based on the forname and surname fields in the form? What would the coding be then?

---

It would be done the exact same way you are doing it above.

The code would be something like:
[VBA]Dim strFilePath As String

strFilePath = "M:\Student_Files\My_Students\" & Forms!FrmStudentTracking.Form.Forename.Value _
& "_" & Forms!FrmStudentTracking.Form.Surname.Value & "\" & Forms!FrmStudentTracking.Form.Forename.Value _
& "_" & Forms!FrmStudentTracking.Form.Surname.Value & "_-_IAG.xls"

Application.FollowHyperlink strFilePath,,True
[/VBA]

Question about your code- is FrmStudentTracking the name of the form that this code is behind? If it is, then you can replace all of this:
"Forms!FrmStudentTracking.Form"
with this:
"Me."

Me is a keyword that refers to the current, active form that the code is behind. It's a shortcut that allows you to skip the longhand form reference. It would simplify your code a great deal, to this:

[VBA]Dim strFilePath As String

strFilePath = "M:\Student_Files\My_Students\" & Me.Forename _
& "_" & Me.Surname & "\" & Me.Forename & "_" & Me.Surname & "_-_IAG.xls"

Application.FollowHyperlink strFilePath,,True
[/VBA]

Also, when you are referencing the value of standard objects, like a textbox or combobox, you don't have to use .Value at the end of the object's name. Just referenecing the object with "Me.ObjectName" will automatically pull the object's value. It's another shortcut that can cut out a few unneccessary keystrokes.

HTH:thumb

Quote:

---

Originally Posted by CreganTur

Question about your code- is FrmStudentTracking the name of the form that this code is behind? If it is, then you can replace all of this:
"Forms!FrmStudentTracking.Form"
with this:
"Me."

Me is a keyword that refers to the current, active form that the code is behind. It's a shortcut that allows you to skip the longhand form reference. It would simplify your code a great deal.

---

:doh::doh:Slaps Head repeatedly :doh::doh: both at his stupidity, and at the simplicity.
Only been learning Access for 2 weeks, but have been using VBA for over a year - so, no excuses for it! DOH :bug:

Thanks for the info OBP and CreganTur - always so much to learn - GOODY!:cloud9:

Quote:

---

:doh::doh:Slaps Head repeatedly :doh::doh: both at his stupidity, and at the simplicity.
Only been learning Access for 2 weeks, but have been using VBA for over a year - so, no excuses for it! DOH :bug:

---

Don't stress about it- Access is a very different environment than other office products, with a very unique object model. It takes time to learn all the shortcuts.

Quote:

---

Originally Posted by CreganTur

Don't stress about it- Access is a very different environment than other office products, with a very unique object model. It takes time to learn all the shortcuts.

---

Not stressed :motz2: - LOVE the challenge! :thumb


NARF! :hi:

Please note - another solution was posted here, on the cross post.

Thanks to all for the help.

The cross posted version is a well known method of getting the data, it also works with Excel.
I have a Document management database that uses that method and others to do exactly that, I have posted a few versions of it on this and another forum.