Form shows incorrect results from Union Query

[bgreenspan]

Hi Everyone, I'm back for some more expert help. Here's what I am
doing and what I tried.

My database has entries with Contract Names and Expiry Dates, among
other fields. I have a form designed to show the expiring contracts.
To do this I use a straight forward query in my form's ON LOAD code

strwhere = "[Expiry_Date] BETWEEN #" & Now() & "# AND #" &
DateAdd("m", 6, Now()) & "#"
Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery", "SELECT
* FROM Matters " & (" WHERE " & strwhere & " ORDER BY
[Expiry_Date];"))

This looks 6 months into the future and puts the expiring contract
info into a query. I use this query as the record source for a for
with conditional formatting to color code by time interval.

When the form's Record Source is set to the query "qryMattersQuery" =,
everything works.

I then created another query to check and alter the expiry Date for
contract in the past (since the database was last accessed). I use
analogous coding as shown above and create a query called
"qryExpiringList". When I set that as the Record Source for my form,
it works fine.

What doesn't work right is:

I want to show all of the results at the same time on the form. So, I
created a Union query with the following line of code:

Set MyQueryDef = MyDatabase.CreateQueryDef("qryUnionQuery",
"SELECT * FROM qryExpiringList UNION SELECT * FROM qryMattersQuery
ORDER BY [Expiry_Date];")

The query results in tabular format are correct, but when I use the
query as the record source for my form, I don't always get the correct
results. I frequently see additional entries listed, seemingly
leftover from previous queries.

I have even added code to delete the previous queries before they are
created:

If ObjectExists("Queries", "qryUnionQuery") = True Then
MyDatabase.QueryDefs.Delete "qryUnionQuery"
MyDatabase.QueryDefs.Refresh
End If

but I still get the leftover record on the form, but NOT in the
tabular display of the UnionQuery results. The tabular display is
always what I expect to see. Where are the old results being stored?

I'm stumped. Thanks in advance for you help.

- Bernie

[bobh]

Hi,
I don't have the answer but here are two things you might want to try
or concider

- you know that there are two types of union queries, right? 'Union'
and 'Union All'

- maybe its not the querydef that needs refreshing, it might be that
the form needs either refreshing or requerying after you set the query
to the new querydef something like; Me.Form.ReQuery or Me.Form.Refresh

bobh.

On May 10, 3:09 pm, [e-mail] wrote:

Hi Everyone, I'm back for some more expert help. Here's what I am
doing and what I tried.

My database has entries with Contract Names and Expiry Dates, among
other fields. I have a form designed to show the expiring contracts.
To do this I use a straight forward query in my form's ON LOAD code

strwhere = "[Expiry_Date] BETWEEN #" & Now() & "# AND #" &
DateAdd("m", 6, Now()) & "#"
Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery", "SELECT
* FROM Matters " & (" WHERE " & strwhere & " ORDER BY
[Expiry_Date];"))

This looks 6 months into the future and puts the expiring contract
info into a query. I use this query as the record source for a for
with conditional formatting to color code by time interval.

When the form's Record Source is set to the query "qryMattersQuery" =,
everything works.

I then created another query to check and alter the expiry Date for
contract in the past (since the database was last accessed). I use
analogous coding as shown above and create a query called
"qryExpiringList". When I set that as the Record Source for my form,
it works fine.

What doesn't work right is:

I want to show all of the results at the same time on the form. So, I
created a Union query with the following line of code:

Set MyQueryDef = MyDatabase.CreateQueryDef("qryUnionQuery",
"SELECT * FROM qryExpiringList UNION SELECT * FROM qryMattersQuery
ORDER BY [Expiry_Date];")

The query results in tabular format are correct, but when I use the
query as the record source for my form, I don't always get the correct
results. I frequently see additional entries listed, seemingly
leftover from previous queries.

I have even added code to delete the previous queries before they are
created:

If ObjectExists("Queries", "qryUnionQuery") = True Then
MyDatabase.QueryDefs.Delete "qryUnionQuery"
MyDatabase.QueryDefs.Refresh
End If

but I still get the leftover record on the form, but NOT in the
tabular display of the UnionQuery results. The tabular display is
always what I expect to see. Where are the old results being stored?

I'm stumped. Thanks in advance for you help.

- Bernie