How to Update multiple fields in a table

[tara99]

Can some one tell me the correct syntax of Update statment for updating multiple fields.
I want to update few item in record (few fields i a table).

Thanks

[PEB]

Can some one tell me the correct syntax of Update statment for updating multiple fields.
I want to update few item in record (few fields i a table).

Thanks

Yeah,

Go in Queries, than choose new one! Choose the design instaed using wizard!

From the menu choose query -> Update query

Add the table u want to update

Add the records that should be updated...

In the row Update to write "kdlsk" if kdlsk is the string that u want to update to your field

If u want to update a field with the information from other field type: [TheSourceField] Where TheSourceField is the name of the field with the information!

[NeoPa]

If you just want to do it, then PEB's answer helps you to do it easily.
If you want the syntax then follow instructions in PEB's then select View / SQL view. Look at the SQL code there and you will have an example.

Otherwise the Help doesn't mention the multiple fields but give the general info.

UPDATE Statement
Creates an update query that changes values in fields in a specified table based on specified criteria.

Syntax
UPDATE table
SET newvalue
WHERE criteria;

The UPDATE statement has these parts:

Part Description
table The name of the table containing the data you want to modify.
newvalue An expression that determines the value to be inserted into a particular field in the updated records.
criteria An expression that determines which records will be updated. Only records that satisfy the expression are updated.

[MMcCarthy]

For Multiple Fields

UPDATE TableName
SET Field1=NewValue1, Field2=NewValue2, etc

.

Mary

[NeoPa]

Yes, even for a single field it's pretty inaccurate it seems.
I missed that when I noticed it didn't even include syntax for multiple fields.

[MMcCarthy]

Yes, even for a single field it's pretty inaccurate it seems.
I missed that when I noticed it didn't even include syntax for multiple fields.

I've made so many mistakes with this syntax recently that I remembered it instantly.

OK, I'm going back to work now. :)

Mary

[NeoPa]

That'll be the work you weren't going to let TheScripts interrupt tonight then?

[tara99]

For Multiple Fields

UPDATE TableName
SET Field1=NewValue1, Field2=NewValue2, etc

.

Mary

Hi Guys
I have used the following code

Dim strSQL As String
 
strSQL = "UPDATE Applications " & _
    "SET Owner = " & Me.NewOwner & " , Responsible = " & Me.NewResponsbile & " " & _
    "WHERE Application = " & Me.Application & ";" 
  DoCmd.RunSQL strSQL
 
 

But there is error message saying 'Object doesn't support this property or method "
Any idea why????

Note: Applications is the name of table, this table has 4 fields ApplicationID, Application, Owner, Responsible
The NewOwner is the name of the textbox that I have created for the user to enter the new Owner.
I just want to update the Owner and Responsible fields

Thanks

[MMcCarthy]

That'll be the work you weren't going to let TheScripts interrupt tonight then?

Yeah that work.

Oh well. :D


Mary

[MMcCarthy]

Try this ...

Dim strSQL As String
 
strSQL = "UPDATE Applications " & _
    "SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
    "WHERE Application='" & Me.Application & "';" 
  DoCmd.RunSQL strSQL
 
 

Assuming Owner is a text field you will need the single quotes.
Assuming NewResposible is a number you won't
Are you sure the Where Criteria is on Application and not ApplicationID.
If Application Assuming it's a text field and you will need the single quotes.

See Adrian thread on this is you're still confused.

Quotes (') and Double-Quotes (") - Where and When to use them

Mary

[tara99]

Try this ...

Dim strSQL As String
 
strSQL = "UPDATE Applications " & _
    "SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
    "WHERE Application='" & Me.Application & "';" 
  DoCmd.RunSQL strSQL
 
 

Assuming Owner is a text field you will need the single quotes.
Assuming NewResposible is a number you won't
Are you sure the Where Criteria is on Application and not ApplicationID.
If Application Assuming it's a text field and you will need the single quotes.

See Adrian thread on this is you're still confused.

Quotes (') and Double-Quotes (") - Where and When to use them

Mary

Thank you Mary
I am working on something else which needs to be finish the next 2 days.
But i will try the above code and get back to you.
cheers

[MMcCarthy]

Thank you Mary
I am working on something else which needs to be finish the next 2 days.
But i will try the above code and get back to you.
cheers

No problem Tara

Mary