Re: filtering records in sub-form has problems in a2k

You will need to look up the VBA help on the keywords I suggested. Set the
Filter property of:
Me.DaysView.Form

FindFirst in its RecordsetClone.
Test NoMatch.
Set the Bookmark.

There's an example of this kind of code in:
http://allenbrowne.com/ser-03.html
Since you are working with a subform, you will need to replace:
Me
with:
Me.DaysView.Form
throughout that code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:55C7F686-4CFB-4329-B023-39545BDA5FF3@xxxxxxxxxxxxxxxx
> allen, i appreciate the insight into 'the mind' of a2k's. clicking on the
> cmd
> button i created for the filtering task on the mainform seems to produce
> the
> desired effect, i.e. filtering records of the subform's meeting the
> critierion specified. (vba relative newbie that i am) how would one get
> vba
> to tell it get on with all that other business when removing the filter
> (assuming that the subform's control source shares the same name as the
> subform itself, i.e. 'DaysView')?
>
> -ted
>
> "Allen Browne" wrote:
>
>> When you apply or remove a filter, Access reloads the form to get the
>> matching records. As a result, you are taken to the first record in the
>> form. That's how it works, and you cannot change that if you use the
>> built-in filter buttons (toolbar).
>>
>> If you apply or remove the filter in code, you can save the primary key
>> value of the current record into a variable, apply/remove the filter, and
>> then FindFirst in the form's RecordsetClone. If found (not NoMatch), set
>> the
>> form's Bookmark to that of the clone to move to that record again.
>>
>> "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:095518B7-FAC9-408B-A66E-B6346AFA1CF4@xxxxxxxxxxxxxxxx
>> >i use the following vba code in a button on the mainform ("Screening Log
>> > (Review Only)" in my a2k application:
>> >
>> > Private Sub FilterDates_Click()
>> > On Error GoTo Err_FilterDates_Click
>> >
>> > With Me.[DaysView].Form
>> > If .FilterOn Then
>> > .FilterOn = False 'Turn the filter off.
>> > lngGreen = RGB(0, 150, 0)
>> > Me.FilterDates.ForeColor = lngGreen
>> > Else
>> > .Filter = "[DateOfVisit] >= Date()"
>> > .FilterOn = True
>> > lngRed = RGB(225, 0, 0)
>> > Me.FilterDates.ForeColor = lngRed
>> > End If
>> > End With
>> >
>> > If Me.FilterDates.ForeColor = lngRed Then
>> > Me.FilterLbl.Visible = True
>> > Me.Close.Visible = False
>> > Me.NavigationButtons = False
>> > Else
>> > Me.FilterLbl.Visible = False
>> > Me.Close.Visible = True
>> > Me.NavigationButtons = True
>> > End If
>> >
>> > Exit_FilterDates_Click:
>> > Exit Sub
>> >
>> > Err_FilterDates_Click:
>> > MsgBox Err.description
>> > Resume Exit_FilterDates_Click
>> >
>> > End Sub
>> >
>> > the button is intended to limit the user's ability to view records in a
>> > nested/child subform (called 'DaysView') which had 'DateOfVisit'
>> > earlier
>> > than
>> > "today's", or to put it another way, the intent is to view only records
>> > in
>> > 'DaysView' dated "today" and after. the rest is pretty much "there" for
>> > the
>> > interested reader to see for himself.
>> >
>> > the point of this posting is attributable to what i feel is a quirky
>> > way
>> > that the applcation behaves when you click the filter button a 2nd
>> > time,
>> > i.e.
>> > to remove the filter from the 'DaysView' subform. what i mean by that
>> > is
>> > that
>> > even if your cursor is positioned on a control in the subform, when you
>> > 'unfilter' it, the next thing the user sees is that he's been
>> > transported
>> > to
>> > the first control on the "Screening Log (Review Only)" main form and
>> > that
>> > the
>> > main form is displaying the first record in the database's data. this
>> > is
>> > kind
>> > of a hassle for the user, since, he or she needs to manually locate the
>> > appropriate record in the database in order to be able to view its
>> > subform's
>> > data. does this make sense? does it have some way to shortcircuit that
>> > behavior?
>> >
>> > thanks in advance,
>> >
>> >
>> >
>> > when the filter is activated i want a label appearing above the
>> > filtering
>> > button (both of which are sitting on the mainform) to appear and for
>> > the
>> > filterinng buttons text to display in red.


.

Loading