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

the core issue revolves 'round what happens on REMOVING the filter alone,
allen. it (the code i described early in this thread) seems to work
successfully in filtering the subform's records. it's when you click on it a
2nd time to remove the filter that it scoots the user to the mainform and the
first recdord in its underlying control source.

here's what i just tested in its entirety
Private Sub FilterDates_Click()
On Error GoTo Err_FilterDates_Click

Dim rs As DAO.Recordset
Dim strWhere As String
Dim frm As Form

Set frm = Me.DaysView.Form
strWhere = "([Last Name] = """ & frm![LastName] & _
""") AND ([First Name] = """ & frm!First_Name & _
""") AND ([MI] = """ & frm![M_I] & _
""") AND ([MR_Number] = " & frm![MRNumber] & _
") AND ([IRB Number] = """ & frm![IRBNumber] & """)"
'Debug.Print strWhere
frm.Requery
Set rs = -frm.RecordsetClone
rs.FindFirst strWhere
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If


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

which on clicking the cmdbutton resulted in a message/error window's
appearing declaring : "Automation Error" when i click the cmdbutton to FILTER
the subform's records. i also noticed that it scooted the cursor to the 1st
of the subform's records.

-ted



"Allen Browne" wrote:

> If I understand you correctly, the core issue is that when you apply or
> remove the filter, you are taken to the first record, and you want to keep
> the current one. Is that right?
>
> If that's the idea, you could build the Where clause you need for the
> FindFirst before the Requery, and apply it afterwards. The string has to
> look exactly like the WHERE clause in a query statement. You can print it to
> the debug window to see what it looks like and compare the result to a
> mocked up query if it helps to develop it.
>
> The Text based fields need the extra quotes marks. Parentheses are optional.
> Square brackets are needed around the field names that contain spaces.
>
> Something like this:
>
> Dim rs As DAO.Recordset
> Dim strWhere As String
> Dim frm As Form
>
> Set frm = Me.DaysView.Form
> strWhere = "([Last Name] = """ & frm![LastName] & _
> """) AND ([First Name] = """ & frm!First_Name & _
> """) AND ([MI] = """ & frm![M_I] & _
> """) AND ([MR_Number] = " & frm![MRNumber] & _
> ") AND ([IRB Number] = """ & frm![IRBNumber] & """)"
> 'Debug.Print strWhere
> frm.Requery
> Set rs =- frm.RecordsetClone
> rs.FindFirst strWhere
> If Not rs.NoMatch Then
> frm.Bookmark = rs.Bookmark
> End If
>
> --
> 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:F738BA71-0BC1-4CA7-9FF0-42317DEC6D26@xxxxxxxxxxxxxxxx
> > 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