can union query results be edited?

Posted on 2000-02-29
Last Modified: 2007-10-18
I'm using a union query as a source for a form. "Allow Edits" on form property is "yes" and recordset type is "dynaset", but when I type something in one of the fields I get a message at the bottom saying "this recordset is not updatable". what's wrong?
Question by:SSF
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 2570640
Are there duplicate field names in the recordsets?

It seems plausible that a union query could be updated, after all, each field is unique.  But if there are an ambiguities, that would not be the case.

Are there any calculated fields in this query, (or any of the queries)?  If so, you won't be able to update it.

What is the SQL involved?


Author Comment

ID: 2570664
There are a couple of IIF functions in the first query. Also, doesn't union query require the same field names in both queries? Anyway, here's the sql:

SELECT DISTINCTROW tblFD.FD_Num AS FD_Num, tblFD.BrandName AS BrandName, tblFD.[X&W_Flag] AS [X&W_Flag], tblFD.MainIndiCode AS MainIndiCode, tblFD.Mfg AS Mfg, tblATCDesc.ATCd AS ATCd, tblATCDesc.EngDesc AS EngDesc, tblFD.DoseForm AS DoseForm, tblFD.ApplRoute AS ApplRoute, tblFD.NumOfUnits AS NumOfUnits, tblFD.DoseFormUnits AS DoseFormUnits, tblFD.DoseFormType AS DoseFormType, tblFD.NumOfUnitsPerDose AS NumOfUnitsPerDose, tblFD.DoseFm AS DoseFm, tblFD.PackSize AS PackSize, tblFD.PackType AS PackType, tblFD.Phase1 AS Phase1, tblFD.EasyGCNSkip AS EasyGCNSkip, [Tbl_IDDF-DE].IDC AS IDC, [Tbl_IDDF-DE].FD_Num AS [FD#], [Tbl_IDDF-DE].LabelName AS LabelName, [Tbl_IDDF-DE].BrandName AS Brand_Name, [Tbl_IDDF-DE].MfgIdentifier AS MfgIdentifier, [Tbl_IDDF-DE].GCNSeqno AS GCNSeqno, [Tbl_IDDF-DE].RecReady AS RecReady, [Tbl_IDDF-DE].Notes AS Notes, IIf(IsNull([Tbl_IDDF-DE]![Initial]),"SYS",[Tbl_IDDF-DE]![Initial]) AS Initial, IIf(IsNull([Tbl_IDDF-DE]![AddDate]),Date(),[Tbl_IDDF-DE]![AddDate]) AS AddDate, [Tbl_IDDF-DE].ModifyDate, [Tbl_IDDF-DE].CallMfg AS CallMfg, [Tbl_IDDF-DE].NewGCNIngre AS NewGCNIngre, [Tbl_IDDF-DE].NewGCNSalt AS NewGCNSalt, [Tbl_IDDF-DE].NewGCNStr AS NewGCNStr, [Tbl_IDDF-DE].NewGCN_DF AS NewGCN_DF, [Tbl_IDDF-DE].NewGCNRt AS NewGCNRt, [Tbl_IDDF-DE].QCDate AS QCDate, Null AS iobsdte, " " AS iobsdte1, [Tbl_IDDF-DE].QCInitial AS QCInitial
FROM ((tblFD LEFT JOIN tblATCDesc ON tblFD.ATC = tblATCDesc.ATCd) LEFT JOIN [Tbl_IDDF-DE] ON tblFD.FD_Num = [Tbl_IDDF-DE].FD_Num)

UNION SELECT DISTINCTROW [Tbl_IDDF-DE_Obsolete].FD_Num AS FD_Num, [Tbl_IDDF-DE_Obsolete].BrandName AS BrandName, [Tbl_IDDF-DE_Obsolete].[X&W_Flag], Tbl_FD.MainIndiCode AS MainIndiCode, [Tbl_HD/HI].AbbrName AS Mfg, tblATCDesc.ATCd AS ATCd, tblATCDesc.EngDesc AS EngDesc, Tbl_DoseFormDesc.DoseForm AS DoseForm, Tbl_DoseFormDesc.ApplRoute AS ApplRoute, Tbl_FD.NumOfUnits AS NumOfUnits, Tbl_FD.DoseFormUnits AS DoseFormUnits, Tbl_FD.DoseFormType AS DoseFormType, Tbl_FD.NumOfUnitsPerDose AS NumOfUnitsPerDose, Tbl_FD.DoseForm AS DoseFm, Tbl_FD.PackSize AS PackSize, Tbl_FD.PackType AS PackType, 0 AS Phase1, 0 AS EasyGCNSkip, [Tbl_IDDF-DE_Obsolete].IDC AS IDC, [Tbl_IDDF-DE_Obsolete].FD_Num AS [FD#], [Tbl_IDDF-DE_Obsolete].LabelName AS LabelName, [Tbl_IDDF-DE_Obsolete].BrandName AS Brand_Name, [Tbl_IDDF-DE_Obsolete].MfgIdentifier AS MfgIdentifier, [Tbl_IDDF-DE_Obsolete].GCNSeqno AS GCNSeqno, [Tbl_IDDF-DE_Obsolete].RecReady AS RecReady, [Tbl_IDDF-DE_Obsolete].Notes AS Notes, [Tbl_IDDF-DE_Obsolete].Initial AS Initial, [Tbl_IDDF-DE_Obsolete].AddDate AS AddDate, [Tbl_IDDF-DE_Obsolete].ModifyDate AS ModifyDate, [Tbl_IDDF-DE_Obsolete].CallMfg AS CallMfg, [Tbl_IDDF-DE_Obsolete].NewGCNIngre AS NewGCNIngre, [Tbl_IDDF-DE_Obsolete].NewGCNSalt AS NewGCNSalt, [Tbl_IDDF-DE_Obsolete].NewGCNStr AS NewGCNStr, [Tbl_IDDF-DE_Obsolete].NewGCN_DF AS NewGCN_DF, [Tbl_IDDF-DE_Obsolete].NewGCNRt AS NewGCNRt, [Tbl_IDDF-DE_Obsolete].QCDate AS QCDate,  [Tbl_IDDF-DE_Obsolete].iobsdte, [Tbl_IDDF-DE_Obsolete].iobsdte as iobsdte1, [Tbl_IDDF-DE_Obsolete].QCInitial AS QCInitial
FROM (Tbl_DoseFormDesc RIGHT JOIN ((tblATCDesc RIGHT JOIN Tbl_FD ON tblATCDesc.ATCd = Tbl_FD.ATC) LEFT JOIN [Tbl_HD/HI] ON Tbl_FD.FstMfgNum = [Tbl_HD/HI].Key) ON Tbl_DoseFormDesc.DoseFormCd = Tbl_FD.DoseFormCode) INNER JOIN [Tbl_IDDF-DE_Obsolete] ON Tbl_FD.Key = [Tbl_IDDF-DE_Obsolete].FD_Num;
LVL 58
ID: 2570668
UNION queries are not updateable.

LVL 10

Accepted Solution

paasky earned 50 total points
ID: 2570674
Hello SSF,

You can't update (edit) the records which source is union query because it's SQL specific Snapshot type Recordset.

It's not possible even there's a same table in all union select clauses or you've not used any grouping functions / calculations.


Author Comment

ID: 2570685
thanks all!

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question