Filtering - Table or recordset

[diegomarino]

hi
i have a table that i filter with a form composed by some subform in which i insert multiple criteria for every field.
I tied every subform to a table, i'm wondering if could be better to use recordset instead of tables to store the criteria, and i wonder if is possible to set a form recodsource to a recordset (i read some stuff but didn't find out)

[ranman256]

I set all forms to queries.
you can then filter , say a text box:

sub txtBox_afterupdate()
if isNull(txtBox) then
   me.filterOn = false
else
   me.filter = "[field]='" & txtBox & "'"
   me.filterOn = true
endif
end sub

[diegomarino]

i'm using a complex system, cause for every field i need a lot of criteria

Private Sub LKFiltBtn_Click()

   DoCmd.RunCommand acCmdSaveRecord
   FieldName = " [Nome cognome] "
   NameFiltStr = ""
   Set db = CurrentDb
   Set NameFiltRst = db.OpenRecordset("FiltroLKName")
   If NameFiltRst.RecordCount > 0 Then
      NameFiltRst.MoveFirst
      Do Until NameFiltRst.EOF
         If NameFiltStr = "" Then
            NameFiltStr = FieldName & NameFiltRst![Operatore] & NameFiltRst![Wildcard1] & NameFiltRst![NameFilt] & NameFiltRst![Wildcard2]
         Else
            NameFiltStr = NameFiltStr & NameFiltRst![Logico] & FieldName & NameFiltRst![Operatore] & NameFiltRst![Wildcard1] & NameFiltRst![NameFilt] & NameFiltRst![Wildcard2]
         End If
         NameFiltRst.MoveNext
   Loop
   End If
   TotalFiltStr = NameFiltStr


   DoCmd.RunCommand acCmdSaveRecord
   FieldPosition = " [Position] "
   PositionFiltStr = ""
   Set db = CurrentDb
   Set PositionFiltRst = db.OpenRecordset("FiltroLKPosition")
   If PositionFiltRst.RecordCount > 0 Then
      PositionFiltRst.MoveFirst
      Do Until PositionFiltRst.EOF
         If PositionFiltStr = "" Then
            PositionFiltStr = FieldPosition & PositionFiltRst![Operatore] & PositionFiltRst![Wildcard1] & PositionFiltRst![PosizioneFilt] & PositionFiltRst![Wildcard2]
         Else
            PositionFiltStr = PositionFiltStr & PositionFiltRst![Logico] & FieldPosition & PositionFiltRst![Operatore] & PositionFiltRst![Wildcard1] & PositionFiltRst![PosizioneFilt] & PositionFiltRst![Wildcard2]
         End If
         PositionFiltRst.MoveNext
   Loop
   End If
   'MsgBox (PositionFiltStr)
   If TotalFiltStr = "" Then
      TotalFiltStr = PositionFiltStr
   Else
      If PositionFiltStr <> "" Then
         TotalFiltStr = TotalFiltStr & " AND (" & PositionFiltStr & ")"
      End If
   End If
   
   DoCmd.RunCommand acCmdSaveRecord
   FieldCompany = " [Company] "
   BancaFiltStr = ""
   Set db = CurrentDb
   Set BancaFiltRst = db.OpenRecordset("FiltroLKBanca")
   If BancaFiltRst.RecordCount > 0 Then
      BancaFiltRst.MoveFirst
      Do Until BancaFiltRst.EOF
         If BancaFiltStr = "" Then
            BancaFiltStr = FieldCompany & BancaFiltRst![Operatore] & BancaFiltRst![Wildcard1] & BancaFiltRst![BancaFilt] & BancaFiltRst![Wildcard2]
         Else
            BancaFiltStr = BancaFiltStr & BancaFiltRst![Logico] & FieldCompany & BancaFiltRst![Operatore] & BancaFiltRst![Wildcard1] & BancaFiltRst![BancaFilt] & BancaFiltRst![Wildcard2]
         End If
      BancaFiltRst.MoveNext
   Loop
   End If
   If TotalFiltStr = "" Then
      TotalFiltStr = BancaFiltStr
   Else
      If BancaFiltStr <> "" Then
         TotalFiltStr = TotalFiltStr & " AND (" & BancaFiltStr & ")"
      End If
   End If


   DoCmd.RunCommand acCmdSaveRecord
   FieldCity = " [CittàID] "
   CityFiltStr = ""
   Set db = CurrentDb
   Set CityFiltRst = db.OpenRecordset("FiltroLKCittà")
   If CityFiltRst.RecordCount > 0 Then
      CityFiltRst.MoveFirst
      Do Until CityFiltRst.EOF
         If CityFiltStr = "" Then
            CityFiltStr = FieldCity & CityFiltRst![Operatore] & CityFiltRst![CittàFilt]
         Else
            CityFiltStr = CityFiltStr & " " & CityFiltRst![Logico] & FieldCity & CityFiltRst![Operatore] & CityFiltRst![CittàFilt]
         End If
         CityFiltRst.MoveNext
   Loop
   End If
   If CityFiltStr <> "" And InStr(CityFiltStr, "<>") Then
      CityFiltStr = CityFiltStr & " OR [CittàID] is null"
   End If
   If TotalFiltStr = "" Then
      TotalFiltStr = CityFiltStr
   Else
      If CityFiltStr <> "" Then
         TotalFiltStr = TotalFiltStr & " AND (" & CityFiltStr & ")"
      End If
   End If


   DoCmd.RunCommand acCmdSaveRecord
   FieldMercato = " [Mercato] "
   MercatoFiltStr = ""
   Set db = CurrentDb
   Set MercatoFiltRst = db.OpenRecordset("FiltroLKMercato")
   If MercatoFiltRst.RecordCount > 0 Then
      MercatoFiltRst.MoveFirst
      Do Until MercatoFiltRst.EOF
         If MercatoFiltStr = "" Then
            MercatoFiltStr = FieldMercato & MercatoFiltRst![Operatore] & MercatoFiltRst![MercatoFilt]
         Else
            MercatoFiltStr = MercatoFiltStr & " " & MercatoFiltRst![Logico] & FieldMercato & MercatoFiltRst![Operatore] & MercatoFiltRst![MercatoFilt]
         End If
         MercatoFiltRst.MoveNext
      Loop
   End If
   If MercatoFiltStr <> "" And InStr(MercatoFiltStr, "<>") Then
      MercatoFiltStr = MercatoFiltStr & " OR [Mercato] is null"
   End If
   If TotalFiltStr = "" Then
      TotalFiltStr = MercatoFiltStr
   Else
      If MercatoFiltStr <> "" Then
         TotalFiltStr = TotalFiltStr & " AND (" & MercatoFiltStr & ")"
      End If
   End If


   DoCmd.RunCommand acCmdSaveRecord
   FieldSettore = " [Settore] "
   SettoreFiltStr = ""
   Set db = CurrentDb
   Set SettoreFiltRst = db.OpenRecordset("FiltroLKSettore")
   If SettoreFiltRst.RecordCount > 0 Then
      SettoreFiltRst.MoveFirst
      Do Until SettoreFiltRst.EOF
         If SettoreFiltStr = "" Then
            SettoreFiltStr = FieldSettore & SettoreFiltRst![Operatore] & SettoreFiltRst![SettoreFilt]
         Else
            SettoreFiltStr = SettoreFiltStr & " " & SettoreFiltRst![Logico] & FieldSettore & SettoreFiltRst![Operatore] & SettoreFiltRst![SettoreFilt]
         End If
         SettoreFiltRst.MoveNext
      Loop
   End If
   If SettoreFiltStr <> "" And InStr(SettoreFiltStr, "<>") Then
      SettoreFiltStr = SettoreFiltStr & " OR [Mercato] is null"
   End If
   If TotalFiltStr = "" Then
      TotalFiltStr = SettoreFiltStr
   Else
      If SettoreFiltStr <> "" Then
         TotalFiltStr = TotalFiltStr & " AND (" & SettoreFiltStr & ")"
      End If
   End If
   
   FieldData = " [Data Invio] "
   DataInvioFiltStr = FieldData & DataInvioOperatore & "#" & Me.DataInvioFilt & "#" & " OR [data invio] is null"
   If Me.DataInvioFilt <> "" Then
      If TotalFiltStr = "" Then
         TotalFiltStr = DataInvioFiltStr
      Else
         TotalFiltStr = TotalFiltStr & " AND " & "( " & DataInvioFiltStr & ")"
      End If
   End If
   
   If Not IsNull(Me.InDBInt) Then
      If TotalFiltStr = "" Then
         TotalFiltStr = "[in db] = " & Me.InDBInt & ""
      Else
         TotalFiltStr = TotalFiltStr & " AND (" & "[in db] =" & Me.InDBInt & ")"
      End If
   End If
   
   If Not IsNull(Me.NonInteressanteInt) Then
      If TotalFiltStr = "" Then
         TotalFiltStr = "[non interessante] = " & Me.NonInteressanteInt & ""
      Else
         TotalFiltStr = TotalFiltStr & " AND (" & "[non interessante] =" & Me.NonInteressanteInt & ")"
      End If
   End If
   MsgBox (TotalFiltStr)
   If TotalFiltStr = "" Then
      Forms![lk contatti_messaggiSingle].Form.FilterOn = False
   Else
      Forms![lk contatti_messaggi].[lk contatti_messaggiSingle].Form.Filter = TotalFiltStr
      Forms![lk contatti_messaggi].[lk contatti_messaggiSingle].Form.FilterOn = True
'      Forms![lk contatti_messaggiSingle].Filter = TotalFiltStr
'      Forms![lk contatti_messaggiSingle].FilterOn = True
   End If
End Sub