RUN TIME ERROR 2176 THE SETTING FOR THIS PROPERTY IS TOO LONG 2

[villica]

Hello everyone,

I have created a list box that is supposed lists all the excel files, but I am getting the above error.

Are they any other ways


Dim MyFile As String, Stsql As String

MyFile = Dir("O:\BACKLOG*.xls")
Stsql = MyFile & ";"
Do While Not (MyFile = "")

MyFile = Dir

Stsql = Stsql & MyFile & ";"
Loop

Me.cbolist.RowSource = Stsql

villica

 

[Remou]

It would seem that you have too many items in the directory and have exceeded the limits for the control.

 

[villica]

is there another way of doing the same thing

villica

 

[Remou]

Build the file list into a table and use that for your row source.

 

[PHV]

You may set the RowSourceType property of your control to a function.
Have a look at the VBA help for explanations about this feature.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[villica]

Hi Remou, how do you build the file list into a table. Sample please

villica

 

[Remou]

Something like:

Dim MyFile As String, strSQL As String

strSQL = "Delete * From [tblTableName]"
CurrentDb.Execute strSQL

MyFile = Dir(CurrentProject.Path & "\") 'Dir("O:\BACKLOG*.xls")
    
    Do While Not (MyFile = "")
  
    MyFile = Dir
        strSQL = "Insert Into [tblTableName] ( [FileName] ) Values ('"
    If Len(Trim(MyFile)) > 255 Then
        strSQL = strSQL & "Error: " & Left(Replace(MyFile, "''", "'"), 240) & "')"
    Else
        strSQL = strSQL & Replace(MyFile, "''", "'") & "')"
    End If
    CurrentDb.Execute strSQL

Loop
 
Me.cbolist.RowSource = "[tblTableName]"
End Sub

 

[villica]

Hi Remou, I am trying the code sample above, is the replace command access 97 or 2000. I am using access 97. I do not have access 2000 on my system so i can not verify.


villica

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4342

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[villica]

thank you so much Remou and PHV. I would like also to thank the person who wrote the replace function.

villica