filter 3 subforms

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • favor08
    New Member
    • Jan 2007
    • 56
    #1

    filter 3 subforms

    am trying to filter the 3 subforms based off of the combo boxes and
    textboxes. If I select one cbobox and hit submit it will filter the
    first but not the 2nd and 3rd then if I hit submit againthe second and
    third subform will filter.


    Sumitt is a command button that has the followin code on the
    onclick...


    Code:
    Dim stFilter As String 
If NZ(Me.cboOpidCCE) <> "" Then stFilter = stFilter & " And [opidcce] 
= '" & Me.cboOpidCCE & "'" 
If NZ(Me.cboOpidARA) <> "" Then stFilter = stFilter & " And [opidara] 
= '" & Me.cboOpidARA & "'" 
If NZ(Me.CboProd) <> "" Then stFilter = stFilter & " And [origprodcd] 
= '" & Me.CboProd & "'" 
If NZ(Me.txtSession) <> "" Then stFilter = stFilter & " And 
[origsession] = '" & Me.txtSession & "'" 
If NZ(Me.txtPolicy) <> "" Then stFilter = stFilter & " And [cvtpolicy] 
= '" & Me.txtPolicy & "'" 
If NZ(Me.txtcheck) <> "" Then stFilter = stFilter & " And 
[checknumber] = '" & Me.txtcheck & "'" 
If NZ(Me.txtStobAmt) <> "" Then stFilter = stFilter & " And [netStob] 
= " & Me.txtStobAmt 
If Len(stFilter) > 0 Then stFilter = right(stFilter, Len(stFilter) - 
5) 
Me!FCashApplicationBrokerSub.Form.Filter = stFilter 
Me!FCashApplicationBrokerSub2.Form.Filter = stFilter 
Me!fCashApplicationBrokersub3.Form.Filter = stFilter 
Me!FCashApplicationBrokerSub.Form.FilterOn = True 
Me!FCashApplicationBrokerSub2.Form.FilterOn = True 
Me!fCashApplicationBrokersub3.Form.FilterOn = True

    They are not filtering correctly
    Last edited by NeoPa; Feb 5 '07, 12:10 AM. Reason: Tags for Layout
    Tags: None
    • willakawill
      Top Contributor
      • Oct 2006
      • 1646
      #2
      Code:
      Dim stFilter As String 
If NZ(Me.cboOpidCCE) <> "" Then 
   stFilter = stFilter & "[opidcce] = '" & Me.cboOpidCCE & "'" 
End If
If NZ(Me.cboOpidARA) <> "" Then 
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [opidara] = '" & Me.cboOpidARA & "'" 
   Else
      stFilter = "[opidara] = '" & Me.cboOpidARA & "'" 
   End If
End If
If NZ(Me.CboProd) <> "" Then 
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origprodcd] = '" & Me.CboProd & "'" 
   Else
       stFilter = "[origprodcd] = '" & Me.CboProd & "'" 
   End If
End If
If NZ(Me.txtSession) <> "" Then 
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origsession] = '" & Me.txtSession & "'" 
   Else
      stFilter = "[origsession] = '" & Me.txtSession & "'" 
   End If
End If  'and so on
      Hi it might help if you constructed your filter string using the pattern above. Let us know how it goes.

        Comment

        • favor08
          New Member
          • Jan 2007
          • 56
          #3
          Originally posted by willakawill
          Code:
          Dim stFilter As String 
If NZ(Me.cboOpidCCE) <> "" Then 
   stFilter = stFilter & "[opidcce] = '" & Me.cboOpidCCE & "'" 
End If
If NZ(Me.cboOpidARA) <> "" Then 
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [opidara] = '" & Me.cboOpidARA & "'" 
   Else
      stFilter = "[opidara] = '" & Me.cboOpidARA & "'" 
   End If
End If
If NZ(Me.CboProd) <> "" Then 
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origprodcd] = '" & Me.CboProd & "'" 
   Else
       stFilter = "[origprodcd] = '" & Me.CboProd & "'" 
   End If
End If
If NZ(Me.txtSession) <> "" Then 
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origsession] = '" & Me.txtSession & "'" 
   Else
      stFilter = "[origsession] = '" & Me.txtSession & "'" 
   End If
End If  'and so on
          Hi it might help if you constructed your filter string using the pattern above. Let us know how it goes.
          When I put this:

          Code:
          Dim stFilter As String
If NZ(Me.cboOpidCCE) <> "" Then
   stFilter = stFilter & "[opidcce] = '" & Me.cboOpidCCE & "'"
End If
If NZ(Me.cboOpidARA) <> "" Then
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [opidara] = '" & Me.cboOpidARA & "'"
   Else
      stFilter = "[opidara] = '" & Me.cboOpidARA & "'"
   End If
End If
If NZ(Me.CboProd) <> "" Then
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origprodcd] = '" & Me.CboProd & "'"
   Else
       stFilter = "[origprodcd] = '" & Me.CboProd & "'"
   End If
End If
If NZ(Me.txtSession) <> "" Then
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origsession] = '" & Me.txtSession & "'"
   Else
      stFilter = "[origsession] = '" & Me.txtSession & "'"
   End If
Me!FCashApplicationSub.Form.Filter = stFilter
Me!fcashApplicationSub2.Form.Filter = stFilter
Me!fCashApplicationsub3.Form.Filter = stFilter
Me!FCashApplicationSub.Form.FilterOn = True
Me!fcashApplicationSub2.Form.FilterOn = True
Me!fCashApplicationsub3.Form.FilterOn = True
End If
End Sub
          On the onclick of the command button nothing happens.
          Last edited by NeoPa; Feb 5 '07, 12:08 AM. Reason: Tags for Layout

            Comment

            • willakawill
              Top Contributor
              • Oct 2006
              • 1646
              #4
              Please use code tags when posting code

              When you say nothing happens. Did you step through this code in debug?

                Comment

                • favor08
                  New Member
                  • Jan 2007
                  • 56
                  #5
                  yes, I don't get an error message or anything.

                  I click on the button and nothing filters.

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32633
                      #6
                      Did you check that the filter (strFilter) matches what you expected it to?
                      When you say "nothing filters", do you mean that (ie you get all records) or did you mean it filters it all out (No records)?

                        Comment

                        • favor08
                          New Member
                          • Jan 2007
                          • 56
                          #7
                          Originally posted by NeoPa
                          Did you check that the filter (strFilter) matches what you expected it to?
                          When you say "nothing filters", do you mean that (ie you get all records) or did you mean it filters it all out (No records)?
                          I mean nothing happens, nothing filters or move or give me an error.

                            Comment

                            • NeoPa
                              Recognized Expert Moderator MVP
                              • Oct 2006
                              • 32633
                              #8
                              1. Do the records in your subforms change their data at all?
                              2. Do they have any records showing before this code executes?
                              3. Can you get any records to show if you don't filter at all?

                                Comment

                                • favor08
                                  New Member
                                  • Jan 2007
                                  • 56
                                  #9
                                  No- nothing filters
                                  Yes - I can see the data before i submit the fitler
                                  Yes- yes I can still see the data after i hit the filter because nothing moves.

                                    Comment

                                    • NeoPa
                                      Recognized Expert Moderator MVP
                                      • Oct 2006
                                      • 32633
                                      #10
                                      Originally posted by favor08
                                      When I put this:

                                      Code:
                                      Dim stFilter As String
If NZ(Me.cboOpidCCE) <> "" Then
   stFilter = stFilter & "[opidcce] = '" & Me.cboOpidCCE & "'"
End If
If NZ(Me.cboOpidARA) <> "" Then
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [opidara] = '" & Me.cboOpidARA & "'"
   Else
      stFilter = "[opidara] = '" & Me.cboOpidARA & "'"
   End If
End If
If NZ(Me.CboProd) <> "" Then
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origprodcd] = '" & Me.CboProd & "'"
   Else
       stFilter = "[origprodcd] = '" & Me.CboProd & "'"
   End If
End If
If NZ(Me.txtSession) <> "" Then
   If Len(stFilter) > 0 Then
      stFilter = stFilter & " And [origsession] = '" & Me.txtSession & "'"
   Else
      stFilter = "[origsession] = '" & Me.txtSession & "'"
   End If
Me!FCashApplicationSub.Form.Filter = stFilter
Me!fcashApplicationSub2.Form.Filter = stFilter
Me!fCashApplicationsub3.Form.Filter = stFilter
Me!FCashApplicationSub.Form.FilterOn = True
Me!fcashApplicationSub2.Form.FilterOn = True
Me!fCashApplicationsub3.Form.FilterOn = True
End If
End Sub
                                      On the onclick of the command button nothing happens.
                                      Try moving the (last) End If line up six lines.
                                      This is where proper code indentation can help you so much.
                                      Let us know how you get on.

                                        Comment

                                        Previous template Next
                                        Working...