Full Version: Union query doesn't work when embedded as sub form
UtterAccess Forums > Microsoft® Access > Access Forms
kforward
I created a union query (and subsequently a form based on the union query) that work fine when run on its own. However, when the form is pulled into another form as a subform, and master / child fields are linked correctly - it doesn't work. Has anyone had success using a union query in a subform, and if so, how do you work around the above?
Thanks!
vtd
Sorry but "doesn't work" gives no clues for potential respondents to analyze the problem ... You need to post the details of your Table(s), the SQL String of the Union Query, the LinkMasterFields / LinkChildFields and describe what happens when you try to open the Form/Subform combination in Form View.
nion Queries work fine as RecordSource for Subforms ...
datAdrenaline
>> Has anyone had success using a union query in a subform <<
Yes .... I just created a sample to try and duplicate your scenario and I had no problems ... Do all field data types remain the same as the First table listed? ... in otherwords, where the first table has a Long datatype, does the second table also have a Long datatype.
vtd
Sorry, Brent ... but the data types don't have to match either. The only requirement of the Union is that each Select component has the same number of Fields in the selection list.
datAdrenaline
Hey Van --
realize that the datatypes don't need to match in the UNION query, but I was wondering if the datatypes got coerced in the UNION due to a mismatch and thus are preventing the LinkMaster and LinkChild fields from working properly ... like if the ID is text in one table is Text and a Number/Long in an other, then I think the field will be coerced to TEXT by the UNION, then if the table bound in the Main has an ID of Number/Long, and it is used in the Link properties in conjunction with the coerced ID field from the Union, then ... maybe that is causing the problem.... ??? ...
... I have not tested this theory, so I may be way off.. but I thought it was an avenue to persue.
kforward
Thanks for your help guys - here are more details as requested:
The error I get is "ODBC Failed", but that only pops up after the query runs for about 5 minutes w/o returning any results. It seems to time-out.
- When I pull it into my main form as a subform, I link the LinkMaster to ATTEMPT_OID (which is a field in the mainform's query) and LinkChild to ATTEMPT_OID on the subform.
- Here are the select portions of the union query (as mentioned previously - this union query when run as a stand-alone query - runs and displays the correct result in less than a second - so I don't think the Union Query itself is the problem):
SELECT
TBL1.ATTEMPT_OID,
TBL2.DESCRIPTION,
TBL3.ACTUAL_DATE,
" " AS PLAN_DATE,
TBL5.FILENAME
UNION
SELECT
TBL6.ATTEMPT_OID,
TBL6.NAME,
TBL6.ACTUAL_DATE,
TBL6.PLAN_DATE,
" " AS FILENAME
Note: to test I created the subform using only one of the SELECT queries (so it wouldn't be a union query) - linked the Master and Child as noted above - and it runs and pulls the correct result in less than a second. Any suggestions are much appreciated.
Thanks!
vtd
Excellent thinking, 99.
However, this means that only the linking Field(s) need to conform and not all Fields. As you wrote "Do all field data types remain the same ... ?", I misunderstood your intention (sorry).
Cheers
vtd
I am not sure but the SQL looks strange in that it doesn't have the FROM clause for each of the SELECT components ... of the UNION SQL?
Since there are no FROM clause, I doubt the database engine understand what the qualifiers TBL1, TBL2, ... mean ...
kforward
Hi Van,
Here is the whole UNION query below, in my original post - for readability - I shortened the table names and only included the SELECT portion of the UNION query since it is actually working on its own and doesn't need de-bugging. But here is the whole query in all its glory, it works fine. Just not sure why it runs endlessly when inserted into a form as subform and joined to main form via linkMaster ATTEMPT_OID and linkChild ATTEMPT_OID:
SELECT FTC_V_INVOICE_DETAIL_ALL.ATTEMPT_OID,
FTC_INVOICE_STATE_DESC.DESCRIPTION,
FTC_INVOICE_STATE.ACTUAL_DATE,
" " AS PLAN_DATE,
FTC_INVOICE.FILENAME
FROM ((FTC_INVOICE INNER JOIN FTC_INVOICE_STATE ON FTC_INVOICE.INVOICE_OID = FTC_INVOICE_STATE.INVOICE_OID) INNER JOIN FTC_INVOICE_STATE_DESC ON FTC_INVOICE_STATE.NAME = FTC_INVOICE_STATE_DESC.NAME) INNER JOIN (FTC_V_INVOICE_DETAIL_ALL INNER JOIN FTC_INVOICE_LINE_ITEM ON FTC_V_INVOICE_DETAIL_ALL.INVOICE_LINE_ITEM_OID = FTC_INVOICE_LINE_ITEM.INVOICE_LINE_ITEM_OID) ON FTC_INVOICE.INVOICE_OID = FTC_INVOICE_LINE_ITEM.INVOICE_OID
UNION SELECT FTC_V_ATTEMPT_STATE_ALL.ATTEMPT_OID,
FTC_V_ATTEMPT_STATE_ALL.NAME,
FTC_V_ATTEMPT_STATE_ALL.ACTUAL_DATE,
FTC_V_ATTEMPT_STATE_ALL.PLAN_DATE,
" " AS FILENAME
FROM FTC_V_ATTEMPT_STATE_ALL;
vtd
1. Try leaving the LinkMasterFields/LinkChildFields Property of the SubformControl blank and see if the Subform works (without filtering by the link Fields).
. If point 1 works, leave the LinkMasterFields/LinkChildFiels empty but test the Union SQL (as RecordSource of the Subform) with the WHERE clause in each SELECT component specifying a particular/valid value for [ATTEMPT_OID].
3. If point 2 works, make sure that the Fields [ATTEMPT_OID] in Tables [FTC_V_INVOICE_DETAIL_ALL] and [FTC_V_ATTEMPT_STATE_ALL] are of the same data-type (which is the same data-type as the LinkMasterFields Field) as mentioned by Brent.
4. If point 3 is OK and you get the error again when the LinkMasterFields/LinkChildFiels are specified properly:
(It sounds like your back-end is not a JET database and you use ODBC-linked Tables as data sources since you got ODBC errors mentioned in one of your posts.)
In some circunstances, the ODBC driver does weird things with the SQL String, especially in this case, Access injects the selection criteria required by the LinkMasterFields / LinkChildFields.
In this case, you will have to find a work-around so that the combination of Access-Jet-ODBC driver "translates" your SQL correctly as you want. In one case, I had a similar problem and the SQL String I used worked with JET back-end and also in the SQL Server (tested independently from Access/JET). As soon as I used the SQL on the ODBC-linked Table in Access front-end, the SQL String errored out. In my case, somehow, I decided to add a Field (which I didn't need) in the SELECT clause and the whole SQL worked fine with ODBC-linked Tables in the Access front-end!
5. One possible work-around if you get to this is to create use the UNION SQL as a SubQuery/SubSQL deployed as the data source for a "wrapper" main Query / SQL. The SQL should ve something like:
CODE
SELECT SQ.*
FROM
(
  SELECT FTC_V_INVOICE_DETAIL_ALL.ATTEMPT_OID,
  FTC_INVOICE_STATE_DESC.DESCRIPTION,
  FTC_INVOICE_STATE.ACTUAL_DATE,
  " " AS PLAN_DATE,
  FTC_INVOICE.FILENAME
  FROM ((FTC_INVOICE INNER JOIN FTC_INVOICE_STATE
    ON FTC_INVOICE.INVOICE_OID =   FTC_INVOICE_STATE.INVOICE_OID) INNER JOIN
    FTC_INVOICE_STATE_DESC
    ON FTC_INVOICE_STATE.NAME = FTC_INVOICE_STATE_DESC.NAME) INNER JOIN
    (FTC_V_INVOICE_DETAIL_ALL INNER JOIN FTC_INVOICE_LINE_ITEM
    ON FTC_V_INVOICE_DETAIL_ALL.INVOICE_LINE_ITEM_OID =
    FTC_INVOICE_LINE_ITEM.INVOICE_LINE_ITEM_OID)
    ON FTC_INVOICE.INVOICE_OID = FTC_INVOICE_LINE_ITEM.INVOICE_OID
  UNION
  SELECT FTC_V_ATTEMPT_STATE_ALL.ATTEMPT_OID,
    FTC_V_ATTEMPT_STATE_ALL.NAME,
    FTC_V_ATTEMPT_STATE_ALL.ACTUAL_DATE,
    FTC_V_ATTEMPT_STATE_ALL.PLAN_DATE,
    " " AS FILENAME
  FROM FTC_V_ATTEMPT_STATE_ALL
) AS SQ

6. Another work-around if point 1 works is to use VBA code to do the filtering rather than relying on the LinkMasterFields/LinkChildFields.
kforward
1 - 3 checks out and works, then as you mentioned, when re-linking the master/child properly it returns to the ODBC call failed error. I think you're right in that things are getting muddled between the Access JET front end and the Oracle back end that I have my tables linked to. I'll try the work arounds suggested and truly appreciate your help on this conundrum!
Thanks,
Kent
vtd
You're welcome ... Glad we could assist ...
The work-around in point 6 should work, in general. I am not too sure about point 5. The rest are just trial and error like my adding a Field in the selection list.
I am not familiar with Oracle but it should have something similar to the SQL Server's "SQL Profiler". You can use this facility to see what the SQL String is being sent to the Oracle database engine for processing and this may help you to work out a work-around.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.