HELP! Strange behavior in A2K3 Dir function

[Lauren Wilson]

Hi Folks,

We have a solid, five year old Access app that is suddenly behaving
oddly after conversion to Access 2002/2003 format. Everything seems
to run OK except for a few oddities. The code below is a test form I
created to demo the problem. It has a single text box and a command
button with the code below. It's function is to determine if a
particular folder exists or not. Please note that this code has
worked just fine for several years. NOW it throws a runtime error if
the folder does not exist.

Example: I have the following drives on my PC:

C: (Primary disk drive)
D: (Secondary disk drive)
E: (a DVD RO drive)
F: (a CDRW drive)

Both the C and the D drives have a folder called "LEADS"

The code below returns the word "LEADS" for both of those drives.
However, when I specify the path name "E:\LEADS" or "F:\LEADS", the
code below throws a Runtime Error 52: Bad file name or number".
It does this EVENT IF I SPECIFY: On Error Resume Next OR if I include
a case for Error 52 in the Error trap (like the sample below.

It NEVER did this before being converted to Access 2002/2003 format

Does anyone have a clue why this is happening -- and ESPECIALLY why
the Resume Next option does NOT trap this error?

=============== ========= BEGIN CODE =============== ========
Private Sub cmdIsPath_Click ()

Dim msg As String

On Error GoTo cmdIsPath_Click _Error

msg = Dir(Me.txtPath, vbDirectory)

MsgBox msg, vbInformation, "IsPath?"

'======= ERROR HANDLER =============== ========
ExitHere:
On Error GoTo 0
Exit Sub

cmdIsPath_Click _Error:
Select Case Err.Number
Case 52
Resume Next
Case Else
MsgBox "ERROR: " & Err.Number & " (Line: " & Erl & "): " &
Err.Description & _
" in procedure: cmdIsPath_Click of VBA Document
Form_AAAAA_TEST _DIR Function " & _
" ," & vbCritical, "Sorry. An error has occurred ..."
End Select
End Sub

[Wolf]

Lauren,
For all intents and purposes your code should work. Perhaps you have a
reference that is in "Missing" status? This can tend to cause strange
errors.

Also, I would like to suggest that if you are trying to determine if
the folder exists or not, that you use the "FolderExis ts" function of
the Microsoft Scripting Runtime, which returns a boolean true or false
if the folder exists.

Create a reference to the Microsoft Scripting Runtime, then create this
function;

Function IsFolder(strFol derName) as Boolean
Dim fso as FileSystemObjec t
Set fso = New FileSystemObjec t
IsFolder = fso.FolderExist s(strFolderName )
End Function

If you then call this function by using IsFolder("E:\LE ADS") it will
return true or false. It's nice too because you can use it all over
your application for any folder you like.

HTH!

[Wayne Morgan]

I just tried this in Access 2003 and it works fine. Since this happened
during an upgrade of the database, I'm going to guess that there is a
References problem. Does the old database work if you open it in Access 2003
without upgrading it?

For information on References, see this link:
http://www.allenbrowne.com/ser-38.html

--
Wayne Morgan
MS Access MVP
"Lauren Wilson" <no****@private .com> wrote in message
news:40******** *************** *********@4ax.c om...

Hi Folks,

We have a solid, five year old Access app that is suddenly behaving
oddly after conversion to Access 2002/2003 format. Everything seems
to run OK except for a few oddities. The code below is a test form I
created to demo the problem. It has a single text box and a command
button with the code below. It's function is to determine if a
particular folder exists or not. Please note that this code has
worked just fine for several years. NOW it throws a runtime error if
the folder does not exist.

Example: I have the following drives on my PC:

C: (Primary disk drive)
D: (Secondary disk drive)
E: (a DVD RO drive)
F: (a CDRW drive)

Both the C and the D drives have a folder called "LEADS"

The code below returns the word "LEADS" for both of those drives.
However, when I specify the path name "E:\LEADS" or "F:\LEADS", the
code below throws a Runtime Error 52: Bad file name or number".
It does this EVENT IF I SPECIFY: On Error Resume Next OR if I include
a case for Error 52 in the Error trap (like the sample below.

It NEVER did this before being converted to Access 2002/2003 format

Does anyone have a clue why this is happening -- and ESPECIALLY why
the Resume Next option does NOT trap this error?

=============== ========= BEGIN CODE =============== ========
Private Sub cmdIsPath_Click ()

Dim msg As String

On Error GoTo cmdIsPath_Click _Error

msg = Dir(Me.txtPath, vbDirectory)

MsgBox msg, vbInformation, "IsPath?"

'======= ERROR HANDLER =============== ========
ExitHere:
On Error GoTo 0
Exit Sub

cmdIsPath_Click _Error:
Select Case Err.Number
Case 52
Resume Next
Case Else
MsgBox "ERROR: " & Err.Number & " (Line: " & Erl & "): " &
Err.Description & _
" in procedure: cmdIsPath_Click of VBA Document
Form_AAAAA_TEST _DIR Function " & _
" ," & vbCritical, "Sorry. An error has occurred ..."
End Select
End Sub

[Lauren Wilson]

Thanks Wayne. I'll verify this. But last time I checked the refs,
there were none showing missing.
On Thu, 23 Jun 2005 21:46:25 GMT, "Wayne Morgan"
<co************ *************** @hotmail.com> wrote:

I just tried this in Access 2003 and it works fine. Since this happened
during an upgrade of the database, I'm going to guess that there is a
References problem. Does the old database work if you open it in Access 2003
without upgrading it?

For information on References, see this link:
http://www.allenbrowne.com/ser-38.html

[Lauren Wilson]

Thanks a LOT Wolf. I just KNEW there had to be a more elegant way to
do this, but, as you probably know, "when it ain't broke, we don't fix
it." This just became the squeaky wheel.

I will implement your solution immediately. However, I'm still bugged
about why the old approach worked for a long time and then stopped
working suddenly. I hate loose ends.
On 23 Jun 2005 14:43:37 -0700, "Wolf" <sp************ *@hotmail.com>
wrote:

Lauren,
For all intents and purposes your code should work. Perhaps you have a
reference that is in "Missing" status? This can tend to cause strange
errors.

Also, I would like to suggest that if you are trying to determine if
the folder exists or not, that you use the "FolderExis ts" function of
the Microsoft Scripting Runtime, which returns a boolean true or false
if the folder exists.

Create a reference to the Microsoft Scripting Runtime, then create this
function;

Function IsFolder(strFol derName) as Boolean
Dim fso as FileSystemObjec t
Set fso = New FileSystemObjec t
IsFolder = fso.FolderExist s(strFolderName )
End Function

If you then call this function by using IsFolder("E:\LE ADS") it will
return true or false. It's nice too because you can use it all over
your application for any folder you like.

HTH!

[Lauren Wilson]

Just confirmed it. All refs in place. Go figure.
On Thu, 23 Jun 2005 19:28:05 -0500, Lauren Wilson <no****@private .com>
wrote:

Thanks Wayne. I'll verify this. But last time I checked the refs,
there were none showing missing.
On Thu, 23 Jun 2005 21:46:25 GMT, "Wayne Morgan"
<co*********** *************** *@hotmail.com> wrote:

I just tried this in Access 2003 and it works fine. Since this happened
during an upgrade of the database, I'm going to guess that there is a
References problem. Does the old database work if you open it in Access 2003
without upgrading it?

For information on References, see this link:
http://www.allenbrowne.com/ser-38.html

[Wayne Morgan]

If that's the case, try unchecking one, click Ok, then go back and check it
again. This may "reset" the References. Remember which one you unchecked
because when you go back to check it again, it will be in alphabetical
order, not at the top with the checked references. You may not be able to
uncheck all of the references, so if you run across one that won't let you
uncheck it, just try another one.

Doing this should also decompile the code. When you recheck the reference,
click Ok, then go to Debug|Compile DatabaseName to recompile the code. Note
any errors that the compiler finds. This may give you a clue as to where to
look. Also, it is possible that the decompile/recompile will fix the problem
and you'll see no errors.

--
Wayne Morgan
MS Access MVP
"Lauren Wilson" <no****@private .com> wrote in message
news:t8******** *************** *********@4ax.c om...

Just confirmed it. All refs in place. Go figure.
On Thu, 23 Jun 2005 19:28:05 -0500, Lauren Wilson <no****@private .com>
wrote:

Thanks Wayne. I'll verify this. But last time I checked the refs,
there were none showing missing.
On Thu, 23 Jun 2005 21:46:25 GMT, "Wayne Morgan"
<co********** *************** **@hotmail.com> wrote:

I just tried this in Access 2003 and it works fine. Since this happened
during an upgrade of the database, I'm going to guess that there is a
References problem. Does the old database work if you open it in Access
2003
without upgrading it?

For information on References, see this link:
http://www.allenbrowne.com/ser-38.html

[Lauren Wilson]

On Fri, 24 Jun 2005 15:03:58 GMT, "Wayne Morgan"
<co************ *************** @hotmail.com> wrote:

If that's the case, try unchecking one, click Ok, then go back and check it
again. This may "reset" the References. Remember which one you unchecked
because when you go back to check it again, it will be in alphabetical
order, not at the top with the checked references. You may not be able to
uncheck all of the references, so if you run across one that won't let you
uncheck it, just try another one.

Doing this should also decompile the code. When you recheck the reference,
click Ok, then go to Debug|Compile DatabaseName to recompile the code. Note
any errors that the compiler finds. This may give you a clue as to where to
look. Also, it is possible that the decompile/recompile will fix the problem
and you'll see no errors.

Thanks again Wayne. I often use the /decompile command line option
for starting the mdb but I did not know that unchecking references
would force a decompile/recomplie.

This group is so awesome!

[Steve Jorgensen]

On Fri, 24 Jun 2005 14:38:37 -0500, Lauren Wilson <no****@private .com> wrote:

On Fri, 24 Jun 2005 15:03:58 GMT, "Wayne Morgan"
<co*********** *************** *@hotmail.com> wrote:

If that's the case, try unchecking one, click Ok, then go back and check it
again. This may "reset" the References. Remember which one you unchecked
because when you go back to check it again, it will be in alphabetical
order, not at the top with the checked references. You may not be able to
uncheck all of the references, so if you run across one that won't let you
uncheck it, just try another one.

Doing this should also decompile the code. When you recheck the reference,
click Ok, then go to Debug|Compile DatabaseName to recompile the code. Note
any errors that the compiler finds. This may give you a clue as to where to
look. Also, it is possible that the decompile/recompile will fix the problem
and you'll see no errors.

Thanks again Wayne. I often use the /decompile command line option
for starting the mdb but I did not know that unchecking references
would force a decompile/recomplie.

Yes, it does, but it's not as thorough. Sometimes, you still need the
/decompile.