PC Review


Reply
Thread Tools Rate Thread

select all filtered records

 
 
=?Utf-8?B?TGFuYQ==?=
Guest
Posts: n/a
 
      29th Jun 2005
Hi all,

I have a continuous form with some buttons which allow to apply different
filteres to this form.

I have a check-box which allows me to select the records i like.
I have a button which allows me to deselect all previously selected records.

Now I would like to have a button which adds all filtered records to my
selected list.
Is that possible? (given that i use different filters all the time)

in other words I want to be able to apply 1 filter and manually select 2 or
3 records from the results, then apply another filter and select all the
records from there by pressing some button, and then go to another filter...

Please can somebody help me?
Thank you.
Lana
 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      29th Jun 2005
Sounds like you already have a yes/no field in your table, for making a
record as chosen.

Now you want to use the Filter of the form to mark all those as chosen.

Assuming a table named Table1, and a yes/no field named IsPicked:

Dim strSql as String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET IsPicked = True WHERE " & Me.Filter & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.Requery 'Show the changed state in the form.

That should work, unless you are using FilterByForm with some lookup combos
that have a zero width bound column. If so, you will need to craft the
UPDATE statement to that it includes the table that you need to filter on,
or parse the Filter statement, and resolve it into the correct bound field
instead of the lookup values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lana" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all,
>
> I have a continuous form with some buttons which allow to apply different
> filteres to this form.
>
> I have a check-box which allows me to select the records i like.
> I have a button which allows me to deselect all previously selected
> records.
>
> Now I would like to have a button which adds all filtered records to my
> selected list.
> Is that possible? (given that i use different filters all the time)
>
> in other words I want to be able to apply 1 filter and manually select 2
> or
> 3 records from the results, then apply another filter and select all the
> records from there by pressing some button, and then go to another
> filter...
>
> Please can somebody help me?
> Thank you.
> Lana



 
Reply With Quote
 
 
 
 
=?Utf-8?B?TGFuYQ==?=
Guest
Posts: n/a
 
      30th Jun 2005
hi Allen!

thank you for spending your time answering my question.

may be i didnt understand correctly what you were suggesting, but when i
created a button with the following event procedure on click:

Private Sub Command112_Click()
On Error GoTo Err_Command112_Click
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Me.Requery 'Show the changed state in the form.
Exit_Command112_Click:
Exit Sub
Err_Command112_Click:
MsgBox Err.Description
Resume Exit_Command112_Click
End Sub


and tried to run it upon my filter I got the following message: "Syntax
error in UPDATE statement."

then i changed the line
strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
to
strSql = "UPDATE [Codes] SET [Select] = True WHERE " & Me.Filter & ";"

when tried to run it, got the message "Too few parameters. Expected 1."

What I have done wrong?

Lana


"Allen Browne" wrote:

> Sounds like you already have a yes/no field in your table, for making a
> record as chosen.
>
> Now you want to use the Filter of the form to mark all those as chosen.
>
> Assuming a table named Table1, and a yes/no field named IsPicked:
>
> Dim strSql as String
> If Me.Dirty Then Me.Dirty = False 'Save first.
> strSql = "UPDATE Table1 SET IsPicked = True WHERE " & Me.Filter & ";"
> dbEngine(0)(0).Execute strSql, dbFailOnError
> Me.Requery 'Show the changed state in the form.
>
> That should work, unless you are using FilterByForm with some lookup combos
> that have a zero width bound column. If so, you will need to craft the
> UPDATE statement to that it includes the table that you need to filter on,
> or parse the Filter statement, and resolve it into the correct bound field
> instead of the lookup values.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lana" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi all,
> >
> > I have a continuous form with some buttons which allow to apply different
> > filteres to this form.
> >
> > I have a check-box which allows me to select the records i like.
> > I have a button which allows me to deselect all previously selected
> > records.
> >
> > Now I would like to have a button which adds all filtered records to my
> > selected list.
> > Is that possible? (given that i use different filters all the time)
> >
> > in other words I want to be able to apply 1 filter and manually select 2
> > or
> > 3 records from the results, then apply another filter and select all the
> > records from there by pressing some button, and then go to another
> > filter...
> >
> > Please can somebody help me?
> > Thank you.
> > Lana

>
>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      30th Jun 2005
You have a field named "Select"?

That's a reserved word in SQL, e.g.
SELECT * FROM MyTable;

Firstly, make sure that the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Then change the field name in your table.
Then change the Name and ControlSource of the control on the form.
Then change the query statement to use the new name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lana" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> hi Allen!
>
> thank you for spending your time answering my question.
>
> may be i didnt understand correctly what you were suggesting, but when i
> created a button with the following event procedure on click:
>
> Private Sub Command112_Click()
> On Error GoTo Err_Command112_Click
> Dim strSql As String
> If Me.Dirty Then Me.Dirty = False 'Save first.
> strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
> DBEngine(0)(0).Execute strSql, dbFailOnError
> Me.Requery 'Show the changed state in the form.
> Exit_Command112_Click:
> Exit Sub
> Err_Command112_Click:
> MsgBox Err.Description
> Resume Exit_Command112_Click
> End Sub
>
>
> and tried to run it upon my filter I got the following message: "Syntax
> error in UPDATE statement."
>
> then i changed the line
> strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
> to
> strSql = "UPDATE [Codes] SET [Select] = True WHERE " & Me.Filter & ";"
>
> when tried to run it, got the message "Too few parameters. Expected 1."
>
> What I have done wrong?
>
> Lana
>
>
> "Allen Browne" wrote:
>
>> Sounds like you already have a yes/no field in your table, for making a
>> record as chosen.
>>
>> Now you want to use the Filter of the form to mark all those as chosen.
>>
>> Assuming a table named Table1, and a yes/no field named IsPicked:
>>
>> Dim strSql as String
>> If Me.Dirty Then Me.Dirty = False 'Save first.
>> strSql = "UPDATE Table1 SET IsPicked = True WHERE " & Me.Filter & ";"
>> dbEngine(0)(0).Execute strSql, dbFailOnError
>> Me.Requery 'Show the changed state in the form.
>>
>> That should work, unless you are using FilterByForm with some lookup
>> combos
>> that have a zero width bound column. If so, you will need to craft the
>> UPDATE statement to that it includes the table that you need to filter
>> on,
>> or parse the Filter statement, and resolve it into the correct bound
>> field
>> instead of the lookup values.
>>
>> "Lana" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hi all,
>> >
>> > I have a continuous form with some buttons which allow to apply
>> > different
>> > filteres to this form.
>> >
>> > I have a check-box which allows me to select the records i like.
>> > I have a button which allows me to deselect all previously selected
>> > records.
>> >
>> > Now I would like to have a button which adds all filtered records to my
>> > selected list.
>> > Is that possible? (given that i use different filters all the time)
>> >
>> > in other words I want to be able to apply 1 filter and manually select
>> > 2
>> > or
>> > 3 records from the results, then apply another filter and select all
>> > the
>> > records from there by pressing some button, and then go to another
>> > filter...
>> >
>> > Please can somebody help me?
>> > Thank you.
>> > Lana



 
Reply With Quote
 
=?Utf-8?B?TGFuYQ==?=
Guest
Posts: n/a
 
      30th Jun 2005
I changed the name to "Selected", then to "2Select" - still doesn't work. Are
those wrong names too?
I still get the message "Too few parameters. Expected 1."

Lana


"Allen Browne" wrote:

> You have a field named "Select"?
>
> That's a reserved word in SQL, e.g.
> SELECT * FROM MyTable;
>
> Firstly, make sure that the Name AutoCorrect boxes are unchecked under:
> Tools | Options | General
> Then change the field name in your table.
> Then change the Name and ControlSource of the control on the form.
> Then change the query statement to use the new name.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lana" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > hi Allen!
> >
> > thank you for spending your time answering my question.
> >
> > may be i didnt understand correctly what you were suggesting, but when i
> > created a button with the following event procedure on click:
> >
> > Private Sub Command112_Click()
> > On Error GoTo Err_Command112_Click
> > Dim strSql As String
> > If Me.Dirty Then Me.Dirty = False 'Save first.
> > strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
> > DBEngine(0)(0).Execute strSql, dbFailOnError
> > Me.Requery 'Show the changed state in the form.
> > Exit_Command112_Click:
> > Exit Sub
> > Err_Command112_Click:
> > MsgBox Err.Description
> > Resume Exit_Command112_Click
> > End Sub
> >
> >
> > and tried to run it upon my filter I got the following message: "Syntax
> > error in UPDATE statement."
> >
> > then i changed the line
> > strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
> > to
> > strSql = "UPDATE [Codes] SET [Select] = True WHERE " & Me.Filter & ";"
> >
> > when tried to run it, got the message "Too few parameters. Expected 1."
> >
> > What I have done wrong?
> >
> > Lana
> >
> >
> > "Allen Browne" wrote:
> >
> >> Sounds like you already have a yes/no field in your table, for making a
> >> record as chosen.
> >>
> >> Now you want to use the Filter of the form to mark all those as chosen.
> >>
> >> Assuming a table named Table1, and a yes/no field named IsPicked:
> >>
> >> Dim strSql as String
> >> If Me.Dirty Then Me.Dirty = False 'Save first.
> >> strSql = "UPDATE Table1 SET IsPicked = True WHERE " & Me.Filter & ";"
> >> dbEngine(0)(0).Execute strSql, dbFailOnError
> >> Me.Requery 'Show the changed state in the form.
> >>
> >> That should work, unless you are using FilterByForm with some lookup
> >> combos
> >> that have a zero width bound column. If so, you will need to craft the
> >> UPDATE statement to that it includes the table that you need to filter
> >> on,
> >> or parse the Filter statement, and resolve it into the correct bound
> >> field
> >> instead of the lookup values.
> >>
> >> "Lana" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Hi all,
> >> >
> >> > I have a continuous form with some buttons which allow to apply
> >> > different
> >> > filteres to this form.
> >> >
> >> > I have a check-box which allows me to select the records i like.
> >> > I have a button which allows me to deselect all previously selected
> >> > records.
> >> >
> >> > Now I would like to have a button which adds all filtered records to my
> >> > selected list.
> >> > Is that possible? (given that i use different filters all the time)
> >> >
> >> > in other words I want to be able to apply 1 filter and manually select
> >> > 2
> >> > or
> >> > 3 records from the results, then apply another filter and select all
> >> > the
> >> > records from there by pressing some button, and then go to another
> >> > filter...
> >> >
> >> > Please can somebody help me?
> >> > Thank you.
> >> > Lana

>
>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      30th Jun 2005
Selected should be fine.
2Select starts with a number, so you would probably have to put the name in
square brackets.

The "parameters" message means one of the names in the query does not match
the name of a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lana" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I changed the name to "Selected", then to "2Select" - still doesn't work.
>Are
> those wrong names too?
> I still get the message "Too few parameters. Expected 1."
>
> Lana
>
>
> "Allen Browne" wrote:
>
>> You have a field named "Select"?
>>
>> That's a reserved word in SQL, e.g.
>> SELECT * FROM MyTable;
>>
>> Firstly, make sure that the Name AutoCorrect boxes are unchecked under:
>> Tools | Options | General
>> Then change the field name in your table.
>> Then change the Name and ControlSource of the control on the form.
>> Then change the query statement to use the new name.
>>
>>
>> "Lana" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > hi Allen!
>> >
>> > thank you for spending your time answering my question.
>> >
>> > may be i didnt understand correctly what you were suggesting, but when
>> > i
>> > created a button with the following event procedure on click:
>> >
>> > Private Sub Command112_Click()
>> > On Error GoTo Err_Command112_Click
>> > Dim strSql As String
>> > If Me.Dirty Then Me.Dirty = False 'Save first.
>> > strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
>> > DBEngine(0)(0).Execute strSql, dbFailOnError
>> > Me.Requery 'Show the changed state in the form.
>> > Exit_Command112_Click:
>> > Exit Sub
>> > Err_Command112_Click:
>> > MsgBox Err.Description
>> > Resume Exit_Command112_Click
>> > End Sub
>> >
>> >
>> > and tried to run it upon my filter I got the following message: "Syntax
>> > error in UPDATE statement."
>> >
>> > then i changed the line
>> > strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
>> > to
>> > strSql = "UPDATE [Codes] SET [Select] = True WHERE " & Me.Filter & ";"
>> >
>> > when tried to run it, got the message "Too few parameters. Expected 1."
>> >
>> > What I have done wrong?
>> >
>> > Lana
>> >
>> >
>> > "Allen Browne" wrote:
>> >
>> >> Sounds like you already have a yes/no field in your table, for making
>> >> a
>> >> record as chosen.
>> >>
>> >> Now you want to use the Filter of the form to mark all those as
>> >> chosen.
>> >>
>> >> Assuming a table named Table1, and a yes/no field named IsPicked:
>> >>
>> >> Dim strSql as String
>> >> If Me.Dirty Then Me.Dirty = False 'Save first.
>> >> strSql = "UPDATE Table1 SET IsPicked = True WHERE " & Me.Filter &
>> >> ";"
>> >> dbEngine(0)(0).Execute strSql, dbFailOnError
>> >> Me.Requery 'Show the changed state in the form.
>> >>
>> >> That should work, unless you are using FilterByForm with some lookup
>> >> combos
>> >> that have a zero width bound column. If so, you will need to craft the
>> >> UPDATE statement to that it includes the table that you need to filter
>> >> on,
>> >> or parse the Filter statement, and resolve it into the correct bound
>> >> field
>> >> instead of the lookup values.
>> >>
>> >> "Lana" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > Hi all,
>> >> >
>> >> > I have a continuous form with some buttons which allow to apply
>> >> > different
>> >> > filteres to this form.
>> >> >
>> >> > I have a check-box which allows me to select the records i like.
>> >> > I have a button which allows me to deselect all previously selected
>> >> > records.
>> >> >
>> >> > Now I would like to have a button which adds all filtered records to
>> >> > my
>> >> > selected list.
>> >> > Is that possible? (given that i use different filters all the time)
>> >> >
>> >> > in other words I want to be able to apply 1 filter and manually
>> >> > select
>> >> > 2
>> >> > or
>> >> > 3 records from the results, then apply another filter and select all
>> >> > the
>> >> > records from there by pressing some button, and then go to another
>> >> > filter...
>> >> >
>> >> > Please can somebody help me?
>> >> > Thank you.
>> >> > Lana

>>
>>
>>



 
Reply With Quote
 
=?Utf-8?B?TGFuYQ==?=
Guest
Posts: n/a
 
      30th Jun 2005
What else can be wrong here? why it doesn't work?
is there any other way to achieve the required results?
Lana


"Allen Browne" wrote:

> Selected should be fine.
> 2Select starts with a number, so you would probably have to put the name in
> square brackets.
>
> The "parameters" message means one of the names in the query does not match
> the name of a field.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lana" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I changed the name to "Selected", then to "2Select" - still doesn't work.
> >Are
> > those wrong names too?
> > I still get the message "Too few parameters. Expected 1."
> >
> > Lana
> >
> >
> > "Allen Browne" wrote:
> >
> >> You have a field named "Select"?
> >>
> >> That's a reserved word in SQL, e.g.
> >> SELECT * FROM MyTable;
> >>
> >> Firstly, make sure that the Name AutoCorrect boxes are unchecked under:
> >> Tools | Options | General
> >> Then change the field name in your table.
> >> Then change the Name and ControlSource of the control on the form.
> >> Then change the query statement to use the new name.
> >>
> >>
> >> "Lana" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > hi Allen!
> >> >
> >> > thank you for spending your time answering my question.
> >> >
> >> > may be i didnt understand correctly what you were suggesting, but when
> >> > i
> >> > created a button with the following event procedure on click:
> >> >
> >> > Private Sub Command112_Click()
> >> > On Error GoTo Err_Command112_Click
> >> > Dim strSql As String
> >> > If Me.Dirty Then Me.Dirty = False 'Save first.
> >> > strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
> >> > DBEngine(0)(0).Execute strSql, dbFailOnError
> >> > Me.Requery 'Show the changed state in the form.
> >> > Exit_Command112_Click:
> >> > Exit Sub
> >> > Err_Command112_Click:
> >> > MsgBox Err.Description
> >> > Resume Exit_Command112_Click
> >> > End Sub
> >> >
> >> >
> >> > and tried to run it upon my filter I got the following message: "Syntax
> >> > error in UPDATE statement."
> >> >
> >> > then i changed the line
> >> > strSql = "UPDATE Codes SET Select = True WHERE " & Me.Filter & ";"
> >> > to
> >> > strSql = "UPDATE [Codes] SET [Select] = True WHERE " & Me.Filter & ";"
> >> >
> >> > when tried to run it, got the message "Too few parameters. Expected 1."
> >> >
> >> > What I have done wrong?
> >> >
> >> > Lana
> >> >
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> Sounds like you already have a yes/no field in your table, for making
> >> >> a
> >> >> record as chosen.
> >> >>
> >> >> Now you want to use the Filter of the form to mark all those as
> >> >> chosen.
> >> >>
> >> >> Assuming a table named Table1, and a yes/no field named IsPicked:
> >> >>
> >> >> Dim strSql as String
> >> >> If Me.Dirty Then Me.Dirty = False 'Save first.
> >> >> strSql = "UPDATE Table1 SET IsPicked = True WHERE " & Me.Filter &
> >> >> ";"
> >> >> dbEngine(0)(0).Execute strSql, dbFailOnError
> >> >> Me.Requery 'Show the changed state in the form.
> >> >>
> >> >> That should work, unless you are using FilterByForm with some lookup
> >> >> combos
> >> >> that have a zero width bound column. If so, you will need to craft the
> >> >> UPDATE statement to that it includes the table that you need to filter
> >> >> on,
> >> >> or parse the Filter statement, and resolve it into the correct bound
> >> >> field
> >> >> instead of the lookup values.
> >> >>
> >> >> "Lana" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > Hi all,
> >> >> >
> >> >> > I have a continuous form with some buttons which allow to apply
> >> >> > different
> >> >> > filteres to this form.
> >> >> >
> >> >> > I have a check-box which allows me to select the records i like.
> >> >> > I have a button which allows me to deselect all previously selected
> >> >> > records.
> >> >> >
> >> >> > Now I would like to have a button which adds all filtered records to
> >> >> > my
> >> >> > selected list.
> >> >> > Is that possible? (given that i use different filters all the time)
> >> >> >
> >> >> > in other words I want to be able to apply 1 filter and manually
> >> >> > select
> >> >> > 2
> >> >> > or
> >> >> > 3 records from the results, then apply another filter and select all
> >> >> > the
> >> >> > records from there by pressing some button, and then go to another
> >> >> > filter...
> >> >> >
> >> >> > Please can somebody help me?
> >> >> > Thank you.
> >> >> > Lana
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      30th Jun 2005
"Lana" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> What else can be wrong here? why it doesn't work?
> is there any other way to achieve the required results?
> Lana


Yes, I'm sure you will be able to solve this, Lana.
I can't see it from here, so you will need to break the problem down.

Make a simple query that does work.
Build up from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select two records then select the next two records Ian Microsoft Access Queries 3 2nd Mar 2010 11:27 AM
Microsoft Speech Recognition repeating error "all all all all all all...." kenrosen@gmail.com Windows XP General 1 17th Dec 2005 03:22 AM
Microsoft Speech Recognition repeating error "all all all all all all...." kenrosen@gmail.com Windows XP Help 0 16th Dec 2005 07:39 PM
Nslookup query for NS records returns all of the NS records, but not all of the Host records Bob Microsoft Windows 2000 Networking 1 8th Nov 2004 08:03 PM
Nslookup query for NS records returns all of the NS records, but not all of the Host records Bob Microsoft Windows 2000 DNS 2 7th Nov 2004 05:42 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:48 AM.