asked on

Upgrading from Access 2000 to 2007

Hi, I recently upgraded from MS Access 2000 to 2007. The database that was made in access 2000 had a command button that would allow the user to enter a parameter value based on the "Site_Name" field and all the records with the characters entered would be displayed. Alongside it would also open up the respective pdf files (with the same name as the "Site_Name") linked with each records "Site_Name".
Below is the code used to make it in access 2000. Using it in 2007, when I press the command button, type in the parameter value, it opens up the records in datasheet but is not opening the files. It says
"You entered an expression that has an invalid reference to the property FileSearch"
I am guessing the "application.filesearch" method is not available in MS Access 2007.
Can you please help.

Private Sub Command101_Click()
    Dim rs As DAO.Recordset
    Dim strPath As String
    Dim fs As FileSearch
    Dim i As Integer
    Dim stDocName As String
    
    On Error GoTo Err_Command101_Click:
 
    stDocName = "By Site Name"
    DoCmd.OpenForm stDocName, acFormDS
        
    strPath = "D:\SAP - Private Sites LA\"
    
    Set fs = Application.FileSearch
    Set rs = Forms(stDocName).RecordsetClone
    If Not rs.EOF Then
        rs.MoveFirst
    End If
    Do While Not rs.EOF
        With fs
           .NewSearch
           .LookIn = strPath
           .SearchSubFolders = True
           .FileName = rs![Site Name] & ".pdf"
           .FileType = msoFileTypeAllFiles
           If .Execute() > 0 Then
               For i = 1 To .FoundFiles.Count
                   FollowHyperlink .FoundFiles(i)
               Next i
           End If
        End With
        rs.MoveNext
    Loop
Exit Sub
 
Err_Command101_Click:
    MsgBox Err.Description
End Sub

Open in new window

frankytee

i've never used application.filesearch, i use the filesystem object (fso) instead, see my eg below
https://www.experts-exchange.com/questions/22763180/File-Explorer-Manipulation.html

fso is a commonly used object in vb, vba, vbscript etc

sahi0002

ASKER

Thanks a lot for your reply. I'm quite new to Access and weak with programming.
Can you please edit the code for me and how am I suppose to replace application.filesearch with filesystem object?
waiting for your reply :)

frankytee

i had to write this quickly. fso does not search subfolders elegantly so I had to code for subfolders.
also i noticed that you are looking for pdf files. fso does not open non text files, so you need to use windows shell and call the adobe program.
my code below does all this so copy and paste and modify accordingly.
you may need to modify the code below to match wherever your adobe exec is. i have adobe 7, you may have adobe 8 or 9 etc.

call this function from your command button
simply by :
call fnSearchSubfoldersAndOpenPdfFiles()

Function fnSearchSubfoldersAndOpenPdfFiles() As Boolean
    '=================================================================================
    'Frank Teng response to http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24127936.html
 
    '=================================================================================
    
    Dim fso As FileSystemObject, fold As Folder
    Dim subF As Folder
    Dim f As File
    
    Dim sFolder As String
    Dim sAdobe As String 'location of Adobe program
    Dim sFile As String
    Dim sFileToOpen As String
        
    '=================================================================================
    'whatever parent folder
    sFolder = "D:\SAP - Private Sites LA\"
    
    'whatever your pdf exe is located
    sAdobe = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe "
    '=================================================================================
    
    Set fso = New FileSystemObject
    Set fold = fso.GetFolder(sFolder)
    
    '-------------------------------------------
    'your variables to go thru form
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim stDocName As String
    '-------------------------------------------
        
 
    stDocName = "By Site Name"
    DoCmd.OpenForm stDocName, acFormDS
                    
    Set rs = Forms(stDocName).RecordsetClone
    If Not rs.EOF Then
        rs.MoveFirst
    End If
    Do While Not rs.EOF
        'search parent folder, assume that field [Site Name] does is file name only and excludes the path
        sFile = rs![Site Name] & ".pdf"
        For Each f In fold.Files
            If f.Name = sFile Then
                sFileToOpen = sFolder & sFile
                'to open pdf file you need to use shell object
                Call Shell(sAdobe & "" & sFileToOpen & "", 1)
            End If
        
        Next
                
        'search subfolders
        For Each subF In fold.SubFolders
               For Each f In subF.Files
                    If f.Name = sFile Then
                        sFileToOpen = sFolder & subF.Name & sFile
                        'to open pdf file you need to use shell object
                        Call Shell(sAdobe & "" & sFileToOpen & "", 1)
                    End If
               
               Next f
        Next
        
        rs.MoveNext
    Loop
        
    Set fold = Nothing
    Set fso = Nothing
 
End Function

Open in new window

sahi0002

ASKER

Thankyou so much for the effort.
I placed the code as you said but unfortunately its giving an error at line 60 "calll shell.." line
I think opening it as pdf is causing a lot of trouble. Instead of opening it in pdf, can you please make it open only the folders and sub folders.. sub sub folders with the same name as Site Name?
i would really appreciate it. I'm quite stuck :S

ASKER CERTIFIED SOLUTION

frankytee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sahi0002

ASKER

Ok perfect its working :)
Thanks a bunch. I think it was to do with the reference.
Problem solved