Variable that is automatically 0

[BruceM]

I have code that I borrowed from Allen Browne to loop through folder and
subfolder contents recursively. Actually, I adapted it to search for a
particular file that includes a specific text string in the file name. If
the file is not found I want to tell the user. Without getting into the
details of the code, the subfolders to be searched are added to a collection
for which I can determine the count. Each time through the code I increment
a variable. If the variable equals the subfolder count and the file is not
found I generate an informational message. If I do not increment the
variable the message appears after every subfolder.

My question is what is a good way to establish a variable that starts with
the value of 0 by default? I have this at the start of the procedure:

Dim varCount as Variant

I added a watch on varCount and noted that its value is Empty. From various
tests, including adding one to varCount and noting that its new value is
equal to one, it seems that varCount can be treated as a 0 initially, but I
don't know if this is reliable. I can't figure out how to assign the
variable an initial value without having it reset to that value each time
through the code, so I hope there is a reliable way to use the default
variable.

I have also seen:

Static intCount as Integer

which also seems to start at 0.

 

[Tom van Stiphout]

On Wed, 10 Dec 2008 08:21:54 -0500, "BruceM" <[email protected]>
wrote:

Don't use a variant. It's a sloppy data type best left for exceptional
situations. Use an integer instead, or a Long Integer if you expect to
go over 32000.
Don't use Static unless you need its features.

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

While I prefer explicitly setting the value, any numeric variable (Byte,
Integer, Long, Single, Double, Decimal and even Currency) will be
initialized to 0, any String variable will be initialized to a zero-length
string (""), any Boolean variable will be initialized to False (which is 0),
and any Variant variable will be initialized Null.

I'm not familiar with the exact sample of Allen's to which you refer, but is
there a reason why the variable needs to be declared as a Variant?

 

[BruceM]

Thanks for the reply, and thanks to Tom too. I will respond in this section
of the thread.

Originally I thought the variant variable would be initialized to Null and I
could use Nz to change it to 0. However, it shows up as Empty when I set a
watch on it. VBA Help seems to confirm this.

I can't explicitly assign a value to the variable because it resets each
time through the code. I will use an integer instead. Here is the code
from which I adpated mine:

http://allenbrowne.com/ser-59.html

The difference is that I exit the procedure as soon as the array contains a
value. It seemed clumsy to build an array that is limited to a single
element or value, but I could not find an alternative. Twice I posted on
that topic, but did not receive any replies, so since my clumsy system works
I abandoned for now the attempt to find something better. Since we're on
the subject I have posted my code below, minus error handling and with a
simplifed message box.

As I mentioned, I am using the code to find a particular file by using the
strFileSpec argument. The main form's record contains a value such as
08-22. Only one file name includes that string with a space before and
after it. The file naming convention is such that I can reliably specify
[FieldName] in the strFileSpec argument.


Public Function ListFiles(strPath As String, Optional strFileSpec As String,
_
Optional bIncludeSubfolders As Boolean)

' Purpose: List the files in the path.
' Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from
subdirectories of strPath as well.
' Method: FilDir() adds items to a collection, calling itself recursively
for subfolders.

Dim colDirList As New Collection

Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)

End Function


Public Function FillDir(colDirList As Collection, ByVal strFolder As String,
strFileSpec As String, _
bIncludeSubfolders As Boolean)

'Build up a list of files, and then add add to this list any additional
folders
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim lngCol As Long
Dim intCount As Integer

'Add the files to the list
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
If colDirList.Count > 0 Then
Application.FollowHyperlink (strFolder & strTemp)
Exit Function
End If
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop

'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName),
strFileSpec, True)
lngCol = colFolders.Count
If intCount = 0 Then
intCount = 1
Else
intCount = intCount + 1
End If
If intCount = lngCol And _
colDirList.Count = 0 Then
MsgBox "File not found"
End If
Next vFolderName
End If

End Function


Public Function TrailingSlash(varIn As Variant) As String

If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If

End Function


I call the code from a command button thus:

Dim colDirList As New Collection
Dim strPath As String, strFile As String

strPath = "\\ServerName\ShareName\FolderName\"
strFile = "* " & Me.FieldName & " *.doc"

Call FillDir(colDirList, strPath, strFile, True)

 

[jadeiitti]

Hi,
I tried to use what you wrote but without success.
But I need something similar.

How I can add some valueto show files in listbox or some textbox?
E.g. I have field [Number] with numbers. And this number (e.g. 0022038) is
including in a file name.
E.g. file names: 0022038.doc; COR-1LD-1871-1226-0022038.doc; COR-1LD-1871-
1226-0022038.pdf; COR1LD-1871-1226-0022038.doc.
How I can restrict a list only to files contain a value from form field
[Number]?
How I can list only file names without full path?
I tried this but it’s not working:
Call ListFiles(MyPath, "*" & Format(Me!Number, "0000000") & "*.doc", Me.
lstFileList)

Where

Dim cartella, Categoria As Variant
Dim, MyPath As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
MyPath = cartella & "\AD\DOC\" & Categoria & "\"

Thanks for your help.

Thanks for the reply, and thanks to Tom too. I will respond in this section
of the thread.

Originally I thought the variant variable would be initialized to Null and I
could use Nz to change it to 0. However, it shows up as Empty when I set a
watch on it. VBA Help seems to confirm this.

I can't explicitly assign a value to the variable because it resets each
time through the code. I will use an integer instead. Here is the code
from which I adpated mine:

http://allenbrowne.com/ser-59.html

The difference is that I exit the procedure as soon as the array contains a
value. It seemed clumsy to build an array that is limited to a single
element or value, but I could not find an alternative. Twice I posted on
that topic, but did not receive any replies, so since my clumsy system works
I abandoned for now the attempt to find something better. Since we're on
the subject I have posted my code below, minus error handling and with a
simplifed message box.

As I mentioned, I am using the code to find a particular file by using the
strFileSpec argument. The main form's record contains a value such as
08-22. Only one file name includes that string with a space before and
after it. The file naming convention is such that I can reliably specify
[FieldName] in the strFileSpec argument.

Public Function ListFiles(strPath As String, Optional strFileSpec As String,
_
Optional bIncludeSubfolders As Boolean)

' Purpose: List the files in the path.
' Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from
subdirectories of strPath as well.
' Method: FilDir() adds items to a collection, calling itself recursively
for subfolders.

Dim colDirList As New Collection

Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)

End Function

Public Function FillDir(colDirList As Collection, ByVal strFolder As String,
strFileSpec As String, _
bIncludeSubfolders As Boolean)

'Build up a list of files, and then add add to this list any additional
folders
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim lngCol As Long
Dim intCount As Integer

'Add the files to the list
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
If colDirList.Count > 0 Then
Application.FollowHyperlink (strFolder & strTemp)
Exit Function
End If
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop

'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName),
strFileSpec, True)
lngCol = colFolders.Count
If intCount = 0 Then
intCount = 1
Else
intCount = intCount + 1
End If
If intCount = lngCol And _
colDirList.Count = 0 Then
MsgBox "File not found"
End If
Next vFolderName
End If

End Function

Public Function TrailingSlash(varIn As Variant) As String

If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If

End Function

I call the code from a command button thus:

Dim colDirList As New Collection
Dim strPath As String, strFile As String

strPath = "\\ServerName\ShareName\FolderName\"
strFile = "* " & Me.FieldName & " *.doc"

Call FillDir(colDirList, strPath, strFile, True)

While I prefer explicitly setting the value, any numeric variable (Byte,
Integer, Long, Single, Double, Decimal and even Currency) will be

[quoted text clipped - 34 lines]

 

[BruceM]

Um, OK, I have been asking questions in this thread, so I may not have
answers. First of all, be specific when asking a question about something
that "does not work". Without more information I will not speculate. I
really don't understand all the details of the functions.

Number is a reserved word, I think, so I would start by putting square
brackets around the name.

Do you have a list box named lstFileList, configured as specified in the
link?

If this works except that it gives you the full path you should be able to
parse just the file name, but the first thing is to get it to work.

Hi,
I tried to use what you wrote but without success.
But I need something similar.

How I can add some valueto show files in listbox or some textbox?
E.g. I have field [Number] with numbers. And this number (e.g. 0022038) is
including in a file name.
E.g. file names: 0022038.doc; COR-1LD-1871-1226-0022038.doc; COR-1LD-1871-
1226-0022038.pdf; COR1LD-1871-1226-0022038.doc.
How I can restrict a list only to files contain a value from form field
[Number]?
How I can list only file names without full path?
I tried this but it’s not working:
Call ListFiles(MyPath, "*" & Format(Me!Number, "0000000") & "*.doc", Me.
lstFileList)

Where

Dim cartella, Categoria As Variant
Dim, MyPath As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
MyPath = cartella & "\AD\DOC\" & Categoria & "\"

Thanks for your help.

Thanks for the reply, and thanks to Tom too. I will respond in this
section
of the thread.

Originally I thought the variant variable would be initialized to Null and
I
could use Nz to change it to 0. However, it shows up as Empty when I set
a
watch on it. VBA Help seems to confirm this.

I can't explicitly assign a value to the variable because it resets each
time through the code. I will use an integer instead. Here is the code
from which I adpated mine:

http://allenbrowne.com/ser-59.html

The difference is that I exit the procedure as soon as the array contains
a
value. It seemed clumsy to build an array that is limited to a single
element or value, but I could not find an alternative. Twice I posted on
that topic, but did not receive any replies, so since my clumsy system
works
I abandoned for now the attempt to find something better. Since we're on
the subject I have posted my code below, minus error handling and with a
simplifed message box.

As I mentioned, I am using the code to find a particular file by using the
strFileSpec argument. The main form's record contains a value such as
08-22. Only one file name includes that string with a space before and
after it. The file naming convention is such that I can reliably specify
[FieldName] in the strFileSpec argument.

Public Function ListFiles(strPath As String, Optional strFileSpec As
String,
_
Optional bIncludeSubfolders As Boolean)

' Purpose: List the files in the path.
' Arguments: strPath = the path to search.
' strFileSpec = "*.*" unless you specify differently.
' bIncludeSubfolders: If True, returns results from
subdirectories of strPath as well.
' Method: FilDir() adds items to a collection, calling itself
recursively
for subfolders.

Dim colDirList As New Collection

Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)

End Function

Public Function FillDir(colDirList As Collection, ByVal strFolder As
String,
strFileSpec As String, _
bIncludeSubfolders As Boolean)

'Build up a list of files, and then add add to this list any additional
folders
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim lngCol As Long
Dim intCount As Integer

'Add the files to the list
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
If colDirList.Count > 0 Then
Application.FollowHyperlink (strFolder & strTemp)
Exit Function
End If
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop

'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName),
strFileSpec, True)
lngCol = colFolders.Count
If intCount = 0 Then
intCount = 1
Else
intCount = intCount + 1
End If
If intCount = lngCol And _
colDirList.Count = 0 Then
MsgBox "File not found"
End If
Next vFolderName
End If

End Function

Public Function TrailingSlash(varIn As Variant) As String

If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If

End Function

I call the code from a command button thus:

Dim colDirList As New Collection
Dim strPath As String, strFile As String

strPath = "\\ServerName\ShareName\FolderName\"
strFile = "* " & Me.FieldName & " *.doc"

Call FillDir(colDirList, strPath, strFile, True)

While I prefer explicitly setting the value, any numeric variable (Byte,
Integer, Long, Single, Double, Decimal and even Currency) will be

[quoted text clipped - 34 lines]

which also seems to start at 0.

 

[jadeiitti via AccessMonster.com]

Hi,
You are right. In asking we must be more specific.
But. I didn't get work your code on the beginning. After some correction it's
start open file but only one file. So it's no served my purpose.
Corrections:
in: " strFile = "* " & Me.FieldName & " *.doc" "
space with asterisk is deleted: strFile = "*" & Me.FieldName & "*.doc"

I found solution for my purpose.

Final code to see file with value using code of Allen Brown:

Purpose: to see if in directory are files with same value from field [Numero].

It’s a possibility to open each file to check which is corrected. After
verification, user saves the file in a place prepared to archive the file.
User writes file name in field [filename] and changes category in field
[Categoria] same with name of place of archive.

Private Sub Form_Current()
Dim cartella, Categoria As Variant
Dim strPath As String, strFileSpec As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
strPath = cartella & "\AD\DOC\" & Categoria & "\"

'''Verify if files are in directory

lstFileList.RowSource = ""
strFileSpec = "*" & Format(Me!Numero, "0000000") & "*.doc"
Call ListFiles(strPath, strFileSpec, , Me.lstFileList)

End Sub

The code of Allen Brown changed to show only name of file:

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strTemp
'colDirList.Add strFolder & strTemp '''original
strTemp = Dir
Loop

Added DblClick to ListBox.

Private Sub lstFileList_DblClick(Cancel As Integer)
Dim FileName As String
Dim FilePath As String
Dim FullName As String
Dim cartella As Variant
Dim Categoria As Variant

Me.lstFileList.Requery
cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
FileName = lstFileList
FilePath = cartella & "\AD\DOC\" & Categoria & "\"
FullName = FilePath & FileName

Shell "rundll32.exe url.dll,FileProtocolHandler " & FullName,
vbMaximizedFocus

End Sub

Um, OK, I have been asking questions in this thread, so I may not have
answers. First of all, be specific when asking a question about something
that "does not work". Without more information I will not speculate. I
really don't understand all the details of the functions.

Number is a reserved word, I think, so I would start by putting square
brackets around the name.

Do you have a list box named lstFileList, configured as specified in the
link?

If this works except that it gives you the full path you should be able to
parse just the file name, but the first thing is to get it to work.

Hi,
I tried to use what you wrote but without success.

[quoted text clipped - 157 lines]

 

[BruceM]

My code was intended to open a single file, since only one file should match
the criteria. In my case the spaces were necessary, but that is because of
how our files are named. In another situation these would be left out, as
you have found.

Hi,
You are right. In asking we must be more specific.
But. I didn't get work your code on the beginning. After some correction
it's
start open file but only one file. So it's no served my purpose.
Corrections:
in: " strFile = "* " & Me.FieldName & " *.doc" "
space with asterisk is deleted: strFile = "*" & Me.FieldName & "*.doc"

I found solution for my purpose.

Final code to see file with value using code of Allen Brown:

Purpose: to see if in directory are files with same value from field
[Numero].

It’s a possibility to open each file to check which is corrected. After
verification, user saves the file in a place prepared to archive the file.
User writes file name in field [filename] and changes category in field
[Categoria] same with name of place of archive.

Private Sub Form_Current()
Dim cartella, Categoria As Variant
Dim strPath As String, strFileSpec As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
strPath = cartella & "\AD\DOC\" & Categoria & "\"

'''Verify if files are in directory

lstFileList.RowSource = ""
strFileSpec = "*" & Format(Me!Numero, "0000000") & "*.doc"
Call ListFiles(strPath, strFileSpec, , Me.lstFileList)

End Sub

The code of Allen Brown changed to show only name of file:

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strTemp
'colDirList.Add strFolder & strTemp '''original
strTemp = Dir
Loop

Added DblClick to ListBox.

Private Sub lstFileList_DblClick(Cancel As Integer)
Dim FileName As String
Dim FilePath As String
Dim FullName As String
Dim cartella As Variant
Dim Categoria As Variant

Me.lstFileList.Requery
cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
FileName = lstFileList
FilePath = cartella & "\AD\DOC\" & Categoria & "\"
FullName = FilePath & FileName

Shell "rundll32.exe url.dll,FileProtocolHandler " & FullName,
vbMaximizedFocus

End Sub

Um, OK, I have been asking questions in this thread, so I may not have
answers. First of all, be specific when asking a question about something
that "does not work". Without more information I will not speculate. I
really don't understand all the details of the functions.

Number is a reserved word, I think, so I would start by putting square
brackets around the name.

Do you have a list box named lstFileList, configured as specified in the
link?

If this works except that it gives you the full path you should be able to
parse just the file name, but the first thing is to get it to work.

Hi,
I tried to use what you wrote but without success.

[quoted text clipped - 157 lines]

which also seems to start at 0.