How to "Select All" in filtered recordset

[GSteven]

(as formerly posted to microsoft.public.access.forms with no result)

I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is a checkbox with a
control source named "MARK" (boolean) from customer table. I can check and
uncheck individual records fine. Then I created 2 command buttons named
"Select All" and "Deselect All". The Onclick property of these buttons runs
code which calls an update query. All works as expected except that I wanted
to be able to select or deselect a filtered view of the records.

When I apply a filter and select all, ALL records are selected instead of
just the records as viewed in the filtered set.

Anyone know of a way to improve upon this?

tia
Steve

[Wayne Morgan]

You will need to apply the filter constraints in the Where clause of your
Update query to limit that query to only updating the records that match the
filter. For example, if you've filter the form to only show you records
where "Field1 = 2", then in the update query you would need "WHERE Field1 =
2". You will also need to limit the update query to the current value of the
Master/Child link fields since this will also be limiting the subform's
recordset. You would add this in using an AND statement in the Where clause.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

(as formerly posted to microsoft.public.access.forms with no result)

I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is a checkbox with a
control source named "MARK" (boolean) from customer table. I can check and
uncheck individual records fine. Then I created 2 command buttons named
"Select All" and "Deselect All". The Onclick property of these buttons
runs
code which calls an update query. All works as expected except that I
wanted
to be able to select or deselect a filtered view of the records.

When I apply a filter and select all, ALL records are selected instead of
just the records as viewed in the filtered set.

Anyone know of a way to improve upon this?

tia
Steve

[GSteven]

Wayne,

I appreciate your reply. But your answer leads to yet another question. The
idea here is for the EU to be able to filter the customer table (using the
filter by selection/exclusion toolbar) in order to choose who to schedule
for service. I can make the query do what you suggested with hard code but
how can I build the WHERE clause dynamically?

btw - I'm running Access 2000, have considerable SQL experience but am just
learning Access and VB.

Thanks again,
Steve

************************************************** ************************
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:p7****************@newssvr12.news.prodigy.com ...

You will need to apply the filter constraints in the Where clause of your
Update query to limit that query to only updating the records that match
the filter. For example, if you've filter the form to only show you
records where "Field1 = 2", then in the update query you would need "WHERE
Field1 = 2". You will also need to limit the update query to the current
value of the Master/Child link fields since this will also be limiting the
subform's recordset. You would add this in using an AND statement in the
Where clause.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

(as formerly posted to microsoft.public.access.forms with no result)

I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is a checkbox with a
control source named "MARK" (boolean) from customer table. I can check
and
uncheck individual records fine. Then I created 2 command buttons named
"Select All" and "Deselect All". The Onclick property of these buttons
runs
code which calls an update query. All works as expected except that I
wanted
to be able to select or deselect a filtered view of the records.

When I apply a filter and select all, ALL records are selected instead of
just the records as viewed in the filtered set.

Anyone know of a way to improve upon this?

tia
Steve

[GSteven]

Is there a way to pass the Form.Filter object (found in the object browser)
to the SQL??
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:p7****************@newssvr12.news.prodigy.com ...

You will need to apply the filter constraints in the Where clause of your
Update query to limit that query to only updating the records that match
the filter. For example, if you've filter the form to only show you
records where "Field1 = 2", then in the update query you would need "WHERE
Field1 = 2". You will also need to limit the update query to the current
value of the Master/Child link fields since this will also be limiting the
subform's recordset. You would add this in using an AND statement in the
Where clause.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

(as formerly posted to microsoft.public.access.forms with no result)

I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is a checkbox with a
control source named "MARK" (boolean) from customer table. I can check
and
uncheck individual records fine. Then I created 2 command buttons named
"Select All" and "Deselect All". The Onclick property of these buttons
runs
code which calls an update query. All works as expected except that I
wanted
to be able to select or deselect a filtered view of the records.

When I apply a filter and select all, ALL records are selected instead of
just the records as viewed in the filtered set.

Anyone know of a way to improve upon this?

tia
Steve

[Wayne Morgan]

Yes, you can build the SQL dynamically. Where the problem comes in is that
you will have to allow for all of the possible combinations. Getting the
form's Filter value, as you mentioned, may be a good way to do this. The
Filter value will have the text needed for the Where statement, just without
the word Where.

Example:
strFilter = Replace(Me.Filter, Me.RecordSource & ".", "")
strSQL = "UPDATE Table1 SET Table1.Myfield = False WHERE LinkField = " &
Me.txtLinkField & " And " & strFilter & ";"
CurrentDb.Execute strSQL, dbFailOnError

Of course, you'll need to adjust for different data types by concatenating
in quotes, if needed. The reason for the Replace function is that the filter
lists the name of the record source (i.e. Query1.Field1 = 6) instead of just
the field. I haven't tried this, so this may or may not be a problem. I
suspect it wouldn't matter, but if it does, you can use the Replace function
to remove it.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

Wayne,

I appreciate your reply. But your answer leads to yet another question.
The idea here is for the EU to be able to filter the customer table (using
the filter by selection/exclusion toolbar) in order to choose who to
schedule for service. I can make the query do what you suggested with hard
code but how can I build the WHERE clause dynamically?

btw - I'm running Access 2000, have considerable SQL experience but am
just learning Access and VB.

Thanks again,
Steve

************************************************** ************************
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:p7****************@newssvr12.news.prodigy.com ...

You will need to apply the filter constraints in the Where clause of your
Update query to limit that query to only updating the records that match
the filter. For example, if you've filter the form to only show you
records where "Field1 = 2", then in the update query you would need
"WHERE Field1 = 2". You will also need to limit the update query to the
current value of the Master/Child link fields since this will also be
limiting the subform's recordset. You would add this in using an AND
statement in the Where clause.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

(as formerly posted to microsoft.public.access.forms with no result)

I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is a checkbox with a
control source named "MARK" (boolean) from customer table. I can check
and
uncheck individual records fine. Then I created 2 command buttons named
"Select All" and "Deselect All". The Onclick property of these buttons
runs
code which calls an update query. All works as expected except that I
wanted
to be able to select or deselect a filtered view of the records.

When I apply a filter and select all, ALL records are selected instead
of
just the records as viewed in the filtered set.

Anyone know of a way to improve upon this?

tia
Steve

[GSteven]

Thanks again Wayne for your replies. This got me over this hump and I
learned something about the "Me" local variable set in the process. However,
the references you made to the LinkField I don't get, so I commented that
out of my code. I couldn't find a reference to LinkField in my locals
window. What is it?

And you know of course that I'm trying to get a handle on Access/VB the same
way man tries to figure out the universe. Just contemplate it long enough
and the answersr should be obvious, right? ;) Well, maybe not! Do you know
of any excellent reference manuals for Access/VB? I already have "Running
Microsoft Access" published by Microsoft but I find it lacking indepth
demonstration or explanation of how these internal objects work.

Steve
************************************************** *******************************
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:y2*****************@newssvr30.news.prodigy.co m...

Yes, you can build the SQL dynamically. Where the problem comes in is that
you will have to allow for all of the possible combinations. Getting the
form's Filter value, as you mentioned, may be a good way to do this. The
Filter value will have the text needed for the Where statement, just
without the word Where.

Example:
strFilter = Replace(Me.Filter, Me.RecordSource & ".", "")
strSQL = "UPDATE Table1 SET Table1.Myfield = False WHERE LinkField = " &
Me.txtLinkField & " And " & strFilter & ";"
CurrentDb.Execute strSQL, dbFailOnError

Of course, you'll need to adjust for different data types by concatenating
in quotes, if needed. The reason for the Replace function is that the
filter lists the name of the record source (i.e. Query1.Field1 = 6)
instead of just the field. I haven't tried this, so this may or may not be
a problem. I suspect it wouldn't matter, but if it does, you can use the
Replace function to remove it.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

Wayne,

I appreciate your reply. But your answer leads to yet another question.
The idea here is for the EU to be able to filter the customer table
(using the filter by selection/exclusion toolbar) in order to choose who
to schedule for service. I can make the query do what you suggested with
hard code but how can I build the WHERE clause dynamically?

btw - I'm running Access 2000, have considerable SQL experience but am
just learning Access and VB.

Thanks again,
Steve

************************************************** ************************
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:p7****************@newssvr12.news.prodigy.com ...

You will need to apply the filter constraints in the Where clause of
your Update query to limit that query to only updating the records that
match the filter. For example, if you've filter the form to only show
you records where "Field1 = 2", then in the update query you would need
"WHERE Field1 = 2". You will also need to limit the update query to the
current value of the Master/Child link fields since this will also be
limiting the subform's recordset. You would add this in using an AND
statement in the Where clause.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...
(as formerly posted to microsoft.public.access.forms with no result)

I've created a continuous form which is based on a straightforward
table
(ex - customers - 100 records). On the form there is a checkbox with a
control source named "MARK" (boolean) from customer table. I can check
and
uncheck individual records fine. Then I created 2 command buttons named
"Select All" and "Deselect All". The Onclick property of these buttons
runs
code which calls an update query. All works as expected except that I
wanted
to be able to select or deselect a filtered view of the records.

When I apply a filter and select all, ALL records are selected instead
of
just the records as viewed in the filtered set.

Anyone know of a way to improve upon this?

tia
Steve

[Wayne Morgan]

"LinkedField" needs to be replaced with the actual name of your linking
field in the Parent/Child link fields for the main form/subform setup. This
link filters the records in your subform to limit them to records that are
associated with the current main form record. However, when I go back and
reread your post, I don't see that you mentioned a form/subform setup, so
you can probably ignore that.

For a few good books, try

Microsoft Office Access 2003 Inside Out
http://www.viescas.com/Info/books.htm
(he also has a new book on this site that I haven't read yet)

Access 2002 ____ Developer's Handbook
http://www.developershandbook.com

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d8**********@news3.infoave.net...

Thanks again Wayne for your replies. This got me over this hump and I
learned something about the "Me" local variable set in the process.
However, the references you made to the LinkField I don't get, so I
commented that out of my code. I couldn't find a reference to LinkField in
my locals window. What is it?

And you know of course that I'm trying to get a handle on Access/VB the
same way man tries to figure out the universe. Just contemplate it long
enough and the answersr should be obvious, right? ;) Well, maybe not! Do
you know of any excellent reference manuals for Access/VB? I already have
"Running Microsoft Access" published by Microsoft but I find it lacking
indepth demonstration or explanation of how these internal objects work.

Steve
************************************************** *******************************
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:y2*****************@newssvr30.news.prodigy.co m...

Yes, you can build the SQL dynamically. Where the problem comes in is
that you will have to allow for all of the possible combinations. Getting
the form's Filter value, as you mentioned, may be a good way to do this.
The Filter value will have the text needed for the Where statement, just
without the word Where.

Example:
strFilter = Replace(Me.Filter, Me.RecordSource & ".", "")
strSQL = "UPDATE Table1 SET Table1.Myfield = False WHERE LinkField = " &
Me.txtLinkField & " And " & strFilter & ";"
CurrentDb.Execute strSQL, dbFailOnError

Of course, you'll need to adjust for different data types by
concatenating in quotes, if needed. The reason for the Replace function
is that the filter lists the name of the record source (i.e.
Query1.Field1 = 6) instead of just the field. I haven't tried this, so
this may or may not be a problem. I suspect it wouldn't matter, but if it
does, you can use the Replace function to remove it.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...

Wayne,

I appreciate your reply. But your answer leads to yet another question.
The idea here is for the EU to be able to filter the customer table
(using the filter by selection/exclusion toolbar) in order to choose who
to schedule for service. I can make the query do what you suggested with
hard code but how can I build the WHERE clause dynamically?

btw - I'm running Access 2000, have considerable SQL experience but am
just learning Access and VB.

Thanks again,
Steve

************************************************** ************************
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:p7****************@newssvr12.news.prodigy.com ...
You will need to apply the filter constraints in the Where clause of
your Update query to limit that query to only updating the records that
match the filter. For example, if you've filter the form to only show
you records where "Field1 = 2", then in the update query you would need
"WHERE Field1 = 2". You will also need to limit the update query to the
current value of the Master/Child link fields since this will also be
limiting the subform's recordset. You would add this in using an AND
statement in the Where clause.

--
Wayne Morgan
MS Access MVP
"GSteven" <st***@s1f-w1yr5cycl5r.com> wrote in message
news:d7***********@news3.infoave.net...
> (as formerly posted to microsoft.public.access.forms with no result)
>
> I've created a continuous form which is based on a straightforward
> table
> (ex - customers - 100 records). On the form there is a checkbox with a
> control source named "MARK" (boolean) from customer table. I can check
> and
> uncheck individual records fine. Then I created 2 command buttons
> named
> "Select All" and "Deselect All". The Onclick property of these buttons
> runs
> code which calls an update query. All works as expected except that I
> wanted
> to be able to select or deselect a filtered view of the records.
>
> When I apply a filter and select all, ALL records are selected instead
> of
> just the records as viewed in the filtered set.
>
> Anyone know of a way to improve upon this?
>
> tia
> Steve
>
>