Results 1 to 8 of 8
  1. #1
    Dropout is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Location
    Ontario
    Posts
    19

    Update query increment

    Good afternoon All.

    I am working with my first update query. It's working well expect in one instance I need to to look at the last record with no value, add 1 to that value (not update it) and use the new number for all records with no value. I'm updating other information in these records at the same time.

    What I'm trying to do is assign pack slip numbers to records and increment them by 1.

    If I'm clear....

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,010
    Do a Dmax to get the largest value, then add 1...

    iVal =Dmax("[id]","table")
    iVal=iVal + 1

    sql= "insert into table (ID,field2,field3) values (" & iVal & ",'bob','engineer')"

  3. #3
    Dropout is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Location
    Ontario
    Posts
    19
    Thanks for the response.

    I've spent a couple of days reading about this and I understand what the intent is, but I'm unclear about how to add this to my form.

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,181
    Not very easy to follow you without an example, picture, sample db, etc.

    Cheers.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Dropout is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Location
    Ontario
    Posts
    19
    Sorry I took so long to respond.

    I've attached screenshots of the table, the existing query and the table after running the query. My thought is to somehow force the query to run when opening a report but I need to get it to work first.

    My idea is to have the query:

    Add the date to Ship_Date
    Change Ship_Confirmation from No to Yes
    Add the Pack_Slip_Number

    The first 2 seem to work. For the Pack_Slip_Number I guess I need to read the last number, increment it by one and write it in the column for each record that I'm updating.

    I hope this is presented well.
    Attached Thumbnails Attached Thumbnails Screenshot 2020-03-03 13.07.00.png   Screenshot 2020-03-03 13.19.54.png   Screenshot 2020-03-03 13.19.43.png  

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,181
    In the Update To row for the Pack_Slip_Number try: DMax("[Pack_Slip_Number]","[tbl_Shipping_data]") +1 (assumes Pack_Slip_Number is a numeric field, if not you need to strip the Alpha part, increment the numeric part then concatenate the alpha back).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Dropout is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Location
    Ontario
    Posts
    19
    That did it. Thanks!

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,181
    You're welcome, ranman256 should get the credit!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 2
    Last Post: 12-03-2019, 10:15 AM
  2. Append Query and Increment Number
    By burrina in forum Queries
    Replies: 1
    Last Post: 02-02-2013, 12:28 AM
  3. Auto-increment fields update
    By rkalapura in forum Forms
    Replies: 3
    Last Post: 01-07-2012, 06:37 AM
  4. How to increment values through a query
    By doci4a in forum Queries
    Replies: 3
    Last Post: 03-17-2011, 08:25 AM
  5. Replies: 5
    Last Post: 11-12-2010, 12:10 PM

Posting Permissions

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