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

Steve_s wrote:

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;

On 17 July, 17:39, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
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

Why do you need to do both fields at once?
This seems to be a job for two update queries.

Steve_s wrote:

Steve_s <stevo.s (AT) hotmail (DOT) co.uk> wrote in
news:9a2196e2-1381-45b3-a156-e7906a6f7d44 (AT) q12g2000yqj (DOT) googlegroups.co
m:

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];

On 18 July, 16:22, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
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

Steve_s <stevo.s (AT) hotmail (DOT) co.uk> wrote in
news:aca27a60-602c-4955-9c0d-a87af37750d2 (AT) d8g2000yqf (DOT) googlegroups.com
:

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)

On 19 July, 23:17, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
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