Button opening PDF File (2 Viewers)

[Douglas Post]

I have a Button that I want to press to open a PDF. The path to the PDF is constructed from a table called[Listings] that contains part of a link in a field called [Property Root Folder] and then add the end part of the link in another table called [Valuations] with the field [Valuation Link]. The two tables have a relationship with the common field[ListingID] The code I wrote does not work...

Private Sub BPO_Click() 
Dim r As Variant 
 
If Dir(Listings![Property Root Folder] & Me![Valuation Link], vbDirectory) "" Then 
   r = Shell("Explorer.exe " & Listings![Property Root Folder] & Valuations![Valuation Link]) 
End If 
 
End Sub

 

[Kiwiman]

Howzit

Try something like...

Private Sub BPO_Click() 
Dim r As Variant 
dim strRootPath as string

If Dir(Listings![Property Root Folder] & Me![Valuation Link], vbDirectory) "" Then 
    strRootPath = Dlookup("[Property Root Folder]","Listing","[ListingID]=" & me.listingid)
    strRootPath = strRootPath & "\" & me.[valuation link]

 debug.print strRootPath  ' what does this show??

    r = Shell("Explorer.exe " & strRootPath) 
End If 

End Sub

I would recommend against having spaces in any field \ table names as it makes it a lot harder to code vba and generally work with. Sooner or later problems will probably occur...

 

[mdlueck]

I would feed your concatenation configuration into a string variable, perhaps next do a Debug.Print of that string variable to see what concatenation you have actually arrived at.

If you want to launch the .PDF file and not actually have an explorer.exe window, perhaps just launch the .PDF file and allow the Windows file association rule to launch Acrobat, or what ever .PDF file viewer is installed?

It looks to me like you want Explorer to open defaulted to that specified directory, correct? If so, then use command line switches to explorer.exe as follows:

explorer.exe /e,"C:\My Dir\My Other Dir"

 

[boblarson]

A simple method is to build your path to a variable like:

Dim strPath As String
 
strPath = CurrentProject.Path & "\" & Me.MyControlWithFileName
 
Application.FollowHyperlink strPath

very simple and will use the default installed program for whatever file extension you have there.

 

[Douglas Post]

Ok, I think Kiwiman is on the right track with the Dlookup feature. But I still cannot get it correct.

The structure of the database:

DATA FROM THE FIRST TABLE TABLE: Listings
FIELD: Property Root Folder ***This is a path like "C:\6850 Dustin Circle\"
COMMON FIELD: Listing ID ***This is a Numeric Field)

DATA FROM THE SECOND TABLE WHICH FORM IS BASED ON TABLE: Valuations
FIELD: Valuation Link ***This is a file name like "10630 South 27th Street - BPO.PDF"
COMMON FIELD: Listing ID I modified the code to take into account a "\" is already in place and added "" on the If statement to stop if empty.

I understand that Dlookup is looking at the ListingID in the current record trying to search for it in the other table "Listings" and return "Property Root Folder" Here is what I have:

Dim r As Variant 
Dim strRootPath As String 
 
If Dir(Me![Valuation Link], vbDirectory) "" Then 
   strRootPath = DLookup("Property Root Folder", "Listings", "ListingID=Me.ListingID") 
   strRootPath = Listings![Property Root Folder] & Me.[Valuation Link] 
   Debug.Print strRootPath 
 
   ' what does this show?? 
   r = Shell("Explorer.exe " & strRootPath) 
End If

I press the button and nothing. I think I am not referencing the fields correctly but the concept is there.

 

[boblarson]

Also, this code:

If Dir(Listings![Property Root Folder] & Me![Valuation Link], vbDirectory) "" Then

is wrong as you need the red part:

If Dir(Listings![Property Root Folder] & Me![Valuation Link], vbDirectory) = "" Then

or you may need

If Dir(Listings![Property Root Folder] & Me![Valuation Link], vbDirectory) <> "" Then

depending on what you wanted.

Also, please fix your code when posting. What you've posted looks unreadable and I had to go fix your first post. Let's use some code tags here please and fix the code so it is readable.

 

[spikepl]

"It doesn't work" and "I cannot get it correctly" is informationless fluff, with no clues as to what the problem is.

Always be specific: what do you want the code to do, what does the code actually do, error messages, if any, and their location (which line).

BTW: Shell hates paths containing spaces, so when using Shell, paths should be wrapped in Chr(34) & MyPath & Chr(34)

 

[mdlueck]

Also, please fix your code when posting. What you've posted looks unreadable and I had to go fix your first post. Let's use some code tags here please and fix the code so it is readable.

Not so simple, Bob, the past few days. This forum system has been going nuts for me... The OP's post looks similar to my initial posts which then I must edit.

Thread on that topic here:
"Troubles with forums this week?"
http://www.access-programmers.co.uk/forums/showthread.php?p=1132462

 

[boblarson]

Not so simple, Bob, the past few days. This forum system has been going nuts for me... The OP's post looks similar to my initial posts which then I must edit.

If you encapsulate the CODE portion in the code tags BEFORE hitting the post button it WILL work. Really...

 

[Douglas Post]

I tried to reformat the post but something is messed up with the forum for me. None of the formatting options are available, server lag is tremendous and even when I reformat the post is doesn't accept it. I will run my post thru Frontpage and resubmit. Any ideas on why this is occurring? I am fully updated running Windows 7 and using the latest version of Firefox.

 

[mdlueck]

If you encapsulate the CODE portion in the code tags BEFORE hitting the post button it WILL work. Really...

No, it won't. For the past few days, wrapping the code with code tags will result in a code box with all of the code on a single line.

I must also use manual HTML BR tags to force line breaks... and and and... all documented in my question I pasted the link to above.

I am using manual BR tags in order to post in multiple paragraph's for example. Otherwise the post would all flow together as if it were on one line.

The forum system is some how sick.

 

[boblarson]

No, it won't. For the past few days, wrapping the code with code tags will result in a code box with all of the code on a single line.

Do like I do and paste into Notepad first and the copy and paste from that. I've yet to experience what you have described when I do that and I do that on a regular basis.

I must also use manual HTML BR tags to force line breaks... and and and... all documented in my question I pasted the link to above.

Don't know why I have not had any issues with it then. Maybe Portland Oregon is immune.

(I post with IE 8 and Google Chrome)

 

[mdlueck]

Do like I do and paste into Notepad first and the copy and paste from that.

Ja, me/2. Otherwise copy/paste transfers all of the rich formatting to the forum post. I use that technique as well.

I've yet to experience what you have described when I do that and I do that on a regular basis.

Consider yourself fortunate in that case.

As I am typing, the connection to the netdna caching server continues to be accessed, not all of the forum graphics download so I see Alt tag text, etc... YUCK!

(I post with IE 8 and Google Chrome.

I use the latest stable Mozilla Firefox, both on Windows and Linux.

 

[boblarson]

Can't explain what is happening but it could be tied to the type of browser and it would be interesting to note others who have been having issues as to which browser they are using.

And, for code I highly suggest NOT using Rich Text but plain text when posting. I've seen a much greater stability for having it stay formatted (line breaks, indenting, etc.) when using plain text and not rich text. Hence why in the 10 years + I've been on the forum, I've never had a problem when using it as such. I have had problems many times when not coming from Notepad.

 

[Kiwiman]

Howzit

I tried to reformat the post but something is messed up with the forum for me. None of the formatting options are available, server lag is tremendous and even when I reformat the post is doesn't accept it. I will run my post thru Frontpage and resubmit. Any ideas on why this is occurring? I am fully updated running Windows 7 and using the latest version of Firefox.

Yes there are some serious time lags on forum lately. Hitting the quote button just now - I had to wait the best part of a minute before this came back. Even then the formatting icons only show as red crosses, but still work. A little frustrating. This post does seem to be getting off topic though so I'll try and nudge it back

To get most of the formatting options you need to go into the Advanced section, where you will find the code tags quote tags etc.

stop        ' step through the code using the F8 button
' Build the file path first
strRootPath = DLookup("[Property Root Folder]", "Listings", "[Listing ID]=" & Me.[Listing ID])
    debug.print strRootPath     ' this will print in the immediate window what is currently in the variable 
   strRootPath = strRootPath & Me.[Valuation Link] 
   Debug.Print strRootPath     ' this will print in the immediate window what is currently in the variable 

' Check if file exitsts
if Dir(strRootPath) <> "" Then
   ' It does exist so open file
   r = Shell("Explorer.exe " & strRootPath) 
End If

 

[vbaInet]

But why over complicate matters when there's a simple method that can be used as proposed by Bob? What are you doing with the Task ID returned by Shell()? I guess nothing.

A simple method is to build your path to a variable like:

Dim strPath As String
 
strPath = CurrentProject.Path & "\" & Me.MyControlWithFileName
 
Application.FollowHyperlink strPath

very simple and will use the default installed program for whatever file extension you have there.

 

[Kiwiman]

Howzit

The problem he is facing is not what tool (shell or followhyperlink) to open the pdf with, it is building the string to make the file name. He has part of the file name in one table and the rest on his current form which is bound to a separate table. There is no guarantee that the first part is his currentproject path or if they are all consistent.

So whatever way he chooses he will need to build the filename string first, and with spaces in the field\ table names it is making it hard to work out where it is falling over - subject to getting the db of course.

 

[vbaInet]

Alright Kiwiman, I'm making reference to his follow-up post after Bob's FollowHyperlink suggestion which still uses the Shell command. I guess I should have quoted it.

Here are some DLookup() usage examples:

http://access.mvps.org/access/general/gen0018.htm

 

[mdlueck]

Can't explain what is happening...

The source of the troubles has been identified and resolved.

http://www.access-programmers.co.uk/forums/showthread.php?t=221732#post1132694

@Bob, do you happen to surf this site without allowing graphics to load? That might explain why you saw no impact.

 

[Douglas Post]

I have solved the problem by creating a Query that build the path and then using Dlookup with the query.

Private Sub BPO_Click()
Dim strRootPath As String

      strRootPath = Nz(DLookup("[Valuation Path]", "Valuation Path", "
[ListingID]&[ValuationID]=" & 
[ListingID] & [ValuationID]))
      
    Application.FollowHyperlink strRootPath

End Sub

Thanks for pointing me in the right direction with Dlookup. Sorry for the earlier formating issues but something was messed up with the forum which appears fixed now.