473,379 Members | 1,423 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

How to "Select All" in filtered recordset

(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
Nov 13 '05 #1
6 9273
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

Nov 13 '05 #2
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


Nov 13 '05 #3
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


Nov 13 '05 #4
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



Nov 13 '05 #5
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



Nov 13 '05 #6
"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
>
>



Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
4
by: Matt | last post by:
In ASP page, there is a "SELECT ALL" button, when user click it, it will select all checkboxes. I am not sure should I use client-side code to do that? the following is my approach but it didnt...
4
by: Nuno | last post by:
Is there any SQL Error? Or I have to use Select case in VB code to control SQL instead. Thank you for any ans. Nuno
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
2
by: google | last post by:
Hello everyone, I am having an issue using the "Multi Select" option in a list box in MS Access 2003. I am making a form that users can fill out to add an issue to the database. Each issue can...
3
by: divya | last post by:
Hi, I have a table tblbwday with 2 fields Name and Birthday.I have written this script for displaying evryday names of the people on that day. <% set objConn...
7
by: php_mysql_beginer911 | last post by:
Hi .. hope someone will help i am trying to figure it out why i cannot post string "union select" every time i try to post data which content union and select .. the page doesn't get posted and...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.