Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Upgrading from Access 2000 to 2007

Posted on 2009-02-09
6
Medium Priority
?
434 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:sahi0002
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 23596748
i've never used application.filesearch, i use the filesystem object (fso) instead, see my eg below
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22763180.html

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

Author Comment

by:sahi0002
ID: 23597561
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 :)
0
 
LVL 19

Expert Comment

by:frankytee
ID: 23597876
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:sahi0002
ID: 23598050
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
0
 
LVL 19

Accepted Solution

by:
frankytee earned 2000 total points
ID: 23598395
have you checked the adobe string and modify if required? make sure you have the trailing space at the end.
sAdobe = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe "
it works fine when i tested it.
if you want open the folders then you would still need to use the shell. just beware that if more than one file is found in a folder then you'll be opening mutliple instances of that folder.

debug my original post , type
debug.print sAdobe & "" & sFileToOpen & ""
before the line
Call Shell(sAdobe & "" & sFileToOpen & "", 1)

in the FOR loop of the folder and subfolder. post the results here (hit Control + G to see the debug window)
also you need to ensure that a reference to the dll of the fso object has been added to your db. in the code module, go to Tools -> References and look for windows script host and check it.
   

dim sWin as string
 
sWin = "C:\WINDOWS\Explorer.exe"	'whatever location
....
 
'open parent folder if file found
For Each f In fold.Files
        If f.Name = sFile Then                
                'to open parent folder you need to use shell object
                Call Shell(sWin & "" & sFolder & "", 1)
        End If
        
Next
               
....
 
 
'open subfolder if file found
For Each subF In fold.SubFolders
               For Each f In subF.Files
                    If f.Name = sFile Then
                       ' sFileToOpen = sFolder & subF.Name & sFile
                        'to open subfolder you need to use shell object
                        Call Shell(sWin & "" & sFolder & subF.Name & "", 1)
                    End If
               
               Next f
Next

Open in new window

0
 

Author Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question