filtering records in sub-form has problems in a2k

[Ted]

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.

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Ted]

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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.

 

[Ted]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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.

 

[Ted]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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.

 

[Ted]

did you really mean for there to be a dash ("-") before the

Set rs =- frm.RecordsetClone

above?

i tried removing it and re-running the VBA and to all eyes, it seemed to
behave no differently than before (i.e. before i added it to my VBA).

-ted

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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.

 

[Allen Browne]

No: the dash should not be there.

 

[Allen Browne]

So your code is removing the main form's filter, whereas you want it to
remove the subform's filter?

 

[Ted]

the code filters just the subform's records and clicking the cmdbutton will
disable the filtration.

-ted

 

[Allen Browne]

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

 

[Ted]

allen, i will definitely have a 'go' at it and get back w/ whate'er happens.

many thanks,

-ted

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

the code filters just the subform's records and clicking the cmdbutton
will
disable the filtration.

-ted

 

[Ted]

allen, how would i integrate the code you generously give into my VBA's.
please be specific. sorry 'bout the naivete, but i'm still lobbying for a
chance to attend a half-decent course in it.

-ted

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

the code filters just the subform's records and clicking the cmdbutton
will
disable the filtration.

-ted

 

[Allen Browne]

Put it in the Click event of the command button you have for this purpose.

I should have also mentioned that when you change a subform's RecordSource,
Access is likely to reset the subform control's LinkMasterFields and
LInkChildFields to whatever it wishes, so you may need to reset these 2
properties as well.

That's probably as far as I can take you with this one, Ted. Hope you are
able to get into a decent course.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

allen, how would i integrate the code you generously give into my VBA's.
please be specific. sorry 'bout the naivete, but i'm still lobbying for a
chance to attend a half-decent course in it.

-ted

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the
main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the
subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

the code filters just the subform's records and clicking the cmdbutton
will
disable the filtration.

-ted

:

So your code is removing the main form's filter, whereas you want it
to
remove the subform's filter?


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.

 

[Ted]

imho, this is starting to sound like the act of opening one of those russian
dolls, each time you do, it leads to yet another (albeit smaller) one :-(

thanks anyway. i'm going to try my 'plan b'.

-ted

Put it in the Click event of the command button you have for this purpose.

I should have also mentioned that when you change a subform's RecordSource,
Access is likely to reset the subform control's LinkMasterFields and
LInkChildFields to whatever it wishes, so you may need to reset these 2
properties as well.

That's probably as far as I can take you with this one, Ted. Hope you are
able to get into a decent course.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

allen, how would i integrate the code you generously give into my VBA's.
please be specific. sorry 'bout the naivete, but i'm still lobbying for a
chance to attend a half-decent course in it.

-ted

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the
main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the
subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

the code filters just the subform's records and clicking the cmdbutton
will
disable the filtration.

-ted

:

So your code is removing the main form's filter, whereas you want it
to
remove the subform's filter?


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.

 

[Ted]

just a quick f/u to my last reply to your posting....

i managed to get this to work:

Private Sub FilterDates_Click()
On Error GoTo Err_FilterDates_Click

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

Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE [DateOfVisit]

= Date();"

With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
.FilterOn = False 'Turn the filter off.
lngGreen = RGB(0, 150, 0)
Me.FilterDates.ForeColor = lngGreen


Else
.RecordSource = strcFiltered
.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

once i set the "DaysView" subform's control source to

SELECT DaysView.* FROM DaysView


-ted

Put it in the Click event of the command button you have for this purpose.

I should have also mentioned that when you change a subform's RecordSource,
Access is likely to reset the subform control's LinkMasterFields and
LInkChildFields to whatever it wishes, so you may need to reset these 2
properties as well.

That's probably as far as I can take you with this one, Ted. Hope you are
able to get into a decent course.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

allen, how would i integrate the code you generously give into my VBA's.
please be specific. sorry 'bout the naivete, but i'm still lobbying for a
chance to attend a half-decent course in it.

-ted

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the
main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the
subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

the code filters just the subform's records and clicking the cmdbutton
will
disable the filtration.

-ted

:

So your code is removing the main form's filter, whereas you want it
to
remove the subform's filter?


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.

 

[Allen Browne]

i managed to get this to work:

Goodonya!

 

[Ted]

don't take this too literally allen, but your blood's worth bottling ;-)

-ted