Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mass Update of Field Values by Form or Datasheet? (Access XP)

    A question from a new Access User. I want to do a mass update of a field value, setting a large number of them to a specified value. I don't want to learn how to do Update Queries (which is what Help says to do), at least not yet. I tried to find a way to do this using Form View, but I couldn't find a way. I then tried to use Datasheet View. Hey, I thought, it's just like a spreadsheet -- I'll just copy and paste the value I want to multiple cells in the same column! No way. Access lets me select multiple cells for my "Paste" range, but when I do the actual paste, it just pastes the value into the first cell of the Paste range. Must be a bug.

    Anyone know how to do what I want to do in Form or Datasheet view?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Update of Field Values by Form or Datasheet? (Access XP)

    There is a wizard that will help you with an update query - if you will have to learn them why not now.

    a brief overview.
    create query.
    select table.
    add field to query.
    add filter if necessary.
    type in what you want to update to.
    press execute query.
    DONE. vastly faster than trying to do it by hand.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Mass Update of Field Values by Form or Datasheet? (Access XP)

    Although an update query is the way to go. (They really aren't that hard) If you insist on another method then open the table. Select all records then copy and paste them to excel. Manipulate the data in excel. When finished (do not change the order of the columns). Delete all data in the table then copy and paste the records back into access.

    Again - It is not recommended that you do this as an update query is not hard to do. Create a selet query to get the data set to be changed and then change the query to an update query with the change to be made. It will save you a lot of time.

    Note: Highlighting columns of data to be changed by pasting is not a bug....

    HTH
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mass Update of Field Values by Form or Datasheet? (Access XP)

    As you have discovered, a datasheet or table may *look* like an Excel worksheet, but it isn't even close to being the same thing. Access is a relational database and the rules don't allow you to do what you're trying. Update queries are the only way to do this in Access unless you want to learn how to write VBA code to handle it. Even then, the query is faster. There is NO "range" object in Access, so you can't just copy and paste.
    Charlotte

Posting Permissions

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