(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
6  9283 
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,
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
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
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
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
"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
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
