printing recordes viewed on screen

[Guest]

Hi,

I have a continuous form for which i can apply dirrerent filters. I want to
be able to print a report based on what i see on screen (on my form) after
applying a filter.

How can I get only filtered records?

If there is no simple solution, I want at least to be able to somehow assign
the vaue "true" to my [Selected] check-box to all the records which i view
filtered on my form automatically rather than doing it manually. (so that
later i can print a report based on the query "[Selected]=True")

Can anybody help me please?

Thank you.
Lana

 

[Allen Browne]

You can use the Filter of your form in the WhereCondition of your OpenReport
action.

Assuming you have placed a command button on your form for "Print as
filtered", its Event Procedure would be something like this:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Note that in Access 2002 or 2003, it is possible for a form's filter to
include lookup values from combos/list boxes. If this is the case, you will
need to ensure that the lookup tables are in the report's RecordSource
query, using the same names, so that the filter string resolves correctly.

 

[Guest]

Thank you Allen so much!!! You always do miracles!

I have based my report on the main table and put the button on my form with
your code which opens the report all right. (I use Access 2002)

My filters are Macros activated by different buttons on my form.
Your code works fine for those Macros which have pre-defined conditions
(like "=true"). It even works fine for the conditions entered using a
combo-box on my form.
But I have 2 Macros "Find by name" & "Find by number" which give you a
pop-up dialogue box and you enter the value in there.

For those 2 Macros - when I press the Report button - it still requires to
re-enter the value rather than giving me the records which i already see on
the screen.

I guess I wouldnt print those kind of results much - anyway I would have to
look them through and check if those are what i need - while doing so I can
select those which i need manually using the check-box and then print
selected.

But I got 1 more question to ask you: can i use some similar code as you've
given me to select/de-select all the records which i view filtered?

I have only a button for deselecting everything selected (using the Update
Query which sets value to "false" for all records in the main table)

Can you help me to create a button which would set the value to "true" for
only the filtered records. - I also need that for my another project.

Anyway, thank you again for your help.
You always give simple and working solutions!

Lana

You can use the Filter of your form in the WhereCondition of your OpenReport
action.

Assuming you have placed a command button on your form for "Print as
filtered", its Event Procedure would be something like this:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Note that in Access 2002 or 2003, it is possible for a form's filter to
include lookup values from combos/list boxes. If this is the case, you will
need to ensure that the lookup tables are in the report's RecordSource
query, using the same names, so that the filter string resolves correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I have a continuous form for which i can apply dirrerent filters. I want
to
be able to print a report based on what i see on screen (on my form) after
applying a filter.

How can I get only filtered records?

If there is no simple solution, I want at least to be able to somehow
assign
the vaue "true" to my [Selected] check-box to all the records which i view
filtered on my form automatically rather than doing it manually. (so that
later i can print a report based on the query "[Selected]=True")

Can anybody help me please?

Thank you.
Lana

 

[Allen Browne]

If you are using an Update query to clear the selection, I presume you have
a Yes/No field to set. Assuming this field is called IsPicked, you could
pick up the Filter property from your form, and use it in the WHERE clause
of your update query.

The Click event procedure for this button would be something like this:
Private Sub cmdSelect_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET IsPicked = True WHERE (IsPicked =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Thank you Allen so much!!! You always do miracles!

I have based my report on the main table and put the button on my form
with
your code which opens the report all right. (I use Access 2002)

My filters are Macros activated by different buttons on my form.
Your code works fine for those Macros which have pre-defined conditions
(like "=true"). It even works fine for the conditions entered using a
combo-box on my form.
But I have 2 Macros "Find by name" & "Find by number" which give you a
pop-up dialogue box and you enter the value in there.

For those 2 Macros - when I press the Report button - it still requires to
re-enter the value rather than giving me the records which i already see
on
the screen.

I guess I wouldnt print those kind of results much - anyway I would have
to
look them through and check if those are what i need - while doing so I
can
select those which i need manually using the check-box and then print
selected.

But I got 1 more question to ask you: can i use some similar code as
you've
given me to select/de-select all the records which i view filtered?

I have only a button for deselecting everything selected (using the Update
Query which sets value to "false" for all records in the main table)

Can you help me to create a button which would set the value to "true" for
only the filtered records. - I also need that for my another project.

Anyway, thank you again for your help.
You always give simple and working solutions!

Lana

You can use the Filter of your form in the WhereCondition of your
OpenReport
action.

Assuming you have placed a command button on your form for "Print as
filtered", its Event Procedure would be something like this:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Note that in Access 2002 or 2003, it is possible for a form's filter to
include lookup values from combos/list boxes. If this is the case, you
will
need to ensure that the lookup tables are in the report's RecordSource
query, using the same names, so that the filter string resolves
correctly.

I have a continuous form for which i can apply dirrerent filters. I
want
to
be able to print a report based on what i see on screen (on my form)
after
applying a filter.

How can I get only filtered records?

If there is no simple solution, I want at least to be able to somehow
assign
the vaue "true" to my [Selected] check-box to all the records which i
view
filtered on my form automatically rather than doing it manually. (so
that
later i can print a report based on the query "[Selected]=True")

 

[Guest]

Super! Works perfectly!

But still doesnt work for filters which use the condition: Like ("*"+[Enter
the document number]+"*")

(
is there any way to make it work?

Lana

 

[Allen Browne]

Not sure what you mean by "doesn't work".

Would you consider placing an unbound text box on your form, and using its
name as the parameter. For example, if the form is called Form1, and the
text box is named Text1, you would use the condition:
Like "*" & [Forms].[Form1].[Text1] & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Super! Works perfectly!

But still doesnt work for filters which use the condition: Like
("*"+[Enter
the document number]+"*")

(
is there any way to make it work?

Lana

If you are using an Update query to clear the selection, I presume you
have
a Yes/No field to set. Assuming this field is called IsPicked, you could
pick up the Filter property from your form, and use it in the WHERE
clause
of your update query.

The Click event procedure for this button would be something like this:
Private Sub cmdSelect_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET IsPicked = True WHERE (IsPicked =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

 

[Guest]

Hi Allen,

I have placed a textbox on my form and modified my macro as you suggested.
The print-preview button opens the report all right.

But the select-all button (the one setting value to "true") doesnt work,
sais: "Run-time error 3061" and "Too few parameters. Expected 1", then
suggests to debug this line:

DBEngine(0)(0).Execute strSql & ";", dbFailOnError

What am i doing wrong here?

Lana

Not sure what you mean by "doesn't work".

Would you consider placing an unbound text box on your form, and using its
name as the parameter. For example, if the form is called Form1, and the
text box is named Text1, you would use the condition:
Like "*" & [Forms].[Form1].[Text1] & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Super! Works perfectly!

But still doesnt work for filters which use the condition: Like
("*"+[Enter
the document number]+"*")

(
is there any way to make it work?

Lana

If you are using an Update query to clear the selection, I presume you
have
a Yes/No field to set. Assuming this field is called IsPicked, you could
pick up the Filter property from your form, and use it in the WHERE
clause
of your update query.

The Click event procedure for this button would be something like this:
Private Sub cmdSelect_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET IsPicked = True WHERE (IsPicked =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

Thank you Allen so much!!! You always do miracles!

I have based my report on the main table and put the button on my form
with
your code which opens the report all right. (I use Access 2002)

My filters are Macros activated by different buttons on my form.
Your code works fine for those Macros which have pre-defined conditions
(like "=true"). It even works fine for the conditions entered using a
combo-box on my form.
But I have 2 Macros "Find by name" & "Find by number" which give you a
pop-up dialogue box and you enter the value in there.

For those 2 Macros - when I press the Report button - it still requires
to
re-enter the value rather than giving me the records which i already
see
on
the screen.

I guess I wouldnt print those kind of results much - anyway I would
have
to
look them through and check if those are what i need - while doing so I
can
select those which i need manually using the check-box and then print
selected.

But I got 1 more question to ask you: can i use some similar code as
you've
given me to select/de-select all the records which i view filtered?

I have only a button for deselecting everything selected (using the
Update
Query which sets value to "false" for all records in the main table)

Can you help me to create a button which would set the value to "true"
for
only the filtered records. - I also need that for my another project.

Anyway, thank you again for your help.
You always give simple and working solutions!

Lana


:

You can use the Filter of your form in the WhereCondition of your
OpenReport
action.

Assuming you have placed a command button on your form for "Print as
filtered", its Event Procedure would be something like this:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Note that in Access 2002 or 2003, it is possible for a form's filter
to
include lookup values from combos/list boxes. If this is the case, you
will
need to ensure that the lookup tables are in the report's RecordSource
query, using the same names, so that the filter string resolves
correctly.

 

[Allen Browne]

The Expression Service will not be able to resolve a reference such as
[Forms].[Form1].[Text1], so Access treats it as a parameter.

You will need to replace that with the actual value from the text box before
the Execute line, e.g.:
strSql = Replace(strSql, "[Forms].[Form1].[Text1]",
[Forms].[Form1].[Text1])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I have placed a textbox on my form and modified my macro as you suggested.
The print-preview button opens the report all right.

But the select-all button (the one setting value to "true") doesnt work,
sais: "Run-time error 3061" and "Too few parameters. Expected 1", then
suggests to debug this line:

DBEngine(0)(0).Execute strSql & ";", dbFailOnError

What am i doing wrong here?

Lana

Not sure what you mean by "doesn't work".

Would you consider placing an unbound text box on your form, and using
its
name as the parameter. For example, if the form is called Form1, and the
text box is named Text1, you would use the condition:
Like "*" & [Forms].[Form1].[Text1] & "*"

Super! Works perfectly!

But still doesnt work for filters which use the condition: Like
("*"+[Enter
the document number]+"*")

(
is there any way to make it work?

Lana


:

If you are using an Update query to clear the selection, I presume you
have
a Yes/No field to set. Assuming this field is called IsPicked, you
could
pick up the Filter property from your form, and use it in the WHERE
clause
of your update query.

The Click event procedure for this button would be something like
this:
Private Sub cmdSelect_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET IsPicked = True WHERE (IsPicked =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

Thank you Allen so much!!! You always do miracles!

I have based my report on the main table and put the button on my
form
with
your code which opens the report all right. (I use Access 2002)

My filters are Macros activated by different buttons on my form.
Your code works fine for those Macros which have pre-defined
conditions
(like "=true"). It even works fine for the conditions entered using
a
combo-box on my form.
But I have 2 Macros "Find by name" & "Find by number" which give you
a
pop-up dialogue box and you enter the value in there.

For those 2 Macros - when I press the Report button - it still
requires
to
re-enter the value rather than giving me the records which i already
see
on
the screen.

I guess I wouldnt print those kind of results much - anyway I would
have
to
look them through and check if those are what i need - while doing
so I
can
select those which i need manually using the check-box and then
print
selected.

But I got 1 more question to ask you: can i use some similar code as
you've
given me to select/de-select all the records which i view filtered?

I have only a button for deselecting everything selected (using the
Update
Query which sets value to "false" for all records in the main table)

Can you help me to create a button which would set the value to
"true"
for
only the filtered records. - I also need that for my another
project.

Anyway, thank you again for your help.
You always give simple and working solutions!

Lana


:

You can use the Filter of your form in the WhereCondition of your
OpenReport
action.

Assuming you have placed a command button on your form for "Print
as
filtered", its Event Procedure would be something like this:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Note that in Access 2002 or 2003, it is possible for a form's
filter
to
include lookup values from combos/list boxes. If this is the case,
you
will
need to ensure that the lookup tables are in the report's
RecordSource
query, using the same names, so that the filter string resolves
correctly.

 

[Allen Browne]

The Expression Service will not be able to resolve a reference such as
[Forms].[Form1].[Text1], so Access treats it as a parameter.

You will need to replace that with the actual value from the text box before
the Execute line, e.g.:
strSql = Replace(strSql, "[Forms].[Form1].[Text1]",
[Forms].[Form1].[Text1])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I have placed a textbox on my form and modified my macro as you suggested.
The print-preview button opens the report all right.

But the select-all button (the one setting value to "true") doesnt work,
sais: "Run-time error 3061" and "Too few parameters. Expected 1", then
suggests to debug this line:

DBEngine(0)(0).Execute strSql & ";", dbFailOnError

What am i doing wrong here?

Lana

Not sure what you mean by "doesn't work".

Would you consider placing an unbound text box on your form, and using
its
name as the parameter. For example, if the form is called Form1, and the
text box is named Text1, you would use the condition:
Like "*" & [Forms].[Form1].[Text1] & "*"

Super! Works perfectly!

But still doesnt work for filters which use the condition: Like
("*"+[Enter
the document number]+"*")

(
is there any way to make it work?

Lana


:

If you are using an Update query to clear the selection, I presume you
have
a Yes/No field to set. Assuming this field is called IsPicked, you
could
pick up the Filter property from your form, and use it in the WHERE
clause
of your update query.

The Click event procedure for this button would be something like
this:
Private Sub cmdSelect_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Table1 SET IsPicked = True WHERE (IsPicked =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

Thank you Allen so much!!! You always do miracles!

I have based my report on the main table and put the button on my
form
with
your code which opens the report all right. (I use Access 2002)

My filters are Macros activated by different buttons on my form.
Your code works fine for those Macros which have pre-defined
conditions
(like "=true"). It even works fine for the conditions entered using
a
combo-box on my form.
But I have 2 Macros "Find by name" & "Find by number" which give you
a
pop-up dialogue box and you enter the value in there.

For those 2 Macros - when I press the Report button - it still
requires
to
re-enter the value rather than giving me the records which i already
see
on
the screen.

I guess I wouldnt print those kind of results much - anyway I would
have
to
look them through and check if those are what i need - while doing
so I
can
select those which i need manually using the check-box and then
print
selected.

But I got 1 more question to ask you: can i use some similar code as
you've
given me to select/de-select all the records which i view filtered?

I have only a button for deselecting everything selected (using the
Update
Query which sets value to "false" for all records in the main table)

Can you help me to create a button which would set the value to
"true"
for
only the filtered records. - I also need that for my another
project.

Anyway, thank you again for your help.
You always give simple and working solutions!

Lana


:

You can use the Filter of your form in the WhereCondition of your
OpenReport
action.

Assuming you have placed a command button on your form for "Print
as
filtered", its Event Procedure would be something like this:
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Note that in Access 2002 or 2003, it is possible for a form's
filter
to
include lookup values from combos/list boxes. If this is the case,
you
will
need to ensure that the lookup tables are in the report's
RecordSource
query, using the same names, so that the filter string resolves
correctly.

 

[Guest]

Sorry Allan,
It was stupid of me not to explain properly what the problem is.

Below is the code you've given me for selecting records which I see on my
continuous form after filtering them:

Private Sub Command124_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Selected = True WHERE (Selected = False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
DBEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

It works fine for filters with with the condition like:
[Codes]![PaperCopy]=True

But it doesnt work for the filters with the condition like:
[Codes]![DocNo] Like ("*" & [Forms].[Main].[Text125] & "*")

It gives the "Run-time error 3061" and "Too few parameters. Expected 1", and
suggests to debug this line:
DBEngine(0)(0).Execute strSql & ";", dbFailOnError

Please can you show me the way how to modify the above code so that it could
work for both kinds of filters?

PS: which command can be used to immediately show the changes on screen
after applying the above code? "Refresh" didnt work... And "re-query" - is it
applicable to filters? Where in the code shall I put it?

Thank you again,
Lana

 

[Allen Browne]

It could be an issue with delimiters. If DocNo is a Text type field, (not a
Number type field), you need extra quotes:
" ... Codes.DocNo Like (""*" & [Forms].[Main].[Text125] & "*"")"

Use a Requery to show the results after the change:
Me.Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Sorry Allan,
It was stupid of me not to explain properly what the problem is.

Below is the code you've given me for selecting records which I see on my
continuous form after filtering them:

Private Sub Command124_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Selected = True WHERE (Selected =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
DBEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

It works fine for filters with with the condition like:
[Codes]![PaperCopy]=True

But it doesnt work for the filters with the condition like:
[Codes]![DocNo] Like ("*" & [Forms].[Main].[Text125] & "*")

It gives the "Run-time error 3061" and "Too few parameters. Expected 1",
and
suggests to debug this line:
DBEngine(0)(0).Execute strSql & ";", dbFailOnError

Please can you show me the way how to modify the above code so that it
could
work for both kinds of filters?

PS: which command can be used to immediately show the changes on screen
after applying the above code? "Refresh" didnt work... And "re-query" - is
it
applicable to filters? Where in the code shall I put it?

Thank you again,
Lana

The Expression Service will not be able to resolve a reference such as
[Forms].[Form1].[Text1], so Access treats it as a parameter.

You will need to replace that with the actual value from the text box
before
the Execute line, e.g.:
strSql = Replace(strSql, "[Forms].[Form1].[Text1]",
[Forms].[Form1].[Text1])

 

[Guest]

No, Allan, the filter itself works alright.
It's the button for selecting filtered records which doesn't work.
The one with the code as follows:
Private Sub Command124_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Selected = True WHERE (Selected = False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
DBEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

Lana

It could be an issue with delimiters. If DocNo is a Text type field, (not a
Number type field), you need extra quotes:
" ... Codes.DocNo Like (""*" & [Forms].[Main].[Text125] & "*"")"

Use a Requery to show the results after the change:
Me.Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Sorry Allan,
It was stupid of me not to explain properly what the problem is.

Below is the code you've given me for selecting records which I see on my
continuous form after filtering them:

Private Sub Command124_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Selected = True WHERE (Selected =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
DBEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

It works fine for filters with with the condition like:
[Codes]![PaperCopy]=True

But it doesnt work for the filters with the condition like:
[Codes]![DocNo] Like ("*" & [Forms].[Main].[Text125] & "*")

It gives the "Run-time error 3061" and "Too few parameters. Expected 1",
and
suggests to debug this line:
DBEngine(0)(0).Execute strSql & ";", dbFailOnError

Please can you show me the way how to modify the above code so that it
could
work for both kinds of filters?

PS: which command can be used to immediately show the changes on screen
after applying the above code? "Refresh" didnt work... And "re-query" - is
it
applicable to filters? Where in the code shall I put it?

Thank you again,
Lana

The Expression Service will not be able to resolve a reference such as
[Forms].[Form1].[Text1], so Access treats it as a parameter.

You will need to replace that with the actual value from the text box
before
the Execute line, e.g.:
strSql = Replace(strSql, "[Forms].[Form1].[Text1]",
[Forms].[Form1].[Text1])

 

[Allen Browne]

Well, the code will need some reworking to use the parameter like that.

Perhaps you would prefer to work around the problem by using RunSQL instead
of Execute, i.e.:
RunSQL strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

No, Allan, the filter itself works alright.
It's the button for selecting filtered records which doesn't work.
The one with the code as follows:
Private Sub Command124_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Selected = True WHERE (Selected =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
DBEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

Lana

It could be an issue with delimiters. If DocNo is a Text type field, (not
a
Number type field), you need extra quotes:
" ... Codes.DocNo Like (""*" & [Forms].[Main].[Text125] & "*"")"

Use a Requery to show the results after the change:
Me.Requery

Sorry Allan,
It was stupid of me not to explain properly what the problem is.

Below is the code you've given me for selecting records which I see on
my
continuous form after filtering them:

Private Sub Command124_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strSql = "UPDATE Codes SET Selected = True WHERE (Selected =
False)"
If Me.FilterOn Then
strSql = strSql & " AND (" & Me.Filter & ")"
End If
DBEngine(0)(0).Execute strSql & ";", dbFailOnError
End Sub

It works fine for filters with with the condition like:
[Codes]![PaperCopy]=True

But it doesnt work for the filters with the condition like:
[Codes]![DocNo] Like ("*" & [Forms].[Main].[Text125] & "*")

It gives the "Run-time error 3061" and "Too few parameters. Expected
1",
and
suggests to debug this line:
DBEngine(0)(0).Execute strSql & ";", dbFailOnError

Please can you show me the way how to modify the above code so that it
could
work for both kinds of filters?

PS: which command can be used to immediately show the changes on screen
after applying the above code? "Refresh" didnt work... And "re-query" -
is
it
applicable to filters? Where in the code shall I put it?

Thank you again,
Lana



:

The Expression Service will not be able to resolve a reference such as
[Forms].[Form1].[Text1], so Access treats it as a parameter.

You will need to replace that with the actual value from the text box
before
the Execute line, e.g.:
strSql = Replace(strSql, "[Forms].[Form1].[Text1]",
[Forms].[Form1].[Text1])

 

[Marshall Barton]

It could be an issue with delimiters. If DocNo is a Text type field, (not a
Number type field), you need extra quotes:
" ... Codes.DocNo Like (""*" & [Forms].[Main].[Text125] & "*"")"

Use a Requery to show the results after the change:
Me.Requery

Allen, I can't figure out what the issues here are so I
don't want to get into this thread. However, I do think
there is a typo in the above where the first dot should be a
bang:

. . . & [Forms]![Main].[Text125] & . . .