Results 1 to 9 of 9

Auto increment a number based on record value from previous record

  1. #1
    markw is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    5

    Auto increment a number based on record value from previous record

    This question is kind of a tag on to a question I had a couple days ago in a different 'gub group' here on the Accessforums.net.

    My question this time, is in regards to a paragraph I read in the book "Microsoft Access 2010 for Dummies", on page 111 under a 'tip' it reads:
    "If you want to get fancy, create a macro that automatically fills in the next invoice number in sequence"

    I'm assuming this macro would look at the value of the previous record and add a one to it. Being new to access I'm not sure how you do this. Anybody here on the forums be able to help me on this?

    I don't want to use a AutoNumber field as I might need (a) to modify value or (b) need to skip numbers.

    thanks for reading.

    Mark

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370

  3. #3
    markw is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    5
    It probably would/will if I was a VBA programmer. Maybe I can find a co-worker that can walk be through it.

    Thanks

  4. #4
    bcmarshall is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    56
    This is a simple issue to resolve. Use a SetValue command within a macro, and set the value of the desired field to DMax("[FieldName]","[QueryorTableName]")+1. It'll act like an autonumber field, always incrementing the largest number available by one. Since it is not an autonumber field, should you choose to manually insert another number it will allow it. Make sure that the properties in the table are set to Indexed: Yes (No Duplicates). That will prevent you from accidentally entering the same number twice.

  5. #5
    tanjamaya is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    2
    Quote Originally Posted by bcmarshall View Post
    This is a simple issue to resolve. Use a SetValue command within a macro, and set the value of the desired field to DMax("[FieldName]","[QueryorTableName]")+1. It'll act like an autonumber field, always incrementing the largest number available by one. Since it is not an autonumber field, should you choose to manually insert another number it will allow it. Make sure that the properties in the table are set to Indexed: Yes (No Duplicates). That will prevent you from accidentally entering the same number twice.

    Can you be more specific, I'm new to access 2010 and I tried many times but can't make it right... can you make like small tutorial. pls.
    I need these for my Invoice Number.
    Thanks

  6. #6
    bcmarshall is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    56
    I'm not sure how much more specific I can be. Using the DMax function looks up the highest existing value in the domain. DMax+1 obviously increments the highest value.

    Using a SetValue action, you select your invoice number control and set its value to be DMax("[InvoiceNumberField]","[TableorQueryThatContainsIt]")+1

    When the macro runs it will provide you with the next higher number for the control.

    Hope that helps.

  7. #7
    tanjamaya is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    2
    I now I'm pain in the a... But can you please send me some simple file that is made in access 2010

  8. #8
    bcmarshall is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    56
    Perhaps you didn't preceed the expression with an equal sign...try that.

  9. #9
    hicham6w is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    1
    go to data default value of the control

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

Similar Threads

  1. Replies: 5
    Last Post: 11-12-2010, 10:10 AM
  2. Seeing Image from Previous record ......
    By cowboy in forum Reports
    Replies: 1
    Last Post: 02-24-2010, 06:38 AM
  3. SQL Subquery on Previous Record
    By GoVols02 in forum Queries
    Replies: 1
    Last Post: 01-05-2010, 12:40 PM
  4. Replies: 12
    Last Post: 08-09-2009, 06:35 AM
  5. using value from previous record
    By dollars in forum Queries
    Replies: 0
    Last Post: 12-10-2008, 01:30 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