Results 1 to 2 of 2
  1. #1
    24t42 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010

    help writing code that will update a table

    I need a little help writing a script. I donít think it is complicated. However, I have managed to confuse myself the last three weeks reading about Access and trying to figure it out.

    I have written a DB for an antique radio conference. They host two auctions. I have a form called Settle Auction, which shows what someone has bought and sold. It is based on two tables.

    tblNameList-holds name, address, etc. information. The primary key is called ID.

    tblAuctionItem-contains all the information about what was bought and sold. The field Buyer_ID joins to tblNameList and has the same value. It also has a field named PAID.

    From the form SettleAuction, I can print a receipt for the auction items. With the click of a button, the report rptReceipt opens and is filtered on a particular ID. This part is working fine. I am trying to write some code that when the report is closed, the field PAID (in tblAuctionItem)will be updated to contain todayís date. I need this to be done for every item purchased by that person. This can be anywhere from three to 100 items.

    For example, buyer number 252 is ready for check out. I open SettleAuction to number 252. I then click a button to open rptReceipt which opens to number 252. I print the receipt. When I close the report, I want today's date to be placed on tblAuctionItem for the three items that buyer 252 purchased. I hope that makes sense.

    Any help would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    The UPDATE action is quite simple. The real trick is figuring out what event to put code into. Maybe the report Close event.

    CurrentDb.Execute "UPDATE tblAuction SET Paid=Date() WHERE Buyer_ID = " & Me.ID & " AND Paid Is Null"
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-09-2014, 02:01 AM
  2. Update to a table with code
    By jlk in forum Programming
    Replies: 4
    Last Post: 01-07-2013, 03:37 AM
  3. Writing Code for "After Update" Table Events
    By dipique in forum Programming
    Replies: 10
    Last Post: 07-09-2012, 08:11 AM
  4. Writing code on the fly - TransferSpreadsheet
    By IanT in forum Import/Export Data
    Replies: 1
    Last Post: 05-24-2012, 08:28 AM
  5. Replies: 7
    Last Post: 05-31-2011, 11:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums