Press a button to combine two fields to open a PDF

[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, I think I am referencing the field incorrectly as I get a Run-time error '424' Object required....

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

[orange]

Why wopuld you open a pdf with explorer, why not
"C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe" or
where ever you have it located.

There is a sample by hansup (has more than you may need) at
http://stackoverflow.com/questions/6...encing-a-field

[Douglas Post]

I don't want to specify a certain program as sometimes it is a PDF and other times the file could be a JPG.

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 THE 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

This is what I have so far:

Private Sub BPO_Click()
   
Dim r As Variant
Dim strRootPath As String

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

End Sub

I receive no error and nothing appears to happen.

[orange]

I don't see how this will work

"ListingID=ListingID"

What do you think this statement does/will do?
If you are trying to get data from 2 tables, I'd suggest a query.

Does your Debug.print produce anything?

If you have a combo or other control in which you select or type a value, let's call it Me.ListingId
then your

strRootPath = DLookup("Listings![Property Root Folder]", "Listings", "ListingID=ListingID")

could become
strRootPath = DLookup("[Property Root Folder]", "Listings", "ListingID= '" & Me.ListingID & "'")

[Douglas Post]

I solved the issue by creating a query and then using Dlookup to find the value I combined in the query. So basically a two step process.

Thanks for putting me on the right track!

Private Sub BPO_Click()
Dim strRootPath As String

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

End Sub