John W. Colby
jcolby at colbyconsulting.com
Wed Feb 26 12:50:00 CST 2003
I "solved" the problem using nested queries: UPDATE tblClaim SET tblClaim.CL_DOD = #1/1/2000# WHERE (((tblClaim.CL_ID) In (SELECT tblClaimEvents.CLEV_IDCL FROM tblClaimEvents WHERE (((tblClaimEvents.CLEV_IDEVTY)=45));))); Notice the select statement inside the IN(). The inner SQL statement runs, pulls a set of IDs which it hands back to the IN() clause. Using this method allows me to avoid the join, which solves the problem. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of John Frederick Sent: Wednesday, February 26, 2003 1:02 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Operation must use an updateable query You can't update the one side of a query with a one to many join. If there is a rule for determining that the claim record needs to be updated, I suggest code to do DoCmd.OpenQuery using a query that references the event record on the form for the ClaimID or DoCmd.RunSQL a constructed sql statement to update the claim record and then requery the form. -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of John W. Colby Sent: Wednesday, February 26, 2003 10:20 AM To: AccessD Subject: [AccessD] Operation must use an updateable query I have a database where a claim table has an event child table. The event table may have an event such as Open for a set of claims. In order to discover which claims have an open event I have a query that pulls out that event and has the claim id exposed. now I want to update a field in every claim that has an open event (as an example). Trying to do so generates the infamous "Operation must use an updateable query". this is one I have banged into before and never really understood why I get it, not how to reliably not get it. Update a field in the parent table, if it has a related record in the child table. It sounds so simple. How is it done? John W. Colby Colby Consulting www.ColbyConsulting.com ---------------------------------------------------- Is email taking over your day? Manage your time with eMailBoss. Try it free! http://www.eMailBoss.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com ---------------------------------------------------- Is email taking over your day? Manage your time with eMailBoss. Try it free! http://www.eMailBoss.com -------------- next part -------------- A non-text attachment was scrubbed... Name: winmail.dat Type: application/ms-tnef Size: 2840 bytes Desc: not available URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030226/657635e3/attachment-0001.bin>