Full Version: Add items to listbox from VBA
UtterAccess Forums > Microsoft® Access > Access Forms
malcolmd83
I am using the following code to search for files within a directory, and then display the results in a number of textboxes.
!--c1-->
CODE
Dim strMask As String
Dim strFile As String
    
    strMask = "*.txt"
    
    strFile = Dir("g:\library files\" & strMask) 'Get first file
    
    Do While LenB(strFile) > 0 'If > 0 then we found file to match mask
        MsgBox ("Found " & strFile) 'display text box
        strFile = Dir() 'Get next file
    Loop

Is it possible to have the results added to a List Box on the form? I could then allow users to choose a file to open from the results (I hope!).
Thanks in advance for any help!
Jack Cowley
You can create a string and set that as the Row Source for the List box as long as the List boxes Row Source Type is set to Value List. Your string might look like: strFile = "File1;File2;File3:. Then use code similar to this:
e.ListBoxName.RowSource = strFile
hth,
Jack
DoubleD
Here's a function to create a list of values.
CODE
Function GetFiles() As String
  With Application.FileSearch
    .LookIn = "C:\PROD\"
    .Filename = "*.txt"
    If .Execute() > 0 Then
      For I = 1 To .FoundFiles.Count
        GetFiles = GetFiles & .FoundFiles(I) & ";"
      Next I
    Else
      GetFiles = "No Files Found;"
    End If
  End With
  GetFiles = Left(GetFiles, Len(GetFiles) - 1)
End Function

How put this line somewhere in your code when you want to populate the list box:
Me.lstBoxName.RowSource = GetFiles()
malcolmd83
That works great! Thank you so much for your help; I appreciate it.
malcolmd83
I've now expanded my search to a larger directory, and am now getting a Run-Time error '2176', "The setting for this property is too long". I've done a search on the 'net, and discovered this has something to do with the number of "search results", and a maximum size of the ValueList property. Is there another/better way to do what I am trying to do here?
DoubleD
You could populate a table with the values you return, then set your Listbox to source against that table.
malcolmd83
If I do that, I understand there is virtually no size limit, as the ListBox will be using a Table as a source, rather then a Value List. Sounds good...could you point me in the right direction as to how I can change the code you provided to write to a table rather then the ListBox directly? I did a search on the forums, but wasn't able to find the information I'm looking for.
Thanks again for your time!
DoubleD
Here you go.
No just make your rowsource = tblFileList
tblFileList should have one text field called FILE.
!--c1-->
CODE
Sub AddFiles()
Dim db As Connection
Dim rs As New ADODB.Recordset
Dim I As Integer
Set db = CurrentProject.Connection
db.Execute "DELETE * FROM tblFileList"
rs.Open "tblFileList", db, , adLockOptimistic
  With Application.FileSearch
    .LookIn = "C:\PROD\"
    .Filename = "*.xls"
    If .Execute() > 0 Then
      For I = 1 To .FoundFiles.Count
        rs.AddNew "FILE", Dir(.FoundFiles(I))
      Next I
    Else
      rs.AddNew "FILE", "No Files Found"
    End If
  End With
Set rs = Nothing
Set db = Nothing
Me.lstFileList.Requery
End Sub
malcolmd83
That does it! Thanks for your time in giving the information. The only question I have now is, for some reason the results which are returned show only the file name and not the full path. Is there a way to have the path included? I will be enabling "Search Sub Folders", so I can't just "add" a default path after. Thanks again!
malcolmd83
Never mind. I think I figured it out. I just changed:
CODE
          rs.AddNew "FILE", Dir(.FoundFiles(I))

to
CODE
        rs.AddNew "FILE", (.FoundFiles(I))

Thanks...
DoubleD
Yep, I thought you wanted only the FileName since you were using the Dir function earlier.
You got it! Have fun, if you need any more help just let me know.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.