Results 1 to 2 of 2
2003-10-10, 18:47 #1
- Join Date
- Feb 2002
- Thanked 0 Times in 0 Posts
change field value in table by code ? (Access 2000)
Can i change the field value of a table by code?
I have a table orders.The first field, called orderid is an autonumber In my table called orders the first field is orderid and the second field is customerid.
i have a data entry as folows
orderid = 10547
customerid = 1262
I know how to delete the whole row as:
Dim db As Database
Set db = CurrentDb()
db.Execute ("delete * from orders WHERE orderid> 10547")
But i wonder is it possible, instead of deleting the whole row, just to
update the Customerid to read 1265?
2003-10-10, 18:55 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: change field value in table by code ? (Access 2000)
What you want is to execute an update query from code. A good way to learn the SQL "code" for this is to design a query. When you have got it right, switch to SQL view. You will see the SQL statment that is equivalent to the design of your query. If you want to copy and paste this into a module, you need to clean it up a bit:
- remove line ends
- replace double quotes " by single quotes '.
In your example, the code would look like this:
Dim dbs As DAO.Database
Set db = CurrentDb
db.Execute "UPDATE Orders SET CustomerID = 1265 WHERE OrderID = 10547"
Set db = Nothing
- I always prefix DAO and ADODB declarations explicitly for clarity and to avoid confusion.
- You should always set object variables to Nothing after using them, this releases the memory they occupied.