Solved Multiple search engine with dropdowns

[MajP]

So what is the reason for the unicode characters in the file names? Is this a download from the internet

The problem as discussed in the other thread is that Access supports unicode and so does windows. SQL does not nor the vba string functions. So there is no way to search for "bicep bicep 20 expresiones...." or "Graduation Cap Presente Perfecto..." So there is no way to determine if that file is in the table.

 

[Galaxiom]

As you know SQL cannot handle International characters and no way will it handle those crazy unicod characters.

VBA supports unicode using StrConv()

StrConv function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

I assume you mean Access SQL doesn't handle international characters. SQL Server certainly does.
It is all about the collation.

 

[zelarra821]

Good Morning.

Thanks for the info.

I have compiled all the code from the post, and I have put it in @MickJav's database, differentiated into two tables, to apply the codes of @MickJav and yours, @MajP to each one.

The one from @MickJav, as far as I can see, removes the accents, and I don't have the problem with both the accents and the apostrophes and underscore.

Yours does replace more characters, including apostrophes, but not all.

Also, there is a function, which you name as Test, which I don't know what it will be for and if it can be applied to my case.

On the other hand, my problem is comparing the file titles in the W10 file explorer with the ones I have in the database.

From what I gather, I would have to pass each FileItem by whatever function it has to convert the titles. Is it correct or is there another option?

 

[Dreamweaver]

You can create your own list to be removed replace just remember when you want it to relace with zero length sting put "" in the replace with file as it cant be empty.

 

[MajP]

IF this was me. I would change all of those wacky names in the directory before I ever insert anything. If not you are always chasing this. What good is double bicep or granduation symbol in a file name. If it cannot be read in SQL then change it once and be done with it. I believe this can handle all
International characters
Special characters
Unicode

Or convert and save to a second field. But I could see this getting real expensive on a big tree. Or you move to SQL server as @glaxiom pointing out.

Or maybe I am wrong. Maybe there is an easier way to save unicode and special characters and then search a field for them. I do not know. I have always seen the solution to replace international and other special characters. Not sure how strconv could aid here.

Public Function ReplaceInternationalCharacters(ByVal strText As String) As String
    Dim i As Integer
    'Big A
    For i = 192 To 197
            ReplaceInternationalCharacters = Replace(strText, Chr(i), "A")
    Next i
    'little A
     For i = 224 To 229
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "a")
    Next i
    'Big E
    For i = 200 To 203
            ReplaceInternationalCharacters = Replace(strText, Chr(i), "E")
    Next i
    'little e
     For i = 232 To 235
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "e")
    Next i
    'Big I
    For i = 204 To 207
            ReplaceInternationalCharacters = Replace(strText, Chr(i), "I")
    Next i
    'little i
     For i = 236 To 239
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "i")
    Next i
    'Replace Big O
    For i = 210 To 216
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "O")
    Next i
    'Replace little o
    For i = 242 To 248
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "o")
    Next i

    'Replace Big U
    For i = 217 To 220
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "O")
    Next i
    'Replace little u
    For i = 249 To 252
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(i), "o")
    Next i
  
    'Replace Big Y
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(152), "y")
       ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(236), "y")
    
    'Replace Big N
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(209), "N")
    'Replace little N
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(241), "n")
    End Function
Public Function ReplaceSpecialCharacters(ByVal strText As String, Optional ReplaceChar As String = "_")
   Dim i As Integer
   ReplaceSpecialCharacters = strText
   ReplaceSpecialCharacters = Replace(ReplaceSpecialCharacters, "'", "''")
   ReplaceSpecialCharacters = Replace(ReplaceSpecialCharacters, "#", "_No_")
   For i = 1 To 31
       ReplaceSpecialCharacters = Replace(ReplaceSpecialCharacters, Chr(i), "_")
   Next i
   For i = 126 To 231
        ReplaceSpecialCharacters = Replace(ReplaceSpecialCharacters, Chr(i), "_")
   Next i
End Function

Public Function ReplaceUnicodeCharacters(txt As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")
    regEx.Pattern = "[^\u0000-\u007F]"
    ReplaceUnicodeCharacters = regEx.Replace(txt, "~")
End Function
Public Function ReplaceBadCharacters(ByVal strtxt As String) As String
   ReplaceBadCharacters = strtxt
   ReplaceBadCharacters = ReplaceInternationalCharacters(ReplaceBadCharacters)
   ReplaceBadCharacters = ReplaceSpecialCharacters(ReplaceBadCharacters)
   ReplaceBadCharacters = ReplaceUnicodeCharacters(ReplaceBadCharacters)

End Function

 

[Dreamweaver]

IF this was me. I would change all of those wacky names in the directory before I ever insert anything.

I'm with @MajP on that why would you even allow them to be created in the first place.

 

[zelarra821]

Thank you so much guys. I take note of the suggestions. I think the best thing is going to be to clean those titles by hand, because this way I make sure that it does not give any type of error when verifying that what is in the W10 file explorer matches what is entered in the database

 

[MajP]

Thank you so much guys. I take note of the suggestions. I think the best thing is going to be to clean those titles by hand, because this way I make sure that it does not give any type of error when verifying that what is in the W10 file explorer matches what is entered in the database

You do not have to do this by hand. If you are authorized to change the names then do it on the first time you span the folder

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

If this was me and this was a large directory I would modify the process. This assumes you can rename files without breaking other processes

1. Span the directory
2. Clean up the name and rename it with a clean name. I show how to do that in the link.
I would replace international characters, replace number signs, remove apostrophes, Remove special characters. Remove Unicode. Possibly keep apostrophe and handle separately.
3. As I span I would log the non permited extension files and the hidden system files as well. In my opinion just do it once, then having to do it again. It is basically a freebie. They can go into the same table, but I would add a new field identifying them as Non Permitted Extension or System Hidden. Then can make three queries. The permitted files, non permitted, and hidden system.
This will be more efficient than re spanning for this same information.
4. If these folders stay the same but periodically files are added, deleted then I would definitely not reload as we do now. I did this for simplicity. Especially would not do this if we are talking a large number of files and folders. Currently we delete all records and reload. This is slow and inefficient, but if it is a small directory then it may not matter. The constant deleting bloats the database and requires a compact and repair after every time this is done.
Instead once the directory has been spanned, the next "refresh" I would not delete the records and reload. As you span you check if the file is in the table. If not only then do you add it. I would add another boolean field marking it as "Found" and update that. Now you would know what fields were not found, these are the fields that were deleted from the directory. You can do a simple delete query for those.
5. Loading the tree can also be made much faster if these are large trees. I am learning now that doing a recursive call to load the tree is OK for smaller trees., but can take a lot of time on bigger trees. This MSFORMS tree is much slower than the MSCOMCTL tree. What I did on the MSCOMCTL tree was do a "light load". I only load the first level of nodes. Then only when you expand a level do you add the nodes. This is way faster since there is actually no recursion. I have not done this with the MSFORMS code, but I will go ahead. I do not know if you need it, but it will come in handy for others.

How big do you expect these directories to be? That will really determine if this is nice to do or needed.

 

[zelarra821]

I'll give you a structure of the folders, with the number of files and the update frequency.

1. ZZZ Vídeos
   1. Canales de YouTube -> I update it almost every day. 175 files
   2. Documentales -> I update it very little. 11 files
   3. IGTV -> I update it from time to time, although not as much as YouTube channels. 23 files
   4. Películas -> I update it very little. 17 files
   5. Programas -> I update it very little. 11 files
   6. Series -> I update it very little. 1203 files
2. Audios
   1. Podcasts -> I update it very little. 4 files
3. Música -> I update the folders within Música a little, because I usually download little music. The ones I update the most are usually Otras.
   1. Artistas -> 348 files
   2. Otras -> 264 files
   3. Recopilatorios -> 342 files
      1. Discos -> 251 files
      2. Sevillanas -> 91 files

What I'm thinking is that I can convert the table you use to add the default folders into a continuous form with all these subfolders, so that it's easy to add a new one. It's just an idea.

What I did on the MSCOMCTL tree was do a "light load"

I have been left wanting to make MSCOMCTL work, but no matter how hard I tried, I was not able to.

I do not know if you need it, but it will come in handy for others.

Well it would be useful.

Well, it would be useful, but I do not get to that much, only to modify something when I have something already done, as has happened with TreeView, which changed the font and color, for example. I've been looking to get the folder you selected to open in the explorer but I haven't been able to. I have also seen that you can directly modify the name of the file, which if I did it also in the browser would be great.

 

[zelarra821]

I am trying to calculate the total of records in what we saw of the dropdowns and then add it to a text box at the bottom of the form, and it gives me the error that it cannot be assigned to the object, in addition to always returning one count of records.

In the form:

Function UpdateDynamicQuery()
  DoEvents
  getFilterCanciones Me, FAYTform
  If Me.FilterOn = False Then Me.Filter = ""
  GetDynamicQuery "CFormFilterCanciones", "CDynamicFilterCanciones", strFltrCombo
  Me.TxtBalance = GetDynamicQueryTotal("CFormFilterCanciones", "CDynamicFilterCanciones", Me.Filter)
End Function

In the In the mdlQueryUtilities module:

Public Function GetDynamicQueryTotal(SourceQry As String, DynamicQry As String, StrFilter As String) As Variant
  Dim strSql As String
  Dim rs As DAO.Recordset
  If StrFilter <> "" Then
    strSql = "Select * from " & SourceQry & " WHERE " & StrFilter
  Else
    strSql = "Select * from " & SourceQry
  End If
  Set rs = CurrentDb.OpenRecordset(strSql)
  GetDynamicQueryTotal = rs.RecordCount
  rs.Close
  Set rs = Nothing
End Function

 

[MajP]

That error normally means that TxtBalance is a calculated control and therefore you cannot assign anything to it. Or it is bound to a read only field.

 

[zelarra821]

I have removed the formula that I had in the rowsource, and it works. Now why does the function I have given you give me 1? I have used it in other cases and it has always given me a correct value.

 

[zelarra821]

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

I just found what I told you. Look:



It's in the form you created to add the multiple selection box.

 

[MajP]

I believe that is the Grid X and Grid Y properties of the form. By default it is 24 for me. If I make it smaller it looks like that.

Form.GridX property (Access)

Office VBA reference topic

docs.microsoft.com

I would print out your StrSql. Put that in a query and see what is returned.

 

[zelarra821]

I believe that is the Grid X and Grid Y properties of the form. By default it is 24 for me. If I make it smaller it looks like that.

Well, look, I was informing myself of that a few months ago. Curious. Thanks a lot.

I would print out your StrSql. Put that in a query and see what is returned.

That's the best, it gives a normal result. I attach screenshots.

 

[MajP]

Add this code

 If rs.EOF Then
      GetDynamicQueryTotal = 0
   Else
      rs.MoveLast
      GetDynamicQueryTotal = rs.RecordCount
   End If

Here is why

Count the number of records in a DAO Recordset

Office VBA reference topic

docs.microsoft.com

It is a longer discussion of why it worked before.

 

[zelarra821]

Count the number of records in a DAO Recordset

Thanks a lot. She did not know.

I have not done this with the MSFORMS code, but I will go ahead. I do not know if you need it, but it will come in handy for others.

How big do you expect these directories to be? That will really determine if this is nice to do or needed.

Do you plan to make a demo of what you were saying in this post number 268? I'm in no rush.

Thanks for everything.

 

[zelarra821]

I ask you about the last point because it would be useful to me, and I would like to know how to do it, but my knowledge of Access and VBA does not give much.

If you have more time on the weekend, I don't mind waiting

 

[MajP]

If you understand the recursive call to span the directory and log the folders and files, the call to load the Tree is almost identical.

Private Sub AddRecursiveBranch(ByVal ParentID As Variant, ByVal NodeLevel, ParentNode As clsNode)

  On Error GoTo errLable
  Dim strCriteria As String
  Dim bk As String
  Dim NodeID As String
  Dim NodeText As String
  Dim CurrentNode As clsNode
 
  strCriteria = "ParentID = '" & ParentID & "'"
  Me.NodeRecordset.FindFirst strCriteria
 
  Do Until Me.NodeRecordset.NoMatch
    NodeID = Me.NodeRecordset.Fields("NodeID")
    NodeText = Me.NodeRecordset.Fields("NodeText")
    NodeLevel = Me.NodeRecordset.Fields("NodeLevel")
    Set CurrentNode = ParentNode.AddChild(NodeID, NodeText)
    With CurrentNode
       .Tag = NodeLevel
       .Bold = False
      .Expanded = False
    End With
 
   bk = Me.NodeRecordset.Bookmark
   'Recursive call
   Call AddRecursiveBranch(NodeID, NodeLevel, CurrentNode)
   Me.NodeRecordset.Bookmark = bk
   Me.NodeRecordset.FindNext strCriteria
  Loop
 Exit Sub
errLable:
  MsgBox Err.Number & " " & Err.Description & " In addBranch"
  If MsgBox("Do you want to exit the loop?", vbYesNo, "Error In Loop") = vbYes Then
     Exit Sub
   Else
     Resume Next
   End If
End Sub

The big difference is that instead of writing the found files to a table you create a node and add to the tree.

The other method "light load" is actually far simpler and much easier to understand. If you never did recursion the recursive method can be very confusing.. For this 'light" method you read and load only the top Level Item/s.
Then when the user clicks on a node to expand it, you read the immediate children and add to the tree. That is a simple query without recursion. Basically something like
"Select * from qryNodes where ParentID = " & activeNode.Key

I probably should have made this the default method of loading instead of loading the whole tree at once recursively. I think most users would prefer to wait a short time IF they click on a node versus a long time for the whole tree to load at the very beginnin

I was using the MSCOMCTL so I put a lot of features into my class, and very little into the MSFORMS version. But since the MSFORMS version is easier for most people to get working on their computer, I will add these features.

However, I think the bigger issue for your current db is coming up with a solution for determining if a file exists in your database and it has international and special characters.

 

[zelarra821]

Thanks a lot. I will try to put into practice what you say. To do this, I wanted you to clarify something you have said.

Clean up the name and rename it with a clean name. I show how to do that in the link.
I would replace international characters, replace number signs, remove apostrophes, Remove special characters. Remove Unicode. Possibly keep apostrophe and handle separately.

In this database, I use the code you gave me above. However it doesn't work properly:

1. International characters (basically, accents), remove the vowel and put _. It should respect the vowel.
2. What do you understand by number signs? From 0 to 9?
3. What are the special characters? These: ?
4. What are the unicodes?

I ask you why those symbols do not totally replace them. Do I have to add anything else to the code as I discover more? How do i do it?