Set command problem in legacy system

[ch875299]

All,

I have an older program that I have been asked make some updates to. I first wanted to make sure the current code was functioning so ran some tests. Things are working fine except for the code that imports new data into the database. When I attemp this I get a Run time error '5' invalid procedure call or argument. The code in question is:

Public Function GetDataFiles()
Set fs = New Scripting.FileSystemObject
Dim i As Integer

Set drCurrent = fs.Drives(Mid(Me.txtDataFolder.Value, 1, 1)) '"C"

*** Set flCurrent = drCurrent.RootFolder.SubFolders(Mid(Me.txtDataFold er.Value, 4, Len(Me.txtDataFolder.Value) - 4))
For Each fi In flCurrent.Files


i = i + 1
DoEvents
MsgBox i & ": " & fi.Name
Me.txtNumber.Value = i
Me.txtFilename.Value = fi.Name
'import the file into the AllStock table
ImportStockDataFromFile (fi)
DoEvents
Next
End Function


The line that the debugger highlights is noted by the ***. It also wraps around here but not in the actual code. Does anybody see anything that could be causing this? I would appriciate any suggestions. TIA

[Guus2005]

1 - Put "Option Explicit" at the top of the module and compile your application.
2 - When the debugger stops at ***, hovering over the various variables will show you which variable generates bogus information
3 - you might want to remove the space between "Me.txtDataFold" and "er.Value"

Enjoy!

[ch875299]

Thanks for the reply. I checked ther space and it is not in the actualy program so I guess it was a result of the cut and paste. I put the Option explicit in and checked the variables.
The flcurrent shows no value which would be right since the code appears to have an error. The other variables seem tohave correct values with the exception of Mid. When I hover over it I get nothing, however the Len gives me the 23 and the other variables have the valid file name with the full path. One other interesting note is that I ran this under access 2007 and it seems to run correctly. So I am a bit curious. Since this is not my code and the comments are sketchy at best I am slowly working my way through it. Thanks once again for your suggestion and if you see anything else, I'm here.

Craig

[Guus2005]

Did you compile your code?

Did you put a breakpoint in the function somewhere and then check the values by hovering over them or entering some code in the immediate window?

The variables fs, drcurrent, flCurrent are not dimensioned in the function. Don't know what type they are.

HTH

[ch875299]

HTH,

Thanks forthe elp. I did recompile. Here are the dfine statements at the beginning of the function:
Option Explicit
Private fs As Scripting.FileSystemObject
Private drCurrent As Scripting.Drive
Private flCurrent As Scripting.Folder
Private rf As Scripting.Folder
Private fi As Scripting.File
Private fl As Scripting.Folder

Does this giveyou any ideas?

TIA

Craig

[Guus2005]

To provide you with a meaningfull answer: please post a sample database describing the problem.

[Guus2005]

I've seen that your mid statement is flawed.
I've set Nz statement around your textbox.

Mid(Nz(Me.txtDataFolder, "C"), 4, Len(Nz(Me.txtDataFolder, "C")) - 4)

When the length of the contents of the txtDataFolder<=4 it results in an error message because you subtract 4.
Only when the length of the text in txtDataFolder exceeds 4 you don't get an error message.

HTH

[ch875299]

HTH

posted
Mid(Nz(Me.txtDataFolder, "C"), 4, Len(Nz(Me.txtDataFolder, "C")) - 4)

Thanks for the reply. I noticed you also changed the .Value to "C" I did try the change and still recieve the same error. As I said in the original post, this is some legacy code that supposedly was working. However, I think they might remember it incorrectly as I have found a few issues. I can send a zip file that includes the code and a small test DB and a file for testing the load function if you feel like going that far. Don't know if I can pot it here or if it would be better to send it direct e-mail.
Let me know what you want to do from here. Thanks again.

Craig

[ch875299]

HTH,


I have made a discovery that as long as the file is in a single file folder the program moves past this error. So that is an easy fix. So now can start debugging the actual import. The original file is a PRN file and will not import so I have also created a text file and am in the process of testing it. Thanks again for your help and I'll let you know what I find.

Craig