Listing a Directory in a Listbox

[IMO]

Hi All,
I'm using this code to view the contents of a directory in a listbox :

Private Sub Form_Open(Cancel As Integer)

Dim strFile As String, strRowSource As String
strFile = Dir("C:\MY FOLDER NAME\")
strRowSource = strRowSource & strFile
Do Until strFile = ""
strFile = Dir
strRowSource = strRowSource & strFile & ";"
Loop
Me.List20.RowSource = Left(strRowSource, Len(strRowSource) - 1)
End Sub

It works fine if there are files in the folder but if the folder is empty I get the following message :

Run-time error '5'
Invalid procedure call or argument.

Could someone tell me what I'm doing wrong.
Thanks in advance
IMO

 

[Jon K]

Pass the string to RowSource only if the string is not "":-

If strRowSource <> "" Then
Me.List20.RowSource = Left(strRowSource, Len(strRowSource) - 1)
End If

 

[IMO]

Thanks for your reply, it worked a treat. Don't know what I'd do without this site and the cool people who are willing to share their knowledge.
Cheers
IMO

 

[hangglide]

Pass the string to RowSource only if the string is not "":-

If strRowSource <> "" Then
Me.List20.RowSource = Left(strRowSource, Len(strRowSource) - 1)
End If

Thanks,
I used something simmilar to this code in a combo box and it works great. My code however compares the the file name to the recordID number and only displays the appropriate files:

eg:

If Left(strFile, 10) = Me.PermitNumber.Value Then
strRowSource = strRowSource & strFile & ";"
Else
End If

Now I have a new problem. How do I get the combo box to "update" when the focus moves to the next record? This code is sets the rowsource on the form when it opens. What should I change to get it to change for each record?

Here is all my code:

Private Sub Form_Open(Cancel As Integer)

Dim strFile As String, strRowSource As String

strFile = Dir("C:\Directory\")
If Left(strFile, 10) = Me.PermitNumber.Value Then
strRowSource = strRowSource & strFile & ";"
Else
End If

Do Until strFile = ""
strFile = Dir
If Left(strFile, 10) = Me.PermitNumber.Value Then
    strRowSource = strRowSource & strFile & ";"
Else
End If
Loop

If strRowSource <> "" Then
Me.comboFilesAvailable.RowSource = Left(strRowSource, Len(strRowSource) - 1)
End If

End Sub

 

[hangglide]

<---SNIP
Now I have a new problem. How do I get the combo box to "update" when the focus moves to the next record? This code is sets the rowsource on the form when it opens. What should I change to get it to change for each record?

Here is all my code:
SNIP--->

Okay...
I figured it out. I had to change the
Form_Open(Cancel As Integer) to Form_Current() and now it works great.

Leo

 

[JimH]

Old problem, new twist(?)

Hello,

I am using a form which I had got from this site to Add/Copy/Paste folders and files. I modified the form to suit my needs and everything was working fine. I brought the database and folder containing reference material to work and set the folder(s) up on my net vice standalone and am getting Run Time Error 2176 "The setting for this proerty is too liong".

I have already used the form time and time again on the target folder and everything worked on the standalone which lead me to beleive it does not like a newwork enviorment. Having searched this fourm on similiar 2176 errors they seem to have a common thread of the form lists XX number of lines and then stops. In this case, the listbox never populates and the error is immediatly produced. Code is provided. Debugger stops on the line bolded. Any thoughts? thanks!

Private Sub cmdListDirFiles_Click()

If Me.txtDir = "" Then
MsgBox "Please select a folder first..", vbCritical, "No Folder selected"
Call cmdChooseDir_Click
If Me.txtDir = "" Then
MsgBox "No folder selected. Please try again...", vbCritical, "No Folder selected"
Exit Sub
End If
End If

'Code adapted from
'http://www.access-programmers.co.uk/forums/showthread.php?t=40205
'Code credit goes to IMO & JonK

Dim strFile As String, strRowSource As String

strFolderName = Me.txtDir
strFile = Dir(strFolderName & "\")
strRowSource = strRowSource & strFile & ";"

Do Until strFile = ""
strFile = Dir
strRowSource = strRowSource & strFile & ";"
Loop

If strRowSource <> ";" Then
Me.lstDirFiles.RowSource = Left(strRowSource, Len(strRowSource) - 1)
Me.lstDirFiles.Enabled = True
Me.lstDirFiles.SetFocus
Else
Me.lstDirFiles.RowSource = "Folder is EMPTY"
Me.lstDirFiles.Enabled = False
Me.cmdChooseDir.SetFocus
End If

'***************I added this******************************************
Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
Set db1 = CurrentDb

Set rst1 = db1.OpenRecordset("tblRecentFolder", dbOpenTable)
While Not rst1.EOF And Not rst1.BOF
rst1.Edit
rst1("RecentFolder") = Me.txtDir
rst1.Update
rst1.MoveNext
Wend


End Sub