Filter Button

[Eddie Mason]

I have placed a filter button on a form as a filter and written the following on-click event procedure:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort

Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True

When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.

I have checked this procedure in other forms and it works without showing the blank message box.

The only difference with this form is that its control source is a union query.

Can anyone suggest a way of losing the message box?

Kind regards,

Eddie

 

[Cronk]

As a start, I'd remove the first two lines.

 

[Eddie Mason]

Hi Cronk,

I've done what you suggested but without any success same blank message box still appears.

Kind regards,

Eddie

 

[Cronk]

So you are saying you have a form with a union query as the data source for that form. When you open the form, it displays data and when you click your filter button, you get the message box.

Something you could try, is to save the union query (if not already done so) and create a new query based on the saved union query and then put "HS" as criteria in the ACCOUNT_DO_NOT_EMAIL field.

Otherwise, post the SQL for the query.

BTW, ACCOUNT_DO_NOT_EMAIL is not a Yes/No field is it?

 

[Eddie Mason]

Hi Cronk,

Thanks for your help on this it's most frustrating.

Firstly no it is not a Yes/No field, I have tried putting 'HS' into the query's criteria and when I opened the form it worked perfectly; flushed with this success I decided to create an unbound text box to hold the data that I wanted to filter by. I then put this field into the queries criteria and low and behold when I run the filter button I got the same empty message box.

Just to let you know that this is working from a query based on the union query.

 

[Cronk]

The message box might be prompting for a parameter that is irrelevant.

Post the SQL for the union query.

 

[Eddie Mason]

Hi Cronk,

Thanks for your help on this, the Union query is:

SELECT dbDBC_ACCOUNT.*
FROM dbDBC_ACCOUNT;
union all
SELECT tblHSCust.*
FROM tblHSCust;
UNION ALL SELECT tblNHCust.*
FROM tblNHCust;

Kind regards,

Eddie Mason

 

[Cronk]

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order but they do not have to be the same length.

Are you certain the structures of the 3 tables are exactly the same?

 

[Eddie Mason]

Hi Kronk,

I really do appreciate the time that you've spent on this it must be as frustrating for you as it is for me.

What I've done is to cut and paste the dbDBC_ACCOUNT twice as a local table and then appended the data into them to ensure all three tables are identical. After completing this process I got exactly the same result a blank message box.

Kind regards,

Eddie

 

[Cronk]

Try this, as I have done.

Create a table, call it Table1 with an autonumber field (key), a text field, and a date field. Add one record. Create a query
SELECT a.* from table1 as a; union all select b.* from table1 as b; union all SELECT c.* from table1 as c;

I get a result with the record showing 3 times.

I can't help you further other than suggest you incrementally build your query with a union of the same table on itself, union of two different tables and then a union of all three tables.

 

[Eddie Mason]

Hi Kronk,

Many thanks for your help, it is still not quite there but hopefully, when I come back refreshed from my two weeks holiday in Italy, it will be.

Kind regards,

Eddie