Solved

Access Script not working in Access 2007 on Windows 7 64

Posted on 2011-09-06
8
212 Views
Last Modified: 2012-05-12
Greetings, I have a snippet of code that I wrote a while ago that looks at a directory and returns the folder contents into a temporary table, it's worked just fine until the new Windows 7 64 workstations try to run the code, now it's only returning the helpdesk.nal file that's on each users desktop.  Any help would be appreciated, script follows:

Private Sub cmd_inactive_Click()
Me.tgl_dwg = -1
'Start the Progress Meter
    Me.ProgressBar6.Visible = True
 
    Dim zzz As String
    Dim aaa As String
    Dim yyy As Integer
   
' Flag for canceled Dialog
    Dim bCanceled As Boolean
   
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant

    Dim strPath As String

   'Clear listbox contents.
    Me.filelist.RowSource = ""
   
‘Clear the canceled flag
    bCanceld = False

    ChDrive ("S:")
    ChDir ("S:\autocad\active")
   
' Changed Type to Folder Dialog
   'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
' Multiple disabled
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
           
' Dialog box retitled to specify directory.
      'Set the title of the dialog box.
     .Title = "Please select the active directory"

      'Clear out the current filters, and add our own.
      .Filters.Clear

' Filters Disabled
       '.Filters.Add "All Files", "*.*"
' Differentiation between Open and Cancel only      
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = -1 Then
               
' File selection removed
         
         'Loop through each file selected and add it to the list box.
                 
         'For Each vrtSelectedItem In .SelectedItems
          '  Me.filelist.AddItem vrtSelectedItem
'
'         Next
         
' Return the Chosen directory
         Me.filelist.AddItem fDialog.InitialFileName
         strPath = fDialog.InitialFileName
Else
         MsgBox "You clicked Cancel in the folder dialog box."
         bCanceled = True
         
      End If
   End With
If bCanceled = False Then
   
' Table clear to allow cancel
'first clear out temp table
    DoCmd.OpenQuery "del_DCN_tbl_temp"
    Me.ProgressBar6.Value = 1
' Open the text file for filenames
    Open "C:\DCN\OUTPUT.txt" For Output As #1
   
' Use the DIR() function to get the first filename
    Dim strFile As String
    strFile = Dir(strPath & "*.*")
   
' Loop and get the next file in directory
    ' Output to textfile
    Do While Len(strFile) > 0
        Print #1, strFile
        strFile = Dir()
    Loop
    Close #1

    Me.ProgressBar6.Value = 2

'Run a timer for 10 seconds to allow dir to finish

    Dim PauseTime, Start, Finish, TotalTime
   
        PauseTime = 10    ' Set duration.

        yyy = 3
       
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
            If yyy < 4500 Then
                Me.ProgressBar6.Value = yyy
                yyy = yyy + 1
            End If
        Loop
    Finish = Timer    ' Set end time.
    TotalTime = Finish - Start    ' Calculate total time.

    DoCmd.RunMacro "mac_dcn_import1"

End If ' canceled
       
   
    Me.tgl_dwg = -1
    Me.sub_temp.Requery
       
       
    Me.ProgressBar6.Visible = False
    Me.holder = 19 + Len(Me.activesearch)
End Sub
0
Comment
Question by:jscottcamp
  • 4
  • 4
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36491012
0
 

Author Comment

by:jscottcamp
ID: 36495983
Good link, I changed my Len() statements to LenB() and stepped through the code.
The problem appears to be specifically with this statement:      strFile = Dir(strPath & "*.*")
If i hardcode it to strFile = Dir(" s:\autocad\active\010\*.*")  it does return the list properly.
I know when I step through the value of strFile after I've selected the folder (I want returned) gives me the invalid data.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36496428
make sure you code takes into account if the trailing "\" is included or not.
0
 

Author Comment

by:jscottcamp
ID: 36522842
I'm afraid that writing a function of this complexity is currently outside my skill-set.
Has anyone seen a directory select function that I can copy?
I've copied (and modified) the module in the link but I'm not able to call the function properly.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36524191
Here is what I use.
http://www.ammara.com/access_image_faq/browse_for_folder_dialog.html
...works in xp SP1


I don't have a win 7 64 to test it on though
0
 

Author Comment

by:jscottcamp
ID: 36535637
I'll let you know what happens, i'm working on it now.
0
 

Author Closing Comment

by:jscottcamp
ID: 36536067
Worked perfectly, I'll have to poke around to see if I can get it to automatically choose a specified directory as a starting point but this just what I needed.

Thank you very much!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36536956
great
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now