select all filtered records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.

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

Lana said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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.

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

Lana said:
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 said:
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.

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
Click to expand...
Click to expand...
Click to expand...
 
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.

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

Lana said:
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 said:
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 said:
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


:

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.

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
Click to expand...
Click to expand...
Click to expand...
 
What else can be wrong here? why it doesn't work?
is there any other way to achieve the required results?
Lana


Allen Browne said:
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.

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

Lana said:
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 said:
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.


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


:

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.

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
Click to expand...
Click to expand...
Click to expand...
 
Lana said:
What else can be wrong here? why it doesn't work?
is there any other way to achieve the required results?
Lana
Click to expand...

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.
 
Back
Top