Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts