Populate Listbox with filenames in Access97 - No additem 1

[PurpleUnicorn]

I have a piece of code from an Access2000 application that populates a list box with all files ending in .txt using the following code:

tempName = Dir("c:\test\*.txt&quot
Do While tempName <> &quot;&quot;
Me.lstTemp.AddItem (tempName)
tempName = Dir
Loop

I need to do the same thing in an application in Access97 but it seems that additem does not exist. How else can this be done?

Thanks

Nancy

 

[JBG]

There isn't an additem method in 97 for listboxes unfortunatley.

I am far from an expert, but I beleive you need to go to the properties of the listbox and use the rowsource type and row source properties. Cick in either and press F1 and you'll get a bit of a tutorial on how to add items to a list box in 97.

 

[biot023]

You need to set the listbox type to Value List then feed it with a string seperated by semicolons for each field. Eg:
&quot;a_file.txt;b_file.txt;c_file.txt&quot;
The following code should do it, but won't sort the files into order.

Dim lb_str as String

tempName = Dir(&quot;c:\test\*.txt&quot
Do While tempName <> &quot;&quot;
lb_str=lb_str & tempName & &quot;;&quot;
tempName = Dir
Loop

Me.lstTemp.RowSourceType=&quot;Value List&quot;
Me.lstTemp.RowSource=lb_str

You may also have to zap the trailing colon this code would place on lb_str - I'm not sure.

Douglas JL. If it don't make you laugh, it ain't true.

 

[PurpleUnicorn]

Douglas,

Thanks so much - your code worked perfectly. Now I am thinking that my client is probably going to want the list sorted. Hmmm...

Nancy

 

[biot023]

Something along these lines might work - if you create a temporary tabledef, populate that with files, then set the listbox to that table.

Dim tmp_tbl As TableDef
Dim fld As Field
Dim lb_str As String
Dim tempName As String

Set tmp_tbl = CurrentDb.CreateTableDef(&quot;tmp_Fields&quot
Set fld = tmp_tbl.CreateField(&quot;Fields&quot;, dbText, 255)
tmp_tbl.Fields.Append fld
tmp_tbl.Fields.Refresh
CurrentDb.TableDefs.Append tmp_tbl
CurrentDb.TableDefs.Refresh

tempName = Dir(&quot;c:\test\*.txt&quot
Do While tempName <> &quot;&quot;
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;INSERT INTO tmp_tbl (Fields) SELECT &quot; & Chr$(34) & tempName & Chr$(34) & &quot; AS Fields;&quot;
DoCmd.SetWarnings True
Loop

Me.lstTemp.RowSourceType = &quot;Table/Query&quot;
Me.lstTemp.RowSource = &quot;SELECT tmp_tbl.Files FROM tmp_tbl ORDER BY tmp_tbl.Fields ASC;&quot;

Then, when selections have been made, you could delete the no-longer-needed table with

CurrentDb.TableDefs.Delete &quot;tmp_Fields&quot;
CurrentDb.TableDefs.Refresh

This possibly isn't the prettiest way of doing it, but something like it should work (I haven't checked the code).

But it is a real pig not having AddItem, I have to admit.

Good luck!

Douglas JL If it don't make you laugh, it ain't true.

 

[PurpleUnicorn]

Thanks for responding again. I have figured it out (with a little help from various tek-tips members). I found an easy sort on another thread. I have included the procedure below.

Private Sub Form_Load()
Dim tempName As String
Dim lb_str As String
Dim aTempNames() As String
Dim i As Integer
Dim vcount As Integer

'Loads array with files .dot
tempName = Dir(&quot;d:\Directory_Name\templates\*.dot&quot
i = 0
Do While tempName <> &quot;&quot;
ReDim Preserve aTempNames(i + 1)
aTempNames(i) = tempName
tempName = Dir 'next filename
i = i + 1
Loop
vcount = i - 1

'sorts array
Dim iPass, iNdx, iCompVal As Integer
Dim strSwap As String

For iPass = 0 To vcount
For iNdx = 0 To vcount - 1
iCompVal = StrComp(aTempNames(iNdx), aTempNames(iNdx + 1))
If iCompVal = 1 Then 'returns 1 for str1 > str2
strSwap = aTempNames(iNdx)
aTempNames(iNdx) = aTempNames(iNdx + 1)
aTempNames(iNdx + 1) = strSwap
End If
Next
Next

'sets variable string with array items separated by ;
For i = 0 To vcount
lb_str = lb_str & aTempNames(i) & &quot;;&quot;
Next i

'populates list box with variable string
Me.lstTemplates.RowSourceType = &quot;Value List&quot;
Me.lstTemplates.RowSource = lb_str


End Sub

Nancy

 

[biot023]

Well I never knew about that!
Should make things a little easier for me, too!
Cheers,
Douglas If it don't make you laugh, it ain't true.