refresh listbox using DirListBox

[FPS, Romney]

Hi,
I have DirListBox in a global module (see below)
I have a listbox on a form with recordsource = DirListBox
A command button on the form involves a procedure which concludes with a
Kill statement for the contents of the target folder specified in
DirListBox.
Once the Kill statement fires, I would like the listbox to be updated to
display as empty.
Can someone please tell me how to accomplish this?
Thanks,
Mark

Function DirListBox(fld As Control, ID, row, col, code)
' Purpose: To read the contents of a directory into a ListBox.
' Usage: Create a ListBox. Set its RowSourceType to "DirListBox"
' Parameters: The arguments are provided by Access itself.
' Notes: You could read a FileSpec from an underlying form.
' Error handling not shown. More than 512 files not handled.
Dim StrFileName As String
Static StrFiles(0 To 511) As String ' Array to hold File Names
Static IntCount As Integer ' Number of Files in list

Select Case code
Case 0 ' Initialize
DirListBox = True

Case 1 ' Open: load file names into array
DirListBox = Timer
StrFileName = Dir$("P:\_PriorAuth\") ' Read filespec from a
form here???
Do While Len(StrFileName) > 0
StrFiles(IntCount) = StrFileName
StrFileName = Dir
IntCount = IntCount + 1
Loop

Case 3 ' Rows
DirListBox = IntCount

Case 4 ' Columns
DirListBox = 1

Case 5 ' Column width in twips
DirListBox = 1440

Case 6 ' Supply data
DirListBox = StrFiles(row)

End Select
End Function

 

[FPS, Romney]

oops ... RecordSourceType = DirListBox

 

[PieterLinden via AccessMonster.com]

Clarification (I hope)...
1. The folder path is "hardwired" into the global module ... I want to
always display the contents of this one folder.
2. The problem is that the listbox retains the previous entries during the
same instance of the database. If I start with three files in the target
folder, then when I first open the database, and then the form, those three
files will be displayed (as intended). If I then delete one of those files
and requery the listbox, the two remaining files will indeed be added to the
listbox (also as intended), but tagged onto the three that were initially
displayed.

Original listbox contents:
File-1
File-2
File-3

<delete File-2; requery listbox>

New listbox contents:
File-1
File-2
File-3
File-1
File-3

Hi,
I have DirListBox in a global module (see below)

[quoted text clipped - 46 lines]

End Select
End Function

You need to reset the listbox's rowsource... set it to an empty string and
requery.
me.lbxFiles.RowSource = ""
me.lbxFiles.Requery

then your listbox won't have any files in it anymore...

 

[FPS, Romney]

Clarification (I hope)...
1. The folder path is "hardwired" into the global module ... I want to
always display the contents of this one folder.
2. The problem is that the listbox retains the previous entries during the
same instance of the database. If I start with three files in the target
folder, then when I first open the database, and then the form, those three
files will be displayed (as intended). If I then delete one of those files
and requery the listbox, the two remaining files will indeed be added to the
listbox (also as intended), but tagged onto the three that were initially
displayed.

Original listbox contents:
File-1
File-2
File-3

<delete File-2; requery listbox>

New listbox contents:
File-1
File-2
File-3
File-1
File-3

 

[Mark]

Getting close ... thanks, Pieter
....
with a slight modification (changing dot to bang & changing recordsource to
recordsourcetype) the listbox does empty:

me!lbxFiles.RowSourceType = ""
me!lbxFiles.Requery

.... the listbox is blank (great!)
.... however, if I follow this with:

me!lbxFiles.RowSourceType = "DirListBox"
me!lbxFiles.Requery 'to repopulate with the adjusted folder contents

.... I still get an accumulation of each time DirListBox was accessed along
with the newly adjusted contents.

DirListBox, evidently, is not being reset to nothing. Don't know if having
it in a global module is part of the problem.

Thanks,
Mark

Clarification (I hope)...
1. The folder path is "hardwired" into the global module ... I want to
always display the contents of this one folder.
2. The problem is that the listbox retains the previous entries during the
same instance of the database. If I start with three files in the target
folder, then when I first open the database, and then the form, those three
files will be displayed (as intended). If I then delete one of those files
and requery the listbox, the two remaining files will indeed be added to the
listbox (also as intended), but tagged onto the three that were initially
displayed.

Original listbox contents:
File-1
File-2
File-3

<delete File-2; requery listbox>

New listbox contents:
File-1
File-2
File-3
File-1
File-3

Hi,
I have DirListBox in a global module (see below)

[quoted text clipped - 46 lines]

End Select
End Function

You need to reset the listbox's rowsource... set it to an empty string and
requery.
me.lbxFiles.RowSource = ""
me.lbxFiles.Requery

then your listbox won't have any files in it anymore...