Update Query Problem
in [Help]
|
Prev: Form OnClose vs. subform BeforeUpdate validation: the showdown
Next: Way pdf file not will stay permanent open
From: Steve_s on 19 Jul 2010 08:22 On 18 July, 16:22, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote: > Steve_s <stev...(a)hotmail.co.uk> wrote innews:9a2196e2-1381-45b3-a156-e7906a6f7d44(a)q12g2000yqj.googlegroups.co > m: > > > > > > > On 17 July, 17:39, Salad <sa...(a)oilandvinegar.com> wrote: > >> Steve_s wrote: > >> > I have an update query which updates fields in two tables. The > >> > query is fired from an input form but when the command button > >> > is activated the query only changes the fields in one of the > >> > tables but fails to change a yes/no field in the second table. > >> > If I run the query again the yes/no field in the second table > >> > changes as anticipated. > > >> > I would appreciate some help with this as it has me really > >> > confused! > > >> > Steve > > >> What version? What is the SQL statement? > > >> It worked on a statement I ran that looked like this > >> UPDATE CU INNER JOIN CH ON CU.UnitID = CH.UnitID > >> SET CU.UnitNum = 40, CH.YN = False > >> WHERE CU.PartsID=41; > > > Access 2007 > > > The SQL is: > > UPDATE qryMoveMachineSelect SET qryMoveMachineSelect.[AVAILABLE?] > > No, qryMoveMachineSelect.ASSET_NUMBER = > > [Forms]![frmInstallations]! [Text43], > > qryMoveMachineSelect.OPENING_METER_1 = [Forms]! > > [frmInstallations]![txtMeter1], > > qryMoveMachineSelect.OPENING_METER_2 > > [Forms]![frmInstallations]![txtmeter2], > > qryMoveMachineSelect.OPENING_METER_3 = [Forms]![frmInstallations]! > > [txtxMeter3], qryMoveMachineSelect.DATE_INSTALLED = [Forms]! > > [frmInstallations]![txtDate]; > > > Originally I was trying to update directly to the tables but > > thought that may be the problem so I creaed a select query to get > > the data but the result was exactly the same > > Either you miscopied the SQL from your query or you have some messed > up statements. > > When I break down the query into individual statements I see missing > equals (=) signs in 2 places) and missing quotes around a value. > > UPDATE qryMoveMachineSelect > SET > qryMoveMachineSelect.[AVAILABLE?] No, should be = 'no' or = false > > qryMoveMachineSelect.ASSET_NUMBER = [Forms]![frmInstallations]! > [Text43], > > qryMoveMachineSelect.OPENING_METER_1 = [Forms]![frmInstallations]! > [txtMeter1], > > qryMoveMachineSelect.OPENING_METER_2 [Forms]![frmInstallations]! > [txtmeter2], missing equals sign > > qryMoveMachineSelect.OPENING_METER_3 = [Forms]![frmInstallations]! > [txtxMeter3], > > qryMoveMachineSelect.DATE_INSTALLED = [Forms]![frmInstallations]! > [txtDate];- Hide quoted text - > > - Show quoted text - Hi The equals signs are in the query. I built the query using the standard QBE screen. Also I am led to understand that updating two tables is feasible and am trying to understand why this will not work rather than creat two separate queries.. Steve
From: Bob Quintal on 19 Jul 2010 18:17 Steve_s <stevo.s(a)hotmail.co.uk> wrote in news:aca27a60-602c-4955-9c0d-a87af37750d2(a)d8g2000yqf.googlegroups.com : > On 18 July, 16:22, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote: >> Steve_s <stev...(a)hotmail.co.uk> wrote >> innews:9a2196e2-1381-45b3-a156-e790 > 6a6f7d44(a)q12g2000yqj.googlegroups.co >> m: >> >> >> >> >> >> > On 17 July, 17:39, Salad <sa...(a)oilandvinegar.com> wrote: >> >> Steve_s wrote: >> >> > I have an update query which updates fields in two tables. >> >> > The query is fired from an input form but when the command >> >> > button is activated the query only changes the fields in one >> >> > of the tables but fails to change a yes/no field in the >> >> > second table. If I run the query again the yes/no field in >> >> > the second table changes as anticipated. >> >> >> > I would appreciate some help with this as it has me really >> >> > confused! >> >> >> > Steve >> >> >> What version? �What is the SQL statement? >> >> >> It worked on a statement I ran that looked like this >> >> � � � � UPDATE CU INNER JOIN CH ON CU.UnitID = CH.UnitID >> >> � � � � SET CU.UnitNum = 40, CH.YN = False >> >> � � � � WHERE CU.PartsID=41; >> >> > Access 2007 >> >> > The SQL is: >> > UPDATE qryMoveMachineSelect SET >> > qryMoveMachineSelect.[AVAILABLE?] No, >> > qryMoveMachineSelect.ASSET_NUMBER [Forms]![frmInstallations]! >> > [Text43], qryMoveMachineSelect.OPENING_METER_1 = [Forms]! >> > [frmInstallations]![txtMeter1], >> > qryMoveMachineSelect.OPENING_METER_2 >> > [Forms]![frmInstallations]![txtmeter2], >> > qryMoveMachineSelect.OPENING_METER_3 = >> > [Forms]![frmInstallations]! [txtxMeter3], >> > qryMoveMachineSelect.DATE_INSTALLED = [Forms]! >> > [frmInstallations]![txtDate]; >> >> > Originally I was trying to update directly to the tables but >> > thought that may be the problem so I creaed a select query to >> > get the data but the result was exactly the same >> >> Either you miscopied the SQL from your query or you have some >> messed up statements. >> >> When I break down the query into individual statements I see >> missing equals (=) �signs in 2 places) and missing quotes around >> a value. >> >> UPDATE qryMoveMachineSelect >> SET >> qryMoveMachineSelect.[AVAILABLE?] No, should be = 'no' or = false >> >> qryMoveMachineSelect.ASSET_NUMBER = [Forms]![frmInstallations]! >> [Text43], >> >> qryMoveMachineSelect.OPENING_METER_1 = >> [Forms]![frmInstallations]! [txtMeter1], >> >> qryMoveMachineSelect.OPENING_METER_2 [Forms]![frmInstallations]! >> [txtmeter2], missing equals sign >> >> qryMoveMachineSelect.OPENING_METER_3 = >> [Forms]![frmInstallations]! [txtxMeter3], >> >> qryMoveMachineSelect.DATE_INSTALLED = [Forms]![frmInstallations]! >> [txtDate];- Hide quoted text - >> >> - Show quoted text - > > Hi > > The equals signs are in the query. I built the query using the > standard QBE screen. Also I am led to understand that updating two > tables is feasible and am trying to understand why this will not > work rather than creat two separate queries.. > > > Steve > There are several causes where Access, and SQL in general, can create non-updateable queries. Some reasons: three tables in the query, left or right join in the query, no primary key on one or more tables.(Sometimes adding the keys to the queries helps that)
From: Steve_s on 20 Jul 2010 13:19
On 19 July, 23:17, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote: > Steve_s <stev...(a)hotmail.co.uk> wrote innews:aca27a60-602c-4955-9c0d-a87af37750d2(a)d8g2000yqf.googlegroups.com > : > > > > > > > On 18 July, 16:22, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote: > >> Steve_s <stev...(a)hotmail.co.uk> wrote > >> innews:9a2196e2-1381-45b3-a156-e790 > > 6a6f7...(a)q12g2000yqj.googlegroups.co > >> m: > > >> > On 17 July, 17:39, Salad <sa...(a)oilandvinegar.com> wrote: > >> >> Steve_s wrote: > >> >> > I have an update query which updates fields in two tables. > >> >> > The query is fired from an input form but when the command > >> >> > button is activated the query only changes the fields in one > >> >> > of the tables but fails to change a yes/no field in the > >> >> > second table. If I run the query again the yes/no field in > >> >> > the second table changes as anticipated. > > >> >> > I would appreciate some help with this as it has me really > >> >> > confused! > > >> >> > Steve > > >> >> What version? What is the SQL statement? > > >> >> It worked on a statement I ran that looked like this > >> >> UPDATE CU INNER JOIN CH ON CU.UnitID = CH.UnitID > >> >> SET CU.UnitNum = 40, CH.YN = False > >> >> WHERE CU.PartsID=41; > > >> > Access 2007 > > >> > The SQL is: > >> > UPDATE qryMoveMachineSelect SET > >> > qryMoveMachineSelect.[AVAILABLE?] No, > >> > qryMoveMachineSelect.ASSET_NUMBER [Forms]![frmInstallations]! > >> > [Text43], qryMoveMachineSelect.OPENING_METER_1 = [Forms]! > >> > [frmInstallations]![txtMeter1], > >> > qryMoveMachineSelect.OPENING_METER_2 > >> > [Forms]![frmInstallations]![txtmeter2], > >> > qryMoveMachineSelect.OPENING_METER_3 = > >> > [Forms]![frmInstallations]! [txtxMeter3], > >> > qryMoveMachineSelect.DATE_INSTALLED = [Forms]! > >> > [frmInstallations]![txtDate]; > > >> > Originally I was trying to update directly to the tables but > >> > thought that may be the problem so I creaed a select query to > >> > get the data but the result was exactly the same > > >> Either you miscopied the SQL from your query or you have some > >> messed up statements. > > >> When I break down the query into individual statements I see > >> missing equals (=) signs in 2 places) and missing quotes around > >> a value. > > >> UPDATE qryMoveMachineSelect > >> SET > >> qryMoveMachineSelect.[AVAILABLE?] No, should be = 'no' or = false > > >> qryMoveMachineSelect.ASSET_NUMBER = [Forms]![frmInstallations]! > >> [Text43], > > >> qryMoveMachineSelect.OPENING_METER_1 = > >> [Forms]![frmInstallations]! [txtMeter1], > > >> qryMoveMachineSelect.OPENING_METER_2 [Forms]![frmInstallations]! > >> [txtmeter2], missing equals sign > > >> qryMoveMachineSelect.OPENING_METER_3 = > >> [Forms]![frmInstallations]! [txtxMeter3], > > >> qryMoveMachineSelect.DATE_INSTALLED = [Forms]![frmInstallations]! > >> [txtDate];- Hide quoted text - > > >> - Show quoted text - > > > Hi > > > The equals signs are in the query. I built the query using the > > standard QBE screen. Also I am led to understand that updating two > > tables is feasible and am trying to understand why this will not > > work rather than creat two separate queries.. > > > Steve > > There are several causes where Access, and SQL in general, can create > non-updateable queries. > Some reasons: three tables in the query, left or right join in the > query, no primary key on one or more tables.(Sometimes adding the > keys to the queries helps that)- Hide quoted text - > > - Show quoted text - Thanks for your help...I managed to figure out my problem which was down to poor design on my part. I had been using the wrong field for the initial select query. Also, this field was updated when the update query was run initially to the value of the record which had the available? yes/no field which was why the yes/no field was updated when I ran the query again....By altering my select query to inlude the correct fields the update query runs as anticipated. Thanks for your input and sorry for wasting your time...I will however remember the lesson well as I have been looking at this on and off for weeks and I will keep in mind the the points you have made re non updateable queries Steve |