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

Allen,

I’ve been ooo this past week and wanted to get back on this thread with
you....

The elements that go into making the PK of the “DaysView” underlying table’s
(which is also named ‘DaysView’) are

“Last Name” (text), “First Name” (text) , “MI” (text) , “MR_Number”
(number), “IRB Number” (text), and “RecordNumber” (number). The names of the
said control sources on the subform are (respectively): “LastName”,
“First_Name”,”M_I”,”MRNumber”,”IRBNumber”,and lastly ”Visit”

I have looked at your website’s tip you mention and (newbie to vba that I
am) am confused at a higher level J
I’m not altogether sure what it has got to do with the topic of this
thread’s for which I guess my unfamiliarity’s to blame.

As you saw from the VBA code I attached along with my posting, I am using
some code on a command button I created on the main form (which is called
“Screening Log (Review Only)”) to do the job of filtering the records in the
“DaysView” subform having “DateOfVisit” occurring earlier than the date the
user’s viewing the “DaysView” subform along with changing the color of the
cmdbutton’s label/text.

In trying to fathom the relevance of your “Tip’s” example for this thread,
I’m guessing my VBA would need the following ingredients as well:

Dim rs As DAO.Recordset

Set rs = DaysView.RecordsetClone

rs.FindFirst = "[Last Name] = " & [DaysView]![LastName] & "[First Name] = "
& [DaysView]![FirstName] & "[MI] = " & [DaysView]![M_I] & "[MR_Number] = " &
[DaysView]![MRNumber] & "[IRB Number] = " & [DaysView]![IRBNumber]”

If rs.NoMatch then
MsgBox “Not found: filtered”
Else
Me.Bookmark = rs.Bookmark
End If

Set rs=Nothing

I understand your point in your first response about how A2K reloads the
form to get the matching records and how it'll take you to the first record
in the form (although I am not sure that I'm using what you called a
"bulit-in filter buttons (toolbar)" since I wrote this code to the OnClick
event of a cmdbutton I placed on the mainform of the subform) and how you
need to save the primary key of the current record into a variable,
apply/remove the filter, and then FindFirst in the form's RecordSetClone, and
so forth, but absent a formal training course in this lingo/VBA using the
syntax/words is a bit of a guessing game, so any help with more structure'd
be greatly appreciated.

Best,

-ted


"Allen Browne" wrote:

> 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