Converting a foundfile string to a text box string

[shredder249]

Hi, i have a form which displays records containing a filepath field. When a button is pressed all the filepath fields are checked and the form is refiltered to display only those with invalid file paths. Upon pressing a second button each file is searched for on the computer. Currently all the matches are just written to a list box. What i want to happen is if there is only one match then that match's file path is written to the record's file path field. I have tried to write code for this but does not work:

                If fs.Execute() = 1 Then
 
                    URL = fs.FoundFiles
 
                End If
 

When this segment of code is run i get an error message: "The value you entered isn't valid for this field". I am guessing that this is because the text stored in the .foundfiles property is of the wrong type. Is there a way of outputting the file path found by the search to the url textbox either from the foundfiles property itself or the list box?

[JKing]

Hi there,

The FoundFiles property returns an object. It's basically an array of filepaths. Therefore it is indexed and if you want to get only a single filepath you will need to specify an index.

fs.FoundFiles(index)
 

Where index is any number from 0 to FoundFiles.Count - 1.

There are several ways you could do a check to see if only 1 file is returned. The count property would be great for this.

If you would like to see a more detailed example or more information about the FoundFiles property have a look in the help files.

[shredder249]

Cheers JKing, that part of the problem is now fixed. As a follow-problem I said if there were multiple files found I wanted the file paths added to a list box. I kind of managed to do this but have a problem. I set up the form so each record has a list-box. The list box isn't attached to a field or anything its just there for when there are multiple search results. The problem I'm having is that if for example a search of five files produced two matches of each file, I get all 10 results displayed in each list box on each record. I want it so only the multiple matches of each file are displayed in it's respective record, not all the matches of all the files. Is this happening because the list box isnt attached to a proper field?

Hi there,

The FoundFiles property returns an object. It's basically an array of filepaths. Therefore it is indexed and if you want to get only a single filepath you will need to specify an index.

fs.FoundFiles(index)
 

Where index is any number from 0 to FoundFiles.Count - 1.

There are several ways you could do a check to see if only 1 file is returned. The count property would be great for this.

If you would like to see a more detailed example or more information about the FoundFiles property have a look in the help files.

[JKing]

If I'm understanding you correctly you want the listbox to only display the returned values of the current search and not accumulate the results from previous searches.

If this is the case you need to clear the data out of the listbox each to you add new data.

If you post your code that you are using to manipulate the listbox I can give you some more detail as how to go about doing this.

[shredder249]

 
 
Do Until Form.CurrentRecord = CountDisplay
 
            'slashfrontpos = InStrRev(URL, "\")
            'URLLength = Len(URL)
            'slashbackpos = URLLength - slashfrontpos
            'Extract = Right(URL, slashbackpos)
 
            'pointfrontpos = InStrRev(URL, ".")
            'URLLength = Len(URL)
            'pointbackpos = URLLength - pointfrontpos
            'Extension = "*." + Right(URL, pointbackpos)
 
            If Drive = "" Then
                Drive = Left(URL, 3)
            End If
 
            fs.NewSearch
 
            With fs
 
                .LookIn = "C:;D:"
                .SearchSubFolders = True
                .filename = Extract
                .MatchTextExactly = True
                .Filetype = msoFileTypeAllFiles
 
            End With
 
            If fs.Execute > 0 Then
 
                'If only 1 match was found, amend record, if >1 add file paths to list box
                If fs.FoundFiles.Count = 1 Then
 
                    URL = fs.FoundFiles(1)
                    ValidPath = -1
                    corrected = corrected + 1
 
                Else
 
                    For i = 1 To fs.FoundFiles.Count
                    SearchResults.AddItem fs.FoundFiles(i)
                    Next i
 
                    multiple = multiple + 1
 
                End If
 
            Else
 
                none = none + 1
 
            End If
 
            'If the current record number = total number of records with invalid file paths
            'exit sub, if not then go to next record
            If Form.CurrentRecord = TotalInvalid Then
 
                If corrected > 0 Then
 
                    If corrected = 1 Then
                        MsgBox corrected & " music file had it's file path corrected automatically."
                    Else
                        MsgBox corrected & " music files had their file paths corrected automatically."
                    End If
 
                End If
 
                If multiple > 0 Then
 
                    SearchResults.Enabled = True
 
                    If multiple = 1 Then
                        MsgBox multiple & " music file had more than one match in the search."
                    Else
                        MsgBox multiple & " music files had more than one match in the search."
                    End If
 
                    Overwrite.Enabled = True
 
                End If
 
                If none > 0 Then
 
                    If none = 1 Then
                        MsgBox none & " music file had no matches in the search."
                    Else
                        MsgBox none & " music files had no matches in the search."
                    End If
 
                End If
 
                Exit Do
 
            Else
 
                DoCmd.GoToRecord , , acNext
 
            End If
 
        Loop
 
 

Theres quite a lot of unrelevent stuff in the loop so i commented it out for this post. The list box is called searchresults. I have tried writing code to do it but i end up with the last records search results in all the search result boxes.

If I'm understanding you correctly you want the listbox to only display the returned values of the current search and not accumulate the results from previous searches.

If this is the case you need to clear the data out of the listbox each to you add new data.

If you post your code that you are using to manipulate the listbox I can give you some more detail as how to go about doing this.

[JKing]

Your problem is that you are constantly adding to the listbox but never removing the existing items.

There are 3 ways you can fix this.

Method 1

Else
                    'Set value list of the list box to an empty string to clear all values
                    Me.SearchResults.RowSource = ""
 
                    For i = 1 To fs.FoundFiles.Count
 
                    SearchResults.AddItem fs.FoundFiles(i)
 
                    Next i
 
                    multiple = multiple + 1
End If
 

Method 2

Else
                    'Loop through the listbox and remove existing items
                    Do While Me.List1.ListCount > 0
                            Me.List1.RemoveItem (0)
                    Loop
 
                    For i = 1 To fs.FoundFiles.Count
 
                    SearchResults.AddItem fs.FoundFiles(i)
 
                    Next i
 
                    multiple = multiple + 1
End If
 

Method 3

'Declare a string
Dim strRowSource As String
 
strRowSource = ""
Else
                    'Build String
                    For i = 1 To fs.FoundFiles.Count
 
                    strRowSource = strRowSource & fs.FoundFiles(i) & ";"
 
                    Next i
 
                    'Set ListBox rowsource to string
                    SearchResults.RowSource = strRowSource
 
                    multiple = multiple + 1
End If
 

[shredder249]

I have tried all three of the ways you suggested, but the same problem keeps occuring. This is where only the last search results are displayed in each list box. I think im putting the code in the right place i.e. in the else part of the "If fs.FoundFiles.Count = 1 Then" statement. Is it to do with a property on the actual list box?

Your problem is that you are constantly adding to the listbox but never removing the existing items.

There are 3 ways you can fix this.

Method 1

Else
                    'Set value list of the list box to an empty string to clear all values
                    Me.SearchResults.RowSource = ""
 
                    For i = 1 To fs.FoundFiles.Count
 
                    SearchResults.AddItem fs.FoundFiles(i)
 
                    Next i
 
                    multiple = multiple + 1
End If
 

Method 2

Else
                    'Loop through the listbox and remove existing items
                    Do While Me.List1.ListCount > 0
                            Me.List1.RemoveItem (0)
                    Loop
 
                    For i = 1 To fs.FoundFiles.Count
 
                    SearchResults.AddItem fs.FoundFiles(i)
 
                    Next i
 
                    multiple = multiple + 1
End If
 

Method 3

'Declare a string
Dim strRowSource As String
 
strRowSource = ""
Else
                    'Build String
                    For i = 1 To fs.FoundFiles.Count
 
                    strRowSource = strRowSource & fs.FoundFiles(i) & ";"
 
                    Next i
 
                    'Set ListBox rowsource to string
                    SearchResults.RowSource = strRowSource
 
                    multiple = multiple + 1
End If