Trying to do a form filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that has data from different years on it. I would like to be
able to create a filter (drop down list, command button doesn't matter) so
only the year I wish to view appears. So far I have not been able to get it
to work - please help!!
 
Create an unbound combo on the form where you select the year.

Create a filter in the record source of the form that refer to the combo in
the form

Where YearField = Forms![MainFormName]![ComboName]

On the After Update event of the combo you need to refresh the form
Me.Requery
 
You can set up a drop-down box with the years as the source. (I would suggest
setting the source of the drop-down to a query based on your main table with
a group-by on the year. That way your list would only contain the possible
years.) Then create an after update event with code similar to this:

Me.Filter = "[YEAR_FIELD] = " & drpYear.Value
Me.FilterOn = True
 
what I have is a table (showing daily ave. temps with the date - mm/dd
separated by the year). I made a form that lists all the dates. Now, I
would like to create either another form that lists the years and if you
click on that year it goes to my original form showing only that year. Or on
the form that shows all the dates, I have a display showing the year but it
only shows the first year in the table/form. If I could have a drop list
there showing all the available dates would be the best option. I am just
learning, so forgive me if this is simple!
 
When I do this it lists the years, but since I have 365 temps per year it
will list the year 365 times. Also if I click on a year it does nothing.

Otyokwa said:
what I have is a table (showing daily ave. temps with the date - mm/dd
separated by the year). I made a form that lists all the dates. Now, I
would like to create either another form that lists the years and if you
click on that year it goes to my original form showing only that year. Or on
the form that shows all the dates, I have a display showing the year but it
only shows the first year in the table/form. If I could have a drop list
there showing all the available dates would be the best option. I am just
learning, so forgive me if this is simple!

guido via AccessMonster.com said:
You can set up a drop-down box with the years as the source. (I would suggest
setting the source of the drop-down to a query based on your main table with
a group-by on the year. That way your list would only contain the possible
years.) Then create an after update event with code similar to this:

Me.Filter = "[YEAR_FIELD] = " & drpYear.Value
Me.FilterOn = True
Click to expand...
Click to expand...
 
Change the query for the drop-down box to be a "Group By" query (Select
"Totals" from the "View" menu). This will only show unique values. Also, for
this to work, the form record source must be set to be the desired table. I
also want to make sure that you exchanged "YEAR_FIELD" for your field name.
Second, this will work if the year field type is a number. If it is text, the
line would be:
Me.Filter = "[YEAR_FIELD] = (single quote)" & drpYear.Value & "(single quote)
"
=> Of course replace (single quote) with ' - it was hard to see if I just
typed them in above.
 
Back
Top