Removing Filter

[Charles D Clayton Jr]

A2K

I have a main form that uses a tab sheet to change the subforms. The
last subform is based on a crosstab query. I put some filter buttons
on the form (A-Z, Z-A, By Selection, Exclude Selection, Remove
Filter). Everything works fine until I remove filter. When I do
that, it then will not allow me to Filter by Selection of Exclude
Selection. It says "Cannot use the crosstab of a non- fixed column as
a sub-query"

I am not sure what the best why to "Remove filter" is. What I do is
this (code snipet):

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL
Forms!MainForm_Spools![TabsubfrmSpools].Requery

I delete a query and then recreate it for this form. My strSQL is
exactly the SQL for my subform. I just took it and pasted into the
code. But for some reason it is not working for this subform.

If I go to another sheet on the main form and then back to this
subform, the Filter by selection and exclude selection will work. But
whenever I press "remove filter" then it does not work and gives me
that error. So my problem is in the code for remove filter. I know
that my 'strSQL' string is correct because I take that straight from
the subform's recordsource.

Does anybody have any suggestions on what my problem is? Or a better
way to remove filters?

Thanks,

Charles D Clayton Jr

 

[Art]

Hi Charles,
The crosstab message means that you have to go
to query properties..column headings and hardcode the
column headings that you want. Try that and see if things
work better.

The Easy Day

-----Original Message-----
A2K

I have a main form that uses a tab sheet to change the subforms. The
last subform is based on a crosstab query. I put some filter buttons
on the form (A-Z, Z-A, By Selection, Exclude Selection, Remove
Filter). Everything works fine until I remove filter. When I do
that, it then will not allow me to Filter by Selection of Exclude
Selection. It says "Cannot use the crosstab of a non- fixed column as
a sub-query"

I am not sure what the best why to "Remove filter" is. What I do is
this (code snipet):

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Forms!MainForm_Spools!

[TabsubfrmSpools].Form.RecordSource = strSQL

Forms!MainForm_Spools![TabsubfrmSpools].Requery

I delete a query and then recreate it for this form. My strSQL is
exactly the SQL for my subform. I just took it and pasted into the
code. But for some reason it is not working for this subform.

If I go to another sheet on the main form and then back to this
subform, the Filter by selection and exclude selection will work. But
whenever I press "remove filter" then it does not work and gives me
that error. So my problem is in the code for remove filter. I know
that my 'strSQL' string is correct because I take that straight from
the subform's recordsource.

Does anybody have any suggestions on what my problem is? Or a better
way to remove filters?

Thanks,

Charles D Clayton Jr
.

 

[Art]

I forgot to say that the column headings should be
enclosed in quotes and separated by commas. Sorry 'bout
that:
"Call Status","ECR - Customer ID/Setup", etc...

The Easy Day

-----Original Message-----
Hi Charles,
The crosstab message means that you have to go
to query properties..column headings and hardcode the
column headings that you want. Try that and see if things
work better.

The Easy Day

-----Original Message-----
A2K

I have a main form that uses a tab sheet to change the subforms. The
last subform is based on a crosstab query. I put some filter buttons
on the form (A-Z, Z-A, By Selection, Exclude Selection, Remove
Filter). Everything works fine until I remove filter. When I do
that, it then will not allow me to Filter by Selection

of

Exclude
Selection. It says "Cannot use the crosstab of a non- fixed column as
a sub-query"

I am not sure what the best why to "Remove filter" is. What I do is
this (code snipet):

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Forms!MainForm_Spools!

[TabsubfrmSpools].Form.RecordSource = strSQL

Forms!MainForm_Spools![TabsubfrmSpools].Requery

I delete a query and then recreate it for this form. My strSQL is
exactly the SQL for my subform. I just took it and pasted into the
code. But for some reason it is not working for this subform.

If I go to another sheet on the main form and then back to this
subform, the Filter by selection and exclude selection will work. But
whenever I press "remove filter" then it does not work and gives me
that error. So my problem is in the code for remove filter. I know
that my 'strSQL' string is correct because I take that straight from
the subform's recordsource.

Does anybody have any suggestions on what my problem

is?
Or a better

way to remove filters?

Thanks,

Charles D Clayton Jr
.

.

 

[MGFoster]

A2K

I have a main form that uses a tab sheet to change the subforms. The
last subform is based on a crosstab query. I put some filter buttons
on the form (A-Z, Z-A, By Selection, Exclude Selection, Remove
Filter). Everything works fine until I remove filter. When I do
that, it then will not allow me to Filter by Selection of Exclude
Selection. It says "Cannot use the crosstab of a non- fixed column as
a sub-query"

I am not sure what the best why to "Remove filter" is. What I do is
this (code snipet):

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL
Forms!MainForm_Spools![TabsubfrmSpools].Requery

I delete a query and then recreate it for this form. My strSQL is
exactly the SQL for my subform. I just took it and pasted into the
code. But for some reason it is not working for this subform.

If I go to another sheet on the main form and then back to this
subform, the Filter by selection and exclude selection will work. But
whenever I press "remove filter" then it does not work and gives me
that error. So my problem is in the code for remove filter. I know
that my 'strSQL' string is correct because I take that straight from
the subform's recordsource.

Does anybody have any suggestions on what my problem is? Or a better
way to remove filters?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

After creating the query def you need to close it:

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Q.Close ' <<< ----- you need this.
Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL

You don't need to requery the subform 'cuz when a form's RecordSource is
changed the form automatically requeries.

An easier solution is not to delete the QueryDef, but to change it's SQL
property. E.g.:

1 set Q = db.QueryDefs("qFindSpools")
2 Q.SQL = strSQL
3 Q.Close
4 Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL

If the subform's RecordSource was the query "qFindSpools" then instead
of setting it's RecordSource to the strSQL value (line 4) you'd just
have to Requery the subform so it will use the new SQL stored in the
QueryDef. E.g.:

4 Forms!MainForm_Spools![TabsubfrmSpools].Form.Requery

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIWn9YechKqOuFEgEQLODACeJWzEjPMVbYq8L+SwVnu8/abRt7cAnA/b
TFNtWqsd2jKC61KHrTO43LUF
=kfWF
-----END PGP SIGNATURE-----

 

[Charles D Clayton Jr]

Thanks, that worked great. I will remember about closing out the
query def when I am finished.

Hope you have a good day,

Charles D Clayton Jr

A2K

I have a main form that uses a tab sheet to change the subforms. The
last subform is based on a crosstab query. I put some filter buttons
on the form (A-Z, Z-A, By Selection, Exclude Selection, Remove
Filter). Everything works fine until I remove filter. When I do
that, it then will not allow me to Filter by Selection of Exclude
Selection. It says "Cannot use the crosstab of a non- fixed column as
a sub-query"

I am not sure what the best why to "Remove filter" is. What I do is
this (code snipet):

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL
Forms!MainForm_Spools![TabsubfrmSpools].Requery

I delete a query and then recreate it for this form. My strSQL is
exactly the SQL for my subform. I just took it and pasted into the
code. But for some reason it is not working for this subform.

If I go to another sheet on the main form and then back to this
subform, the Filter by selection and exclude selection will work. But
whenever I press "remove filter" then it does not work and gives me
that error. So my problem is in the code for remove filter. I know
that my 'strSQL' string is correct because I take that straight from
the subform's recordsource.

Does anybody have any suggestions on what my problem is? Or a better
way to remove filters?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

After creating the query def you need to close it:

db.QueryDefs.Delete "qFindSpools"
Set Q = db.CreateQueryDef("qFindSpools", strSQL)
Q.Close ' <<< ----- you need this.
Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL

You don't need to requery the subform 'cuz when a form's RecordSource is
changed the form automatically requeries.

An easier solution is not to delete the QueryDef, but to change it's SQL
property. E.g.:

1 set Q = db.QueryDefs("qFindSpools")
2 Q.SQL = strSQL
3 Q.Close
4 Forms!MainForm_Spools![TabsubfrmSpools].Form.RecordSource = strSQL

If the subform's RecordSource was the query "qFindSpools" then instead
of setting it's RecordSource to the strSQL value (line 4) you'd just
have to Requery the subform so it will use the new SQL stored in the
QueryDef. E.g.:

4 Forms!MainForm_Spools![TabsubfrmSpools].Form.Requery

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIWn9YechKqOuFEgEQLODACeJWzEjPMVbYq8L+SwVnu8/abRt7cAnA/b
TFNtWqsd2jKC61KHrTO43LUF
=kfWF
-----END PGP SIGNATURE-----