Populating listbox Rowsource by code, Limitations?

[GoinDeep]

Hello everyone, I need some help.

I'm using the attached .mdb file to populate the table "tblPhotoInventory" with .jpg file names from the path speceified in "txtpath".

The problem is I have over 600 files in each of the 250 paths"folders" the code will only allow me to populate 29 file names at once. This would be acceptable if I could find a way to start the next run from the last file name imported.

Any Suggestions, Thanks in advance!

 

[GoinDeep]

Pat,

I get the following error @
Me.lstAvailableItems.RowSource = Me.lstAvailableItems.RowSource & ";" & myarray(i):

Run-Time Error 2176.
The setting for this property is too long.

It must have something to do with the long path I am using?



Private Sub cmdCopyFiles_Click()
' Dimension variables.
Dim myarray()
Dim fs As Object
Dim i As Integer

' Declare filesearch object.
Set fs = Application.FileSearch

' Set folder to search.
'fs.LookIn = "R:\G-Uts-Common\System Integrity\Transmission\Photos\" & Me!cboSegNo
fs.LookIn = Me!txtPath
' Set file name to search for. This example assumes that you
' want to search for .jpg files.
fs.FileName = "*.jpg"

' Execute the file search, and check to see if the file(s) are
' present.
If fs.Execute > 0 Then

' Redimension the array to the number of files found.
ReDim myarray(fs.foundfiles.Count)

' Loop through all found file names and fill the array.
For i = 1 To fs.foundfiles.Count

myarray(i) = fs.foundfiles(i)

Next i

Else
' Display message if no files were found.
MsgBox "No files were found"
Me.lstAvailableItems.RowSource = " "
Me.lstAvailableItems.Requery
End If

' Loop through the array and fill the list box on the UserForm.
If fs.foundfiles.Count > 1 Then
Me.lstAvailableItems.RowSource = myarray(1)
For i = 2 To fs.foundfiles.Count
Me.lstAvailableItems.RowSource = Me.lstAvailableItems.RowSource & ";" & myarray(i)
Next i
Else
For i = 1 To fs.foundfiles.Count
Me.lstAvailableItems.RowSource = myarray(i)
Next i
End If

' Display the UserForm.
End Sub

 

[GoinDeep]

Thanks Pat,

Your correct I do not need the listboxes, I would prefer to populate the temp table directly from code but I am not having any luck using the array to complete this.

My Access help does not appear to have much info on recordset properties for tables. I am rather new to VBA code.

 

[GoinDeep]

I like it!. I completed my project earlier using the array in around about fashion but am studying the attached file to use on the next phase of the project.