Results 1 to 9 of 9
  1. #1
    walterio is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2019
    Posts
    33

    Get the next AutoNumber value before updating

    Hi


    Is there a possibility to find out which will be the next AutoNumber for such a field, before update?

    I try to explain my intention:

    There is a form just for inserting new records into a table with an AutoNumber field as ID. When the user opens the form, I want him to see which ID the new record will have.
    Inserting the record in the first place (on load) and then finding out the ID produced by access is not a good idea, because the user could decide not to create a new record at all. And then, deleting the created record won't help, because the number has already be used, the next opening of the form will show a higher number, the one offered in the first place is gone forever,... And a Compact & Repair action of the whole DB, just for this litle issue...

    Well, I think you, dear reader and adviser, have gotten an idea of my problem.
    Thank you for your help
    Walter

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The AutoNumber type field makes an excellent Primary Key field but should not be used for meaningful to a user e.g. Invoice Numbers.

    Have a field in your table for such numbers. Use DMax() +1 function to return the next number and assign this as the record is completed, not s the record is started.

    EDIT:

    Code should be in the form's Before Update event and would be something like:

    If Me.NewRecord Then
    Me.TktNum = Nz(DMax("[NameOfInvNumField]", "[NameOfInvoiceTable]"), 0) + 1


    End If
    Last edited by Bob Fitz; 07-01-2020 at 07:29 AM. Reason: Added Code
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As Bob advised, you should not use autonumber datatype if that field has meaning to you and/or you need to have sequential numbering to your records. There are many posts in several forums on this subject. You may find additional comments/suggestions in the Similar Threads at the bottom of the page.
    Also this is a good reference to Autonumber usage.

  4. #4
    walterio is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2019
    Posts
    33
    Thank you Bob. I hoped there would be some magic field method ...

    But then, to let the system check for uniqueness of a field, the AutoNumber as data type is so tempting!

    What do you mean by "not s the record is started" at the end of your sentence?

    As the table is set and I can't do any changes, my solution now is: not to show any ID at all and inform the user after the insert, which ID has been set for the new record.

  5. #5
    walterio is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2019
    Posts
    33
    Thank you for your link, orange. I'll forward it to my customer (he is the table master...)

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by walterio View Post
    Thank you Bob. I hoped there would be some magic field method ...
    What do you mean by "not s the record is started" at the end of your sentence?
    Use the code I gave you in the form's Before Update event which fires when the record is being saved rather than, say, in the On Current event which fires before any data is entered. If a user were to start a new record but then go on tea break there could be a very long time before the record is saved. In a multi-user environment another user may have created a number of new records while the first was on a break.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by walterio View Post
    Thank you for your link, orange. I'll forward it to my customer (he is the table master...)
    Well he should not be. If you are building the database, then that should be you decision. I'm sure my builder would not permit me to tell him how to build my house
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Again I agree with Bob, The client/customer should identify the what functionality is required. The designer/builder should determine how that functionality is achieved with the underlying code/forms etc.
    Good luck with your project.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is the definition I use

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key,


    Also see
    Autonumbers--What they are NOT and What They Are

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

Similar Threads

  1. Replies: 7
    Last Post: 08-28-2017, 02:50 PM
  2. Replies: 3
    Last Post: 03-21-2016, 07:01 AM
  3. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  4. Replies: 1
    Last Post: 02-02-2012, 08:34 PM
  5. Updating Autonumber Fields
    By Rawb in forum Code Repository
    Replies: 4
    Last Post: 02-21-2011, 07:17 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