filter subform using multiple combo boxes

[vranjit138]

Hi,
I have a table tbl_prod wherein i have 4 fields
year
Month
Day
Amount

There are 180 rows of data

I have created a form and put 3 combo boxes which show distinct values of Year, month and day

i have put a subform showing all the 4 fields with all the data in the form which is based on tbl_prod

1) How to filter the records of the subform in order of Year --Month--Day picking the values from comboboxes

2)as the data is filtered i need to show the sum of amount in a label

please help

Thanks in advance

thanks and regards
Ranjit

 

[strive4peace]

Hi Ranji,

do not use YEAR, MONTH, or DAY as a fieldname, they are reserved words

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

put the comboboxes in your form header. Then, in the [Event Procedure] code of the AfterUpdate event of each combobox:

SetFormFilter

this is a generic version of the code that would go behind your form:

Private Function SetFormFilter()
   
   dim varFilter as variant
   varFilter = Null
   
   If not IsNull(me.controlname_for_text) Then
      varFilter = (varFilter + " AND ") _
         & "[TextFieldname]= '" & me.controlname_for_text  & "'"
   end if
   
   If not IsNull(me.controlname_for_date  ) Then
      varFilter = (varFilter + " AND ") _
         & "[DateFieldname]= #" & me.controlname_for_date  & "#"
   end if
  
   If not IsNull(me.controlname_for_number ) Then
      varFilter = (varFilter + " AND ") _
         & "[NumericFieldname]= " & me.controlname_for_number
   end if
   
   With Me
       If Not IsNull(varFilter )  Then   
          .Filter = varFilter 
          .FilterOn = true
      Else
          .FilterOn = false
      End if
      .Requery
   End With
 
End Function

WHERE
me.controlname_for_number, controlname_for_date, and controlname_for_text refer to the NAME property of a control on the form you are behind
(Me. represents the form -- kinda like "me" for me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

since you are wanting to filter a subform, instead of using
With Me
you would use -->

With Me.subform_controlname.form

WHERE.subform_controlname is the Name property of the subform control

 

[strive4peace]

"as the data is filtered i need to show the sum of amount in a label"

it would be best to show your data in a continuous form (NOT a Datasheet form), then, you can make the following textbox control in the form footer:

Name --> SumAmount
ControlSource --> =Sum([Amount])

WHERE
Amount is the NAME of the control that has the Amount field in it.

this way, you will always see the sum of the amount for the records that are showing.

Out of curiosity, why are you storing year, month, and day in three fields instead of using a date field?

 

[james_lankford]

what is the advantage of using filters as opposed to just putting form parameters in a query ?

 

[strive4peace]

Hi Ranjit,

you can use criteria pointing to your form ... but what if you decide to ONLY show the year, or just year/month, ...? You can use Wildcards on text, but not numbers.

With this method, you can filter on just what is specified <smile>

 

[james_lankford]

...

you can use criteria pointing to your form ... but what if you decide to ONLY show the year, or just year/month, ...? You can use Wildcards on text, but not numbers.

With this method, you can filter on just what is specified <smile>

this is what I do in that case;
http://www.mrexcel.com/forum/showthread.php?p=1978163#post1978163

I can filter on just what is specified, I can do it with numbers and dates and I don't have to use like even with text (unless I wanted to)

I've never heard if that way has any problems or heard of comparisons to filters
I've wondered if my way with form criteria specified in the query was wrong somehow, but I haven't had any problems yet
</smile>

 

[strive4peace]

Hi James,

cool solution!

 

[topdesk123]

Hello!

I borrowed this code for filtering my form. But it is not working with null values. Can anyone see why? My comboboxes are named statusfilter, cityfilter, zonefilter, and biddatefilter. Thank you in advance!

Private Function SetFormFilter()
   On Error GoTo errHandler
 

   Dim varFilter As Variant
   varFilter = Null
   
   If Not IsNull(Me.statusfilter) Then
      varFilter = (varFilter + " AND ") _
         & "[status]= '" & Me.statusfilter & "'"
     
   End If
   
   Debug.Print varFilter
   
   If Not IsNull(Me.biddatefilter) Then
      varFilter = (varFilter + " AND ") _
         & "[biddate]= #" & Me.biddatefilter & "#"
   End If
    Debug.Print varFilter
    
   If Not IsNull(Me.zonefilter) Then
      varFilter = (varFilter + " AND ") _
         & "[zone]= " & Me.zonefilter
   End If
     
     Debug.Print varFilter
   
     If Not IsNull(Me.cityfilter) Then
      varFilter = (varFilter + " AND ") _
         & "[city]= " & Me.cityfilter
   End If
   
   Debug.Print varFilter
   
   With Me
       If Not IsNull(varFilter) Then
          .Filter = varFilter
          .FilterOn = True
      Else
          .FilterOn = False
      End If
      .Requery
   End With
   
 Debug.Print varFilter

 Exit Function
errHandler:
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

End Function

I am getting these results upon debug.print "[status]= '' AND [biddate]= ## AND [zone]= AND [city]= Afton"

 

[strive4peace]

"" ... "" is a value. It is called a zero-length string and is not the same as Null.

When you set and reset the controls:

me.controlname = null

then the controls won't have a value and the SetFormFilter code should work properly

 

[strive4peace]

I believe you are also missing delimiters around CITY

     If Not IsNull(Me.cityfilter) Then       
        varFilter = (varFilter + " AND ") _          
        & "'" & Me.cityfilter & "'"
    end if

Is Zone a number?