473,320 Members | 1,933 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to Update multiple fields in a table

106 100+
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
Dec 17 '06 #1
11 78761
PEB
1,418 Expert 1GB
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!
Dec 17 '06 #2
NeoPa
32,556 Expert Mod 16PB
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.
Dec 17 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
For Multiple Fields

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName
  2. SET Field1=NewValue1, Field2=NewValue2, etc
.

Mary
Dec 18 '06 #4
NeoPa
32,556 Expert Mod 16PB
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.
Dec 18 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 18 '06 #6
NeoPa
32,556 Expert Mod 16PB
That'll be the work you weren't going to let TheScripts interrupt tonight then?
Dec 18 '06 #7
tara99
106 100+
For Multiple Fields

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName
  2. SET Field1=NewValue1, Field2=NewValue2, etc
.

Mary
Hi Guys
I have used the following code

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE Applications " & _
  4.     "SET Owner = " & Me.NewOwner & " , Responsible = " & Me.NewResponsbile & " " & _
  5.     "WHERE Application = " & Me.Application & ";" 
  6.   DoCmd.RunSQL strSQL
  7.  
  8.  
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
Dec 18 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
That'll be the work you weren't going to let TheScripts interrupt tonight then?
Yeah that work.

Oh well. :D


Mary
Dec 18 '06 #9
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE Applications " & _
  4.     "SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
  5.     "WHERE Application='" & Me.Application & "';" 
  6.   DoCmd.RunSQL strSQL
  7.  
  8.  
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
Dec 18 '06 #10
tara99
106 100+
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE Applications " & _
  4.     "SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
  5.     "WHERE Application='" & Me.Application & "';" 
  6.   DoCmd.RunSQL strSQL
  7.  
  8.  
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
Dec 20 '06 #11
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 20 '06 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: shank | last post by:
Visually, the page will look somewhat like a spreadsheet. It could have hundreds of records (rows) displayed. I want to enable the user to edit any one or any number of records and any fields, then...
4
by: Duane Phillips | last post by:
"David Portas" <snipped for brevity> wrote: Example 1: > > UPDATE table_a > SET col = ? /* Unspecified */ > WHERE EXISTS > (SELECT * > FROM table_b > WHERE table_b.key_col =...
2
by: Fons Roelandt | last post by:
Heelo, I have to Update all fields from a table with the values of a related table, i've tried some querys i found on the internet, but nothing seems to word, i even tried to lookup the value...
5
by: PAUL | last post by:
Hello, I have 2 tables with a relationship set up in the dataset with vb ..net. I add a new record to the parent table then edit an existing child record to have the new parent ID. However when I...
3
by: mkjets | last post by:
I have worked for hours on trying to find a solution and have not figured it out. I am working in Access 2003. I need to create a query that takes values from 1 table and displays them in...
1
by: Mangler | last post by:
I need to set up my front end app to update multiple fields with the same entry. See below. Database: Testing Columns: Recdte Invdte
2
by: Presto | last post by:
I am making a front end mdb so users can enter new members data. I can then import this into the master database on the backend and erase the existing info on the front end to keep the data...
7
by: john.cole | last post by:
I have searched all the groups I can, and I still haven't been able to come up the solution I need. I have the following problem. In my form named sbfrmSpoolList, I am entering a job, spool and...
482
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.