Filter On Top Of Filter

[dan.cawthorne]

Where Is That When i Filter Results Via a Query, and I have My
Results,

Showing on my form,

When I Click on One Of My A-Z filter Buttons,

I Get The Parameter Dialog Popping Up To Getter The Postcode Like
Expression.

How Do I Get Around This

 

[Guest]

Hi Dan

Sounds like the query is referring to something that it can't find.

What do you mean by A-Z filter buttons?

Are these your own buttons? If so, what is the code behind them and what is
the SQL of the query?

Andy Hull

 

[dan.cawthorne]

Hi Dan

Sounds like the query is referring to something that it can't find.

What do you mean by A-Z filter buttons?

Are these your own buttons? If so, what is the code behind them and whatis
the SQL of the query?

Andy Hull

The A-Z Buttons Are Macro Based 27 It Total Included an All Button,
( Toggle Buttons) With a Group Box "CompanyNameFilters"

And On The

After Update

I Have A Macro, Called Alpha_Buttons

I Typical Line On In The macro is

Condition Action

[CompanyNameFilters]=1 [Company] Like "[AÀÁÂÃÄ]*"

And So On, and Then That Filters My form Via Alphabetical

Then Me SQL View Of My Query Is

SELECT Suppliers.CompanyID, Suppliers.Company, Suppliers.[Contact
Name], Suppliers.[Address 1], Suppliers.[Address 2], Suppliers.
[Address 3], Suppliers.Town, Suppliers.County, Suppliers.[Post Code],
Suppliers.Region, Suppliers.Tel, Suppliers.Fax, Suppliers.Mobile,
Suppliers.Email, Suppliers.Website, Suppliers.[Home Telephone],
Suppliers.[Home Fax], Suppliers.[Personal Mobile], Suppliers.[Personal
Email], Suppliers.[Account Number], Suppliers.Type, Suppliers.
[Supplier Notes], Suppliers.[Recieve by Letter], Suppliers.[Recieve by
CD-Rom], Suppliers.[Recieve by Email], Suppliers.[Recieve by Fax],
Suppliers.Rating, Suppliers.Preferred
FROM Suppliers
WHERE (((Suppliers.[Post Code]) Like "*" & [Forms]!
[Suppliers_PostCode_Dialog]![EnterPostCode] & "*"));

Which Allows Me TYPE WS as Post Code, If you Remember you Assisted
Recent Post

And It Filters All The Company's With a Postcode Beginning With WS

I Might Fliter about 100 Companys Back, So Then I Might Want To Just
Look at The Company's With a Name Beginning With D

Make Sense?

 

[Guest]

Hi again

The SQL looks fine. For it to work, the form [Suppliers_PostCode_Dialog]
must be open so the query can get the postcode entered else it will prompt
for it.

I don't understand the macro action - it isn't a valid action from the usual
drop down list.

Have you posted the full & exact literal entries?

Also, what is the full code for your After Update event?

Andy Hull

Hi Dan

Sounds like the query is referring to something that it can't find.

What do you mean by A-Z filter buttons?

Are these your own buttons? If so, what is the code behind them and what is
the SQL of the query?

Andy Hull

The A-Z Buttons Are Macro Based 27 It Total Included an All Button,
( Toggle Buttons) With a Group Box "CompanyNameFilters"

And On The

After Update

I Have A Macro, Called Alpha_Buttons

I Typical Line On In The macro is

Condition Action

[CompanyNameFilters]=1 [Company] Like "[A@ABCD]*"

And So On, and Then That Filters My form Via Alphabetical

Then Me SQL View Of My Query Is

SELECT Suppliers.CompanyID, Suppliers.Company, Suppliers.[Contact
Name], Suppliers.[Address 1], Suppliers.[Address 2], Suppliers.
[Address 3], Suppliers.Town, Suppliers.County, Suppliers.[Post Code],
Suppliers.Region, Suppliers.Tel, Suppliers.Fax, Suppliers.Mobile,
Suppliers.Email, Suppliers.Website, Suppliers.[Home Telephone],
Suppliers.[Home Fax], Suppliers.[Personal Mobile], Suppliers.[Personal
Email], Suppliers.[Account Number], Suppliers.Type, Suppliers.
[Supplier Notes], Suppliers.[Recieve by Letter], Suppliers.[Recieve by
CD-Rom], Suppliers.[Recieve by Email], Suppliers.[Recieve by Fax],
Suppliers.Rating, Suppliers.Preferred
FROM Suppliers
WHERE (((Suppliers.[Post Code]) Like "*" & [Forms]!
[Suppliers_PostCode_Dialog]![EnterPostCode] & "*"));

Which Allows Me TYPE WS as Post Code, If you Remember you Assisted
Recent Post

And It Filters All The Company's With a Postcode Beginning With WS

I Might Fliter about 100 Companys Back, So Then I Might Want To Just
Look at The Company's With a Name Beginning With D

Make Sense?

 

[dan.cawthorne]

Hi again

The SQL looks fine. For it to work, the form [Suppliers_PostCode_Dialog]
must be open so the query can get the postcode entered else it will prompt
for it.

I don't understand the macro action - it isn't a valid action from the usual
drop down list.

Have you posted the full & exact literal entries?

Also, what is the full code for your After Update event?

Andy Hull

The A-Z Buttons Are Macro Based 27 It Total Included an All Button,
( Toggle Buttons) With a Group Box "CompanyNameFilters"

And On The

After Update

I Have A Macro, Called Alpha_Buttons

I Typical Line On In The macro is

Condition Action

[CompanyNameFilters]=1 [Company] Like "[A@ABCD]*"

And So On, and Then That Filters My form Via Alphabetical

Then Me SQL View Of My Query Is

SELECT Suppliers.CompanyID, Suppliers.Company, Suppliers.[Contact
Name], Suppliers.[Address 1], Suppliers.[Address 2], Suppliers.
[Address 3], Suppliers.Town, Suppliers.County, Suppliers.[Post Code],
Suppliers.Region, Suppliers.Tel, Suppliers.Fax, Suppliers.Mobile,
Suppliers.Email, Suppliers.Website, Suppliers.[Home Telephone],
Suppliers.[Home Fax], Suppliers.[Personal Mobile], Suppliers.[Personal
Email], Suppliers.[Account Number], Suppliers.Type, Suppliers.
[Supplier Notes], Suppliers.[Recieve by Letter], Suppliers.[Recieve by
CD-Rom], Suppliers.[Recieve by Email], Suppliers.[Recieve by Fax],
Suppliers.Rating, Suppliers.Preferred
FROM Suppliers
WHERE (((Suppliers.[Post Code]) Like "*" & [Forms]!
[Suppliers_PostCode_Dialog]![EnterPostCode] & "*"));

Which Allows Me TYPE WS as Post Code, If you Remember you Assisted
Recent Post

And It Filters All The Company's With a Postcode Beginning With WS

I Might Fliter about 100 Companys Back, So Then I Might Want To Just
Look at The Company's With a Name Beginning With D

Make Sense?

Sorry To Confuse You,

The Actual Macro Code is

Condition Action
WHERE Condition

[CompanyNameFilters]=1 Apply Filter Company] Like
"[AÀÁÂÃÄ]*"

[CompanyNameFilters]=1 Apply Filter Company] Like
"*"

[CompanyNameFilters]=1 Apply Filter Company] Like
"[C]*"

And So On

I Did What Suggest, Insteated of Closing The Dialog form when the form
opens the results form, i just minimized it, and does the trick

Now The Reports Giving Me Hassel, But I think I Need Copy the Report
and And Then Base The Report Source of Same Form Results Query

as at moment on the button Click Event I Have the Following

Private Sub Command48_Click()

If Me.FilterOn = True Then

DoCmd.OpenReport "Rrpt_Suppliers", acViewPreview, ,
WhereCondition:=Me.Filter (Filters Company's Of a Selected
A-Z Macro Button)

Else

DoCmd.OpenReport "Rrpt_Suppliers", acViewPreview, , "[Post Code]" = "
& [Post Code] & " '" (Should Just Print The Returned Results Of
Post Code)

End If

End Sub

Dose That Sound about Right?

 

[Guest]

Hi Dan

Let me see if I have this right.

You have a postcode entry dialog first and then a list of suppliers is shown
filtered by the postcode.

At this point, the user can press a button (A-Z) to further filter the
suppliers by name if necessary.

So, I'm thinking you have a form that displays the suppliers and displays
the buttons A-Z.

Peronally, I wouldn't have the postcode entry separate. I would have a
single form that lists all suppliers when it is first opened. At the top of
this form I would have the postcode text box and a button labelled "Get
Suppliers" or "Refresh". So, the user would first see all suppliers but also
be able to enter a part (or full) postcode which your query would use exactly
as it does now.

The Refresh button would need to run the following command...

Me.Requery

This form could also have your A-Z buttons but I wouldn't use a macro to
filter the form. I would use the source query as we did for the postcode.
Actually, I would have another text box so the use can enter the first letter
of the supplier name or, in fact, as many letters as they wanted to narrow
down the search.

This way, all the filtering is done in one place - the query. Then, if you
base your report on this query it will automatically apply the same filter as
the form. If you have to use a different query then just make that query use
the postcode & supplier text box just like the form's query does.

At the moment, in the query design view you have the criteria for Post Code
equal to...

Like [Forms]![Suppliers_Postcode_Dialog]![EnterPostcode] & "*"

If you have a single form as described above (say MyForm) then this criteria
should be...

Like [Forms]![MyForm]![EnterPostcode] & "*"

And you will also enter a criteria for Company like...

Like [Forms]![MyForm]![EnterCompany] & "*"

After adding the [EnterCompany] text box of course.

If you really want to stick with the buttons then you still can - it just
makes the query criteria a bit more complicated. You just have to convert
the options 1-26 into A-Z using...

chr(OptionSelected + 64)

so your criteria for Company will be...

Like iif(OptionSelected=27, "*", chr(OptionSelected + 64) & "*")

Presuming the Any option is 27.

Hope this all makes sense. It does mean changing your design slightly but I
think it is easier to manage in the long run.

Regards

Andy Hull

Hi again

The SQL looks fine. For it to work, the form [Suppliers_PostCode_Dialog]
must be open so the query can get the postcode entered else it will prompt
for it.

I don't understand the macro action - it isn't a valid action from the usual
drop down list.

Have you posted the full & exact literal entries?

Also, what is the full code for your After Update event?

Andy Hull

On 24 Jul, 14:04, Andy Hull <[email protected]>
wrote:
Hi Dan

Sounds like the query is referring to something that it can't find.

What do you mean by A-Z filter buttons?

Are these your own buttons? If so, what is the code behind them and what is
the SQL of the query?

Andy Hull

:
Where Is That When i Filter Results Via a Query, and I have My
Results,

Showing on my form,

When I Click on One Of My A-Z filter Buttons,

I Get The Parameter Dialog Popping Up To Getter The Postcode Like
Expression.

How Do I Get Around This

The A-Z Buttons Are Macro Based 27 It Total Included an All Button,
( Toggle Buttons) With a Group Box "CompanyNameFilters"

And On The

After Update

I Have A Macro, Called Alpha_Buttons

I Typical Line On In The macro is

Condition Action

[CompanyNameFilters]=1 [Company] Like "[A@ABCD]*"

And So On, and Then That Filters My form Via Alphabetical

Then Me SQL View Of My Query Is

SELECT Suppliers.CompanyID, Suppliers.Company, Suppliers.[Contact
Name], Suppliers.[Address 1], Suppliers.[Address 2], Suppliers.
[Address 3], Suppliers.Town, Suppliers.County, Suppliers.[Post Code],
Suppliers.Region, Suppliers.Tel, Suppliers.Fax, Suppliers.Mobile,
Suppliers.Email, Suppliers.Website, Suppliers.[Home Telephone],
Suppliers.[Home Fax], Suppliers.[Personal Mobile], Suppliers.[Personal
Email], Suppliers.[Account Number], Suppliers.Type, Suppliers.
[Supplier Notes], Suppliers.[Recieve by Letter], Suppliers.[Recieve by
CD-Rom], Suppliers.[Recieve by Email], Suppliers.[Recieve by Fax],
Suppliers.Rating, Suppliers.Preferred
FROM Suppliers
WHERE (((Suppliers.[Post Code]) Like "*" & [Forms]!
[Suppliers_PostCode_Dialog]![EnterPostCode] & "*"));

Which Allows Me TYPE WS as Post Code, If you Remember you Assisted
Recent Post

And It Filters All The Company's With a Postcode Beginning With WS

I Might Fliter about 100 Companys Back, So Then I Might Want To Just
Look at The Company's With a Name Beginning With D

Make Sense?

Sorry To Confuse You,

The Actual Macro Code is

Condition Action
WHERE Condition

[CompanyNameFilters]=1 Apply Filter Company] Like
"[A@ABCD]*"

[CompanyNameFilters]=1 Apply Filter Company] Like
"*"

[CompanyNameFilters]=1 Apply Filter Company] Like
"[C]*"

And So On

I Did What Suggest, Insteated of Closing The Dialog form when the form
opens the results form, i just minimized it, and does the trick

Now The Reports Giving Me Hassel, But I think I Need Copy the Report
and And Then Base The Report Source of Same Form Results Query

as at moment on the button Click Event I Have the Following

Private Sub Command48_Click()

If Me.FilterOn = True Then

DoCmd.OpenReport "Rrpt_Suppliers", acViewPreview, ,
WhereCondition:=Me.Filter (Filters Company's Of a Selected
A-Z Macro Button)

Else

DoCmd.OpenReport "Rrpt_Suppliers", acViewPreview, , "[Post Code]" = "
& [Post Code] & " '" (Should Just Print The Returned Results Of
Post Code)

End If

End Sub

Dose That Sound about Right?