Union query as recordsource for subform

[anorakgirl]

Hi,

I have written a union query, which I am able to use as the record source for a form.

However if I make this form into a subform on another form, even without linking child/master fields, I get the error:

you can't use a pass-through query or a non fixed column query as a record source for a subform or report

Before you bind the subform or subreport to a crosstab query, set the query's Column Heading property

However, this query is definitely not a pass-through query, and the columns are fixed, and the query does not have a column headings property. Is is possible to do this - all I want to do is display the union query results within another form!

Thanks,
Tamsin



~

http://www.anorakgirl.co.uk

~

 

[HandsOnAccess]

I created a union query and tested it as a single form record source as well as for subform record source, It did not show any error message.

In your case there could be some complication in Union Query.

Alternatively you can create another query as follows

SELECT * INTO Temp FROM <<union query>>

and make Temp table as Record Source of the Sub Form

Note You need to run this query each time you open the form, so that Temp table gets overwritten and the subform reflects live data.

 

[TheAceMan1]

how are ya anorakgirl . . .

Curious this . . . post the SQL of the query! . . .

See Ya! . . . . . .

 

[anorakgirl]

well, the union itself is quite simple, but the queries it selects from are more complicated; for example:

SELECT DISTINCTROW work.workid, "" & [Forms]![frmWork]![startdate] AS datestart, 
work.dateto AS dateend, DateDiff("d",[Forms]![frmWork]![startdate],[dateto])+1 AS countdays
FROM work
WHERE (((work.dateto) Between [Forms]![frmWork]![startdate] And [Forms]![frmWork]![enddate]]) 
AND ((work.datefrom)<[Forms]![frmWork]![startdate]));

I have got round this by using a list box to show the query results rather than a subform, and I think that'll do. Thanks for the suggestions!



~

http://www.anorakgirl.co.uk

~

 

[ZmrAbdulla]

This is not a Union query..
Where is the "UNION" key word here?

SELECT DISTINCTROW work.workid,
	"" & [Forms]![frmWork]![startdate] AS datestart,
	
work.dateto AS dateend,
	DateDiff("d",
	[Forms]![frmWork]![startdate],
	[dateto])+1 AS countdays
FROM work
WHERE (((work.dateto) Between [Forms]![frmWork]![startdate]
	and [Forms]![frmWork]![enddate]]) 
AND ((work.datefrom)<[Forms]![frmWork]![startdate]));

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[anorakgirl]

Hi,
Yes - as I said at the beginning of my last post, the union query is quite simple, but the queries it selects from are not.

So, for completeness, the union query is like this:

select workid,datestart,dateend,countdays
from workquery1
UNION
select workid,datestart,dateend,countdays
from workquery2
UNION
select workid,datestart,dateend,countdays
from workquery3

where workquery1,workquery2, and workquery3 are variations on the query I posted in my last message.

~

http://www.anorakgirl.co.uk

~

 

[ZmrAbdulla]

1) To run the UNION query you need to keep the form ("frmWork") open.

2) Try changing the SQL by creating Alias

workid AS WKID,datestart AS STDATE ,.....
from workquery1
UNION
workid AS WKID,datestart AS STDATE ,.....
from workquery2
UNION
workid AS WKID,datestart AS STDATE ,.....
from workquery3

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.