Problem with Update query

[Tony Williams]

I am trying to update the value of a field in a table based on the value of
another with a WHERE clause. Here is my SQL statement:

UPDATE Table1 INNER JOIN Table2 ON Table1.txtVRM = Table2.txtVRM SET
Table1.txtStatus = [Table2].[txtstatus]
WHERE (([Table2].[txtstatus]<>"01"));

I want the value of Table1.txtstatus to be updated to the value of
Table2.txtstatus only where the value of Table2.txtstatus is not 01

Where am I going wrong?
Thanks
Tony

 

[Stefan Hoffmann]

hi Tony,

Where am I going wrong?

The syntax is correct.

ON Table1.txtVRM = Table2.txtVRM SET

Is the data correct, does this JOIN (condition) returns the same number
of records as you have in Table1?


mfG
--> stefan <--

 

[John Spencer]

The query looks like it should work. It won't update table1 if
txtStatus in table2 is NULL or is equal to "01".

Since you didn't tell us in what way the query is failing, it is
difficult to give you a more comprehensive answer. Are you getting a
syntax error, the wrong records being updated, only some records being
updated, no records updated, a prompt for some value, OR ????

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tony Williams]

UM? I've checked the table1 and the records in the table are correct but when
I view a form I have based on table1 the txtstatus field has been changed
from 01 to blanks. Looks like it is something to do with the form and not the
query?
I'll have to check that out.
Tony

The query looks like it should work. It won't update table1 if
txtStatus in table2 is NULL or is equal to "01".

Since you didn't tell us in what way the query is failing, it is
difficult to give you a more comprehensive answer. Are you getting a
syntax error, the wrong records being updated, only some records being
updated, no records updated, a prompt for some value, OR ????

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to update the value of a field in a table based on the value of
another with a WHERE clause. Here is my SQL statement:

UPDATE Table1 INNER JOIN Table2 ON Table1.txtVRM = Table2.txtVRM SET
Table1.txtStatus = [Table2].[txtstatus]
WHERE (([Table2].[txtstatus]<>"01"));

I want the value of Table1.txtstatus to be updated to the value of
Table2.txtstatus only where the value of Table2.txtstatus is not 01

Where am I going wrong?
Thanks
Tony

 

[Stefan Hoffmann]

hi Tony,

UM? I've checked the table1 and the records in the table are correct but when
I view a form I have based on table1 the txtstatus field has been changed
from 01 to blanks. Looks like it is something to do with the form and not the
query?

You're using txtVRM in your update query. Have you really named that
field like that? Or are you trying to reference a control from your form
in your query?


mfG
--> stefan <--

 

[Tony Williams]

Hi Stefan I do have a field txtvrm in table1 which is the control source of a
control on the form in question. I think the problem is this:

On my form is a combobox which is based on another table which when the user
selcts a value from the drop down list the value is stored in the field
txtstatus in Table1. So far so good. However when I run the update to try and
update the value of txtstatus in Table1, the query updates the table but when
I view the form it hasn't updated the value on the control. When I think
about it I realise that it wouldn't do because the combobox is getting its
row source values from a different table even though the control source of
the combobox is set to txtstatus.
Hope I've explained that. I need to look at my form i think.
Thanks again
Tony

 

[Tony Williams]

Second thought - is it possible to get the update query to update the value
of the control on the form? The values which are being used to update Table1
will also appear in the table used to popolate the combox so there shouldn't
be a problem with a conflict of values or field types?