Prev: Form OnClose vs. subform BeforeUpdate validation: the showdown
Next: Way pdf file not will stay permanent open
From: Steve_s on 17 Jul 2010 08:06
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

From: Salad on 17 Jul 2010 12:39
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;

From: Steve_s on 18 Jul 2010 04:07
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


From: Bob Barrows on 18 Jul 2010 11:02
Why do you need to do both fields at once?
This seems to be a job for two update queries.

Steve_s wrote:
> 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


From: Bob Quintal on 18 Jul 2010 11:22
Steve_s <stevo.s(a)hotmail.co.uk> wrote in
news: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];

 |  Next  |  Last
Pages: 1 2
Prev: Form OnClose vs. subform BeforeUpdate validation: the showdown
Next: Way pdf file not will stay permanent open