Solved Multiple search engine with dropdowns

[MajP]

See demo. I used module level variables, it was the simplest but maybe not the cleanest way.

 

[zelarra821]

Hi. Thanks a lot. I get error when starting the form where the TreeView is.

Anyway, I'm trying to do it as I told you at the beginning, and I have it almost ready. The problem is that it happens twice and shows you the message with the counter twice. Look:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

There has to be some way to finish the procedure before that second message pops up.

 

[zelarra821]

Achieved.

This is the function:

Public Sub Alert(SourceFolderFullName As String)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
   ' Dim ParentID As Long
    Set FSO = New FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    For Each FileItem In SourceFolder.Files
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" _
        And FileItem.Type <> "MP4 Video File (VLC)" And FileItem.Type <> "MPEG Layer 3 Audio File" Then
            Contador = Contador + 1
        End If
    Next FileItem
    
    If SourceFolder.SubFolders.Count > 0 Then
        For Each SubFolder In SourceFolder.SubFolders
            If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
                    Alert SourceFolderFullName & "\" & SubFolder.Name
            End If
        Next SubFolder
    Else
        MsgBox Contador
        Exit Sub
    End If

    Set FileItem = Nothing
    Set SubFolder = Nothing
    Set SourceFolder = Nothing
End Sub

At the beginning of the module you have to put:

Private Contador As Integer

And then I have created a procedure to start the count, resetting the counter:

Public Sub InitializeAlert()
    Contador = 0
    Alert GetDefaultFolderPath
End Sub

 

[zelarra821]

Well I just tried with more than one subfolder and it loops. I don't know where the fault can be.

 

[zelarra821]

I'm sure it's a sort problem, but I can't find the key.

 

[zelarra821]

I keep thinking about it, but I can't get it right. If it can be the way I propose, better, because then I want to get another value from the same system, but changing the conditions of the if.

 

[MajP]

This seems inefficient. You plan to span the directory and the respan to get the alerts. That is like going on a 10 km walk and logging the names of everyone you see. Then starting over and counting the number of dogs.

  For Each SubFolder In SourceFolder.SubFolders
            If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
                    Alert SourceFolderFullName & "\" & SubFolder.Name
            End If
        Next SubFolder

If you do that you will never span the whole tree because the recursive call is in the if check. You only go to the next subfolder if you find a subfolder that is hidden/system. IF the first folder does not have a hidden/system subfolders then you are done. The recursion has to be outside the IF.
I caution opening the FSO in the recursion. This way you open an FSO for each branch. You will have many open at one time. What do you mean it loops?
Why do you count the files and log the folders? You can have files in regular folders.
Not sure you plan for this information but you may want to log it as well. You can send this to a table similar to the folder/files table and it would have the "bad" files. Then you could have a subform with all the hidden and system files and folders.

 

[zelarra821]

In those three folders there can be four types of files: mp4, mp3, pdf and srt. I want to get a value that tells me if there are any files that do not have that extension, for example mkv or avi. I do not want to put all the video and audio formats, but to obtain it by difference with the ones that I know will be fixed.

And on the other hand, get the total number of mp4 files in videos, and mp3 in the other two folders.

I don't want them to be on a table. Just get it as a parameter to put it in a msgbox.

The first as a warning that there are other formats. The second to compare it with the corresponding table and see if what I have in the database matches what is in the folders.

The demo you sent me gave an error, so I couldn't check if it worked.

 

[MajP]

What error do you get? It works fine for me. Here are the results of a 10,000 file search.

 

[zelarra821]

Okay now. I have had to pass it to another database to see the result. Thanks a lot.

 

[MajP]

That error looks like the database had somehow lost the reference to the MSFORMS which is in a default reference. I understand what you are trying to count. My point was I would think you want to do this on the first span of the directory and not a subsequent span. On a very large directory it can take a long time to span. My 10k takes well over a couple minutes. You may want to include a progress meter. So spanning a second time would not be a good idea.

 

[zelarra821]

A progress meter? What would that be like?

 

[MajP]

There are a couple on this site. @isladogs has one here

Solved - Show progress during export to Word

I have created a popup and very rigid routine for creating a dossier in MS Word with data from my application. Because creating the file is taking sometime (not extremely) I would like to keep the user entertained. my question, is there a better way to show progress? Everything works but I...

www.access-programmers.co.uk

I loaded a big tree and it should have something to tell the user to wait.

 

[zelarra821]

Thanks a lot. I'll take a look at it because it looks good. What I don't know is how to implement it, I guess I will have to put a step in each next.

And then a question that I've wanted to ask you for a long time. I have seen in some of your databases that, in the forms, you have the fields as "grouped", but that they are fully independent of each other, that is, although grouped I can select only one field. I have grouped them and I cannot select a single field, it selects all those that are grouped. I don't know if you know what I mean. How is that done? I have looked for the way but I have not found anything.

 

[MajP]

Not sure what you mean by grouped. Sometimes I simply put a rectangle around some controls to make them appear like a group. Is that what you mean?

 

[zelarra821]

No, I have already seen that, and no. It is a rectangle of dotted lines. Maybe it's what you say. I dont know.

 

[MajP]

can you take a screen shot. I do not know, and do not think it is something I did on purpose.

 

[zelarra821]

I have asked you if you remembered, because I have seen it but I do not remember where. If I tell you that I remember two fields, a blue background, and that it was to the left of the screen, but I don't remember the file you shared with me. So when I see it again, I'll tell you.

 

[zelarra821]

Let's see if you can give me an answer, because I don't know how to fix it.

I tell you:

First of all, I'll give you the entire database because removing the accessory takes me more time handling errors so you don't have any problems.

With the code to do the alerts, I am now comparing the files that I have in the W10 file explorer with the files that I have registered in the database. You can see the code in the module mdlAlertas (alert), and the procedure is called Search (Search).

As you can see, what I do is search in a query for the name of the file (FileItem.Name), removing the extension and replacing the apostrophes. I pass it through Nz to handle null values and not fail. I have created a query to transform the formatted text to the plain one, as well as replacing the apostrophes.

In this way it tells me which files from the W10 file explorer are not in the database.

It does me good for everyone, except the four that I attach in the zip. They are txt because sending the videos could not share it so easily.

How can you check it? The first thing you will have to do is change the custom folder: in the form PredeterminedFolders choose the second one that puts audio. Then, you can go to the FAudios form and use the Check button (I have put the file names in the TAudios table).

Besides, Access recognizes some emojis that W10 also recognizes, but others don't (you can see examples in the TVideos table). I do not know if there will be any to avoid so that it does not fail.

I leave you a video with the steps:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[MajP]

As you know SQL cannot handle International characters and no way will it handle those crazy unicod characters. My suggestion is that you have a second field called SearchName. When you log the name you also log the SearchName. Then do all searches on that
In this you do all your replacements such as á, é, í, ó, ú, ü, ñ, ¿ with a,e,i,o,u,u,n ?
You are never going to find the unicode. I would check every upper unicode and come up with a code
There is no SQL that will find
The word WAY_ ¿Sabes cómo usarla en inglés_
So I would replace any ansii characters with ~. So that gets stored as
~ The word Way_?Sabes como... ingles~

So this will not work
If Nz(DLookup("Nombre1", Consulta, "Nombre1='" & Replace(Left(FileItem.Name, Len(FileItem.Name) - 4), "'", "") & "'"), 0) = 0 Then
All you did was handle 1 exception ( apostrophe)


See discussion here

FileSystemObject error when the file names has special letters

Hello All I'm not bad on Microsoft Access but I am quite new on VBA codes. I spent almost 24 hours to get solution below. I have several csv files I am importing them to table with bellowing code: Dim FSO As Object, objFolder As Object, objFile As Object Set FSO =...

www.access-programmers.co.uk