Solved Multiple search engine with dropdowns

[zelarra821]

And, on the other hand, I have joined two databases, importing into the treeview database all that from the other database, and now the treeview does not load well. I don't know if the QueryUtilities module has something to do with it, which was the only one I've touched, because the rest is exactly the same.

You can use this database, because it has the same thing that I attached to it in the previous post.

 

[zelarra821]

If it is easier for you to see where the failure of the treeview is, the latest version is in post 217, and the only thing would be to append the database of post 220.

 

[zelarra821]

This is how it comes out

 

[MajP]

I cannot test this because I do not have your folder structure. However all the parentID are 0 no idea how that happened. I added folders and ran it without problem.

To debug. I would put in a break. See if you can span a directory. Check that the parent IDs are all good. Then span two folders. Check that. Then span all three.

 

[zelarra821]

I have fix it. The solution was to put the two databases back together, paying more attention. And now I do know where the fault is: in the CSql and ParamInsert functions, which are not the same.

FUNCTIONS FOR TREEVIEW

Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  'Debug.Print strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function

Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = "Null"

    Dim Sql             As String
'    Dim LongLong        As Integer
'
'    #If Win32 Then
'        LongLong = vbLongLong
'    #End If
'    #If Win64 Then
'        LongLong = VBA.vbLongLong
'    #End If

    Select Case varType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = str(Value)
        Case vbLong             '    3  Long integer.
            Sql = str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
            If DateValue(Value) = Value Then
               Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
            Else
               Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = str(Value)
        Case vbByte             '   17  Byte.
            Sql = str(Value)
'        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
'            Sql = str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Trim(Sql)

End Function

FILTER FUNCTIONS

'Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
'  Dim qdf As QueryDef
'  Dim i As Integer
'  Dim MyParams As New Collection
'  Dim strMyParams As String
'  Dim strSql As String
'  If Left(TheFields, 1) <> "(" Then TheFields = "(" & TheFields & ")"
'
'  For i = 0 To UBound(TheValues)
'    MyParams.Add "Param" & i, "Param" & i
'    If strMyParams = "" Then
'      strMyParams = "[" & MyParams(i + 1) & "]"
'    Else
'      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
'    End If
'  Next i
'  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
'  ParamInsert = strSql
'  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
'   For i = 0 To UBound(TheValues)
'    qdf.Parameters(i) = TheValues(i)
'  Next i
'  qdf.Execute
'  CurrentDb.QueryDefs.Delete ("tempquery")
'End Function

'Public Function CSql( _
     ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
'    'Can be used when the Value is subtyped. For example you pass a declared variable
'    Const SqlNull       As String = "Null"
'    Dim Sql             As String
'
'    'If the Sql_type is not passed then use the data type of the value
'    If Trim(Value & " ") = "" Then
'      CSql = SqlNull
'    Else
'         If Sql_Type = sdt_UseSubType Then
'           Select Case varType(Value)
'             Case vbEmpty, vbNull
'               Sql_Type = sdt_Null
'             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
'               Sql_Type = sdt_Numeric
'             Case vbDate
'               Sql_Type = sdt_date
'             Case vbString
'               Sql_Type = sdt_text
'             Case vbBoolean
'               Sql_Type = sdt_Boolean
'             Case Else
'               Sql_Type = sdt_Null
'           End Select
'         End If
'
'        Select Case Sql_Type
'           Case sdt_text
'                 Sql = Replace(Trim(Value), "'", "''")
'                 Sql = fncQuitarAcentos(Sql)
'                 If Sql = "" Then
'                     Sql = SqlNull
'                 Else
'                     Sql = " '" & Sql & "'"
'                 End If
'           Case sdt_Numeric
'                 If IsNumeric(Value) Then
'                  Sql = CStr(Value)
'                 Else
'                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
'                  Exit Function
'                 End If
'           Case sdt_date
'                 If IsDate(Value) Then
'                     If Int(CDate(Value)) = Value Then
'                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
'                     Else
'                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
'                     End If
'                 Else
'                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation
'                   Exit Function
'                 End If
'           Case sdt_Boolean
'                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
'                   If Value = "True" Or Value = "Yes" Then Value = -1
'                   If Value = "False" Or Value = "No" Then Value = 0
'                   Sql = str(Value)
'                   'debug.print Sql
'                 Else
'                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation
'                   Exit Function
'                 End If
'            Case sdt_Null
'              Sql = SqlNull
'        End Select
'          CSql = Trim(Sql)
'   End If
'
'End Function

The two functions should be combined, because when I use CSql for filters, it tells me that arguments are missing, which is obvious.

 

[MajP]

The template for the tree view had some query utilities which may be different than what you had. You need to pick one of each and delete the other. Since you did some modifications I would use the code from the Filter Functions and modify your call to the function.

 

[zelarra821]

Let's see if I understand you, I must leave the 4 functions, and change the name of the functions.

 

[MajP]

Pick 2 and delete the other two. I would keep the one' s from the libros.

 

[zelarra821]

But I can't choose the ones from the books database because the treeview misconfigures me. Look at the functions that I put above, Csql is completely different in Treeview than the one in the Books database.

 

[MajP]

The one in the libros database was updated from the one in the treeview. It uses an enumeration SQL_DataType = sdt_UseSubType which is located in the query utilities module in the filter database. This version should work for both if you bring in the query utilities from the libros datbase.

 

[MajP]

 Case sdt_text
'                 Sql = Replace(Trim(Value), "'", "''")
'                 Sql = fncQuitarAcentos(Sql)
'                 If Sql = "" Then
'                     Sql = SqlNull
'                 Else
'                     Sql = " '" & Sql & "'"
'                 End If

One thing you added fncQuitarAcentos into this function. That is a poor design in my opinion. Now this can no longer be used in an insert query. Because it will modify your text to be unrecognizable. This should be a optional argument so that you can choose or not choose to use it. Or better remove it from the function all together. Then wrap your results only when needed

x = fncQuitarAcentos(CSql)

 

[zelarra821]

Fixed up.

Now other things that I told you before and I think you haven't seen it.

I'm trying to put a default value in the dropdown Estado, and it won't let me. It does not matter if you put it in the properties of the drop-down or activate Me.FilterOn when starting the form. I leave the database for you to try.

 

[MajP]

As I said all of these comboboxes should be bound to the Primary Key. Nothing else. You are creating unnecessary complexity by binding it to text field. So simply in the load

    mdlFiltros.BorrarFiltro Me, FAYTform
    Me.CodigoEstado = 3
    Me.FilterOn = True
   ApplyFilter Me, FAYTform

You cannot set a filter and then call the applyFilter. It will wipe out the filter and set it based on the values in the controls.

I improved this function.

Function getFilter(FName As Form, FAYTform As FindAsYouTypeForm_NoAuto) As String
Dim strFltr As String
Dim strDuracion As String
Dim strFAYT As String
Dim strEstado As String
Dim strMultimedia As String
Dim strCategoria As String
Dim strSubGenero As String
Dim ctrl As Access.Control
On Error GoTo errlbl
    strDuracion = GetBetweenFilter(FName.SrchDuracionMin, FName.SrchDuracionMax, "Duracion", sdt_date)
    For Each ctrl In FName.Controls
      If ctrl.controlType = acComboBox And ctrl.Tag = "Filter" Then ctrl.Requery
    Next ctrl

      
    strEstado = GetFilterFromControl(FName.CodigoEstado, , , 2)
    strMultimedia = GetFilterFromControl(FName.CodigoTipoMultimedia, , , 2)
    strCategoria = GetFilterFromControl(FName.CodigoCategoria, , , 2)
    strSubGenero = GetFilterFromControl(FName.CodigoSubcategoria, , , 2)
    strFltr = CombineFilters(ct_And, strDuracion, strEstado, strMultimedia, strCategoria, strSubGenero)

    strFAYT = FAYTform.Filter
    If strFltr <> "" And strFAYT <> "" Then
        strFltr = strFltr & " AND (" & strFAYT & ")"
    ElseIf strFltr = "" And strFAYT <> "" Then
        strFltr = strFAYT
    End If

    Select Case Screen.ActiveControl.Name
        Case "CodigoEstado"
            strFltrCombo = CombineFilters(ct_And, strDuracion, strMultimedia, strCategoria, strSubGenero)
        Case "CodigoTipoMultimedia"
            strFltrCombo = CombineFilters(ct_And, strDuracion, strEstado, strCategoria, strSubGenero)
        Case "CodigoCategoria"
            strFltrCombo = CombineFilters(ct_And, strDuracion, strEstado, strMultimedia, strSubGenero)
        Case "CodigoSubcategoria"
             strFltrCombo = CombineFilters(ct_And, strDuracion, strEstado, strMultimedia, strCategoria)
    End Select
err2474Resume:
    
     If strFltrCombo <> "" And strFAYT <> "" Then
        strFltrCombo = strFltrCombo & " AND (" & strFAYT & ")"
    ElseIf strFltrCombo = "" And strFAYT <> "" Then
        strFltrCombo = strFAYT
    End If
        
    getFilter = strFltr
    Debug.Print "Overal Filter " & strFltr
    Debug.Print "combo filter 2 " & strFltrCombo
    GetDynamicQuery "CFormFilterVideos", "CDynamicFilterVideos", strFltrCombo
    Exit Function
errlbl:
    If Err.Number = 2474 Then
      GoTo err2474Resume
    Else
      MsgBox Err.Number & " " & Err.Description & " In Getfilter"
    End If
    
End Function

This allows a filter to be applied without the active control being set.

 

[zelarra821]

I have already solved. I have put the dependent column that you recommend.

Another problem that I am running into. I am trying to create a counter to count a file type when I update the treeview. It works for me the first time, but a second time passes, and it resets it to me.

Private Sub SpanFolders(SourceFolderFullName As String, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)

' 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 Contador As Integer
   ' Dim ParentID As Long
    Contador = 0
    
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    FolderLevel = FolderLevel + 1
    LogFilesFolders SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, ParentID, fft_Folder, FolderLevel
    ParentID = GetFolderID(SourceFolder.Path)
    For Each FileItem In SourceFolder.Files
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" Then
            LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
        End If
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" _
        And FileItem.Type <> "MP4 Video File (VLC)" Then
            Contador = Contador + 1
        End If
    Next FileItem
    
    For Each SubFolder In SourceFolder.SubFolders
        ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
    '   LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
       If (SubFolder.Attributes And 2) <> 2 And (SubFolder.Attributes And 4) <> 4 Then
            SpanFolders SubFolder.Path, ParentID, FolderLevel
       End If
    Next SubFolder

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    MsgBox Contador
End Sub

 

[MajP]

I do not see any code to count a file type. I only see the existing code to count files execept certain files.

 

[zelarra821]

Hi. Not to count a specific file type, but what I do is discard the ones that can always be there (hidden, system, file with translations, and mp4), and count the rest to have a warning that they are not mp4. So I use Contador for it. That way I avoid having to put all video formats other than mp4.

 

[MajP]

I still do not understanding. There was code to count only the non system files which worked

 If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 And FileItem.Type <> "Archivo SRT" _
        And FileItem.Type <> "MP4 Video File (VLC)" Then
            Contador = Contador + 1
        End If

You added some more rules to it. Are you saying it only works when you first load the tree? That code looks correct to me.

 

[zelarra821]

I still do not understanding. There was code to count only the non system files which worked

Yes effectively. What I have done has been to add another if exactly the same but to count the files, basically videos, that are not from the system, hidden, srt and mp4 files, through Counter, so that I get a warning that there are videos that are not mp4 .

You added some more rules to it. Are you saying it only works when you first load the tree? That code looks correct to me.

I have already understood you. I believe. Let's see, what I want in the CmdRead button, which it does is update the TreeView, I get a message when there are videos that are not mp4. So, I am focusing it wrong. I have created a procedure for it. Look at the video:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I am attaching the database.

 

[MajP]

Your if check is wrong. You want to count all the bad files, but you copied from the original which included only good files.

Public Sub Alert(SourceFolderFullName As String)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
    Dim Contador As Integer
    Contador = 0
    Dim StrOut As String
    Set FSO = New FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
   
    For Each FileItem In SourceFolder.Files
        If (FileItem.Attributes And 2) = 2 Or (FileItem.Attributes And 4) = 4 Or FileItem.Type = "Archivo SRT" _
        Or FileItem.Type = "MP4 Video File (VLC)" Then
            Contador = Contador + 1
            If StrOut = "" Then
              StrOut = "The Following hidden/system file/s encountered: " & FileItem.Name & " in Folder " & SourceFolder.Name
            Else
              StrOut = StrOut & "; " & FileItem.Name & " in Folder " & SourceFolder.Name
            End If
        End If
    Next FileItem
    If Contador > 1 Then
        MsgBox StrOut & " total files " & Contador
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub

You also need to re set FSO since it was previously set to nothing after spanning.

However, I doubt this will do what you want. This is not a recursive call so only checks the source folder and no subfolders.

 

[zelarra821]

However, I doubt this will do what you want. This is not a recursive call so only checks the source folder and no subfolders.

Hi. Sure, he doesn't do what I want. You need to go through the subfolders, but I can't figure out how to do it.