Results 1 to 3 of 3

Thread: VBA to Add 1 to Highest Number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to Add 1 to Highest Number

    Very complicated one here so I won't explain why I need this..

    I want to create a button that when clicked a pop-up appears saying "Enter URN". When the user enters this info, the record with that URN is updated. It will update by adding a number to the 'CompNo' field which will be Maximum number currently in that field for all rows +1.

    e.g. There's a record with a CompNo of 1002 (Which is the highest number currently). When the user enters the URN, the record that links to that URN is updated so the CompNo is changed from Null to 1003.

    The easiest way of explaining is this:
    Select [URN]
    Update [CompNo] to Max([CompNo])+1

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    648
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Add 1 to Highest Number

    I'd probably use DMax. Here I update the date for record with ID = 1 with the max date plus 1 day in the same table.


    Code:
    UPDATE Transactions SET Transactions.TransactionDate = DMax("TransactionDate","Transactions") +1
    WHERE (((Transactions.ID)=1));
    Quote Originally Posted by QandAdam View Post
    Very complicated one here so I won't explain why I need this..

    I want to create a button that when clicked a pop-up appears saying "Enter URN". When the user enters this info, the record with that URN is updated. It will update by adding a number to the 'CompNo' field which will be Maximum number currently in that field for all rows +1.

    e.g. There's a record with a CompNo of 1002 (Which is the highest number currently). When the user enters the URN, the record that links to that URN is updated so the CompNo is changed from Null to 1003.

    The easiest way of explaining is this:
    Select [URN]
    Update [CompNo] to Max([CompNo])+1
    Last edited by welshgasman; Jan 27th, 2019 at 02:57 PM.
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,048
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to Add 1 to Highest Number

    CompNo is just number or has letter on it?

    edit: sorry, just realized it's an access forum, which I know nothing about.
    Last edited by Akuini; Jan 28th, 2019 at 06:51 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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