Error that only appears in Access 2000 and not in 2002 or 2003

[Anders]

I developed a database in Access 2000 -- but ran into
problems when I put a filter on a control in a form. (The
form was used as a subform to another form.)

A friend helped me fix the problem, by changing the
syntax of the SQL query. However, he works in Access
2003 -- and the correction he put in place does not work
when I use it with Access 2000. I still get the same
error. (I have also tried the corrected database in
Access 2002 and it worked fine there.)

Any advice would be most welcome. The actual error is
described in detail below.

Thanks,
Anders

-----
More detail on the actual error:
-----

A form was put as a subform in another form. Among other
controls, the subform had two controls, named "Phase"
and "Activities." The Activities-control had a filter put
on it -- depending on which of four alternatives that was
chosen in the Phase-control, the Activities-control would
requery itself and fill with the Activities corresponding
to the chosen Phase.

The filter worked fine when the form was used by itself.
When put as a subform, though, it did not. The error
message that came up read: "You can't use the ApplyFilter
on this window."

A friend corrected the SQL syntax used for the filter. It
used to be :

SELECT [ERCActivity].[ActivityName], [ERCActivity].
[ERCPhasesID]
FROM ERCActivity
WHERE ((([ERCActivity].[ERCPhasesID])=[Forms]![ProjectLog
subform1]![Phase]))
ORDER BY [ERCActivity].[DisplayOrder];

but he changed the WHERE statement to

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]!
[ProjectLog subform1].[Form].[Controls]![Phase]))

The change fixed the problem -- on his computer using
Access 2003! It worked when I tried it using Access 2002.
However, in the Access 2000 environment where the
database is supposed to be used, it does not work. The
error persists. What to do?

 

[Arvin Meyer]

Try this:

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]![ProjectLog
subform1].[Form]![Phase]))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I developed a database in Access 2000 -- but ran into
problems when I put a filter on a control in a form. (The
form was used as a subform to another form.)

A friend helped me fix the problem, by changing the
syntax of the SQL query. However, he works in Access
2003 -- and the correction he put in place does not work
when I use it with Access 2000. I still get the same
error. (I have also tried the corrected database in
Access 2002 and it worked fine there.)

Any advice would be most welcome. The actual error is
described in detail below.

Thanks,
Anders

-----
More detail on the actual error:
-----

A form was put as a subform in another form. Among other
controls, the subform had two controls, named "Phase"
and "Activities." The Activities-control had a filter put
on it -- depending on which of four alternatives that was
chosen in the Phase-control, the Activities-control would
requery itself and fill with the Activities corresponding
to the chosen Phase.

The filter worked fine when the form was used by itself.
When put as a subform, though, it did not. The error
message that came up read: "You can't use the ApplyFilter
on this window."

A friend corrected the SQL syntax used for the filter. It
used to be :

SELECT [ERCActivity].[ActivityName], [ERCActivity].
[ERCPhasesID]
FROM ERCActivity
WHERE ((([ERCActivity].[ERCPhasesID])=[Forms]![ProjectLog
subform1]![Phase]))
ORDER BY [ERCActivity].[DisplayOrder];

but he changed the WHERE statement to

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]!
[ProjectLog subform1].[Form].[Controls]![Phase]))

The change fixed the problem -- on his computer using
Access 2003! It worked when I tried it using Access 2002.
However, in the Access 2000 environment where the
database is supposed to be used, it does not work. The
error persists. What to do?

 

[Anders]

Tried it. No change. Same error still appears.

Anders

-----Original Message-----
Try this:

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]! [ProjectLog
subform1].[Form]![Phase]))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I developed a database in Access 2000 -- but ran into
problems when I put a filter on a control in a form. (The
form was used as a subform to another form.)

A friend helped me fix the problem, by changing the
syntax of the SQL query. However, he works in Access
2003 -- and the correction he put in place does not work
when I use it with Access 2000. I still get the same
error. (I have also tried the corrected database in
Access 2002 and it worked fine there.)

Any advice would be most welcome. The actual error is
described in detail below.

Thanks,
Anders

-----
More detail on the actual error:
-----

A form was put as a subform in another form. Among other
controls, the subform had two controls, named "Phase"
and "Activities." The Activities-control had a filter put
on it -- depending on which of four alternatives that was
chosen in the Phase-control, the Activities-control would
requery itself and fill with the Activities corresponding
to the chosen Phase.

The filter worked fine when the form was used by itself.
When put as a subform, though, it did not. The error
message that came up read: "You can't use the ApplyFilter
on this window."

A friend corrected the SQL syntax used for the filter. It
used to be :

SELECT [ERCActivity].[ActivityName], [ERCActivity].
[ERCPhasesID]
FROM ERCActivity
WHERE ((([ERCActivity].[ERCPhasesID])=[Forms]! [ProjectLog
subform1]![Phase]))
ORDER BY [ERCActivity].[DisplayOrder];

but he changed the WHERE statement to

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]!
[ProjectLog subform1].[Form].[Controls]![Phase]))

The change fixed the problem -- on his computer using
Access 2003! It worked when I tried it using Access 2002.
However, in the Access 2000 environment where the
database is supposed to be used, it does not work. The
error persists. What to do?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.591 / Virus Database: 374 - Release Date: 2/17/2004


.

 

[Arvin Meyer]

The correct syntax for referring to a control on a subform is:

Forms!
FormName!
SubformControlName.
Form!
ControlName

space are enclosed in square brackets. Use the bang (!) for the collection
items and the dot(.) for properties. Make sure that the subform's CONTROL
NAME, not the subform name is used.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Tried it. No change. Same error still appears.

Anders

-----Original Message-----
Try this:

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]! [ProjectLog
subform1].[Form]![Phase]))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I developed a database in Access 2000 -- but ran into
problems when I put a filter on a control in a form. (The
form was used as a subform to another form.)

A friend helped me fix the problem, by changing the
syntax of the SQL query. However, he works in Access
2003 -- and the correction he put in place does not work
when I use it with Access 2000. I still get the same
error. (I have also tried the corrected database in
Access 2002 and it worked fine there.)

Any advice would be most welcome. The actual error is
described in detail below.

Thanks,
Anders

-----
More detail on the actual error:
-----

A form was put as a subform in another form. Among other
controls, the subform had two controls, named "Phase"
and "Activities." The Activities-control had a filter put
on it -- depending on which of four alternatives that was
chosen in the Phase-control, the Activities-control would
requery itself and fill with the Activities corresponding
to the chosen Phase.

The filter worked fine when the form was used by itself.
When put as a subform, though, it did not. The error
message that came up read: "You can't use the ApplyFilter
on this window."

A friend corrected the SQL syntax used for the filter. It
used to be :

SELECT [ERCActivity].[ActivityName], [ERCActivity].
[ERCPhasesID]
FROM ERCActivity
WHERE ((([ERCActivity].[ERCPhasesID])=[Forms]! [ProjectLog
subform1]![Phase]))
ORDER BY [ERCActivity].[DisplayOrder];

but he changed the WHERE statement to

WHERE (((ERCActivity.ERCPhasesID)=[Forms]![Project]!
[ProjectLog subform1].[Form].[Controls]![Phase]))

The change fixed the problem -- on his computer using
Access 2003! It worked when I tried it using Access 2002.
However, in the Access 2000 environment where the
database is supposed to be used, it does not work. The
error persists. What to do?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.591 / Virus Database: 374 - Release Date: 2/17/2004


.