Form Filter

[ajorge]

I have a form that is basically for input information only, the problem is that now it has to be filtered to only allow the year the user wants to work in. On open I am asking the user to select the year in drop down list and assigning it to a global var. how do I setup the code in VBA

 

[FancyPrairie]

After the user tells you want year they want, either set the RecordSource of the form equal to a SQL statement like this:

"Select * from tblBlah Where Year = " & YearSelected

(Syntax not right, but you get the idea)

OR

Set the filter property of the Form to something like this:

Me.Filter "Year = " & YearSelected
Me.FilterOn = True

 

[ajorge]

I'm using the filter, but it only looking at the first record in the record source

 

[FancyPrairie]

Post your code and we'll look at.

 

[ajorge]

This is the code I have. Report year is the year that was assigned to each record the user enters. workyear is the global var. that is assigned once the use enters the year

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
[Setup Master]![Report Year] = [WorkYear]

End Sub

 

[FancyPrairie]

Your not setting the Filter. The function "Form_Filter" is executed when the filter is edited. To change the filter, you need to set the form's filter property and then make sure it's on.

Me.Filter "[Setup Master]![Report Year] = " & [WorkYear]
Me.FilterOn = True

This assumes that [Setup Master] is the name of your query or table. [Report Year] is a field within the table or query. And [WorkYear] is a field on the form.

I would put the above code in the AfterUpdate event of the text box [WorkYear].

 

[ajorge]

Everything looks right except the fact that workyear is a global var. and is not display in any records its only stored until the year is change again

 

[sonso]

How can you set the filter for when the form opens? In Paradox OPAL there is a method open(), but I can't find this in Access (97). Also, if you place fields individually in the detail record, it doesn't appear that there are any update type methods available (I get only mouse related events). Lastly, if the filter is to be set on a subform, should the code be in the main form, addressing the subform or should it be in the subform itself?

 

[FancyPrairie]

1. In the OnOpen event of the form is where you can set the filter.
2. On fields you have several events (i.e. BeforeUpdate and AfterUpdate events). You also have the same events for the record (form) as whole.
3. Doesn't matter whether you do it in the subform or form.