Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2008
    Posts
    25

    Unanswered: Need Autonumber field in Subform to return to value 1 aftern new record on main form

    Hello,

    I'd like my subform to behave so that my numberID (autonumber) field will 'refresh' back to the value 1 every time a new record is entered on the main form.

    Is there a way to do this?

    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In which case you need to devise your own autonumber mechanism.
    there are lots out there.......

    you 'could' set the default value to 1,
    make it non null,
    make a validation rule of >=-1
    make it a unique index (with the parent forms PK).

    having created your first record/row then use the max() function (or domain max.dmax?) to find the highest used number. (you could do this in the sub forms addnew event. you could just cheat and stroe the number in a global variable (boo hiss) in the sub form
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2008
    Posts
    25
    I'm fairly new to Access so please bear with me...

    I understand how to set a default value of 1, but how would I create a rule so that the default value is 1 only when a new record is created on the main form?

    Also - why do I need to find the highest used number of records? Do you mean for the main form or subform?

    thanks

  4. #4
    Join Date
    Jun 2008
    Posts
    163
    try looking at this: http://www.techonthenet.com/access/m...ntial_nbr2.php

    It shows how you can make auto-number sequences. You'll have to adjust the code to suit your needs though (the example does not use subforms).

    The idea there is that you keep a separate table that keeps track of each new record in your main form and the highest (rather, last) value given to that set. The point of keeping track of that value is that it will tell access what the next number in the sequence is.

    Good luck!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The short answer is NO.

    Then the question comes to mind... WHY?! Why would you want to do this?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    The short answer is NO.

    Then the question comes to mind... WHY?! Why would you want to do this?

    has the feel of an invoice, purchaase order or shipping note

    ie heres everyt thign for invoice xxx
    item 1
    item 2
    item 3
    ...
    item n
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And why would that need to be stored as data?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cue Rudy charging in and informing us that mySQL does this out of the box.

    Reseeding:
    http://www.dbforums.com/showthread.php?t=1625651

    Autonumber over group I can see a use for. However, @gbuckton - are you aware that autonumbers can often end up with gaps? Is this important? Is Mark right - do your autonumbers have a "meaning" for the user?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    And why would that need to be stored as data?
    Audit trails would be my guess if it is invoices and it must match a form. In that case, I would insist the operator inputs them manually.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    And why would that need to be stored as data?
    'cos a customer may well be ordering multiple pages of items and is querying a specific item number

    eg we only received 10 instead of 100 of item No X. the description printed on the the form may not match the description in the sellers system due to space constraints,

    soem customers may have a predetermined order in which they order goods, ie in an order that makes sense to them, and they expect the supplier invoice to list itmes in the same order.. so you cannot 'just' default to any order that you may generate. so the item order may well have significance outside your the supplier system.

    eg the customer may be ordering stuff in their own little sequence and they want to see the items on the invocie note in a form that they recognise
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok...

    I would have thought that a product ID be the way to identify an item on an invoice/order.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Aug 2008
    Posts
    25
    I'll give the suggestions a shot - thanks everyone for your help.

  13. #13
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    If it is a matter of using a gapless number sequence (e.g. for order numbers), you can use a sequential number generator. This consists of a small table with an autonumber field. A function (GetNumber(), say) tries to select the minimum available number from the table. If one exists, the function takes the number, then deletes the record in the table. If not, it creates a record, reads the number then deletes it.

    Various refinements are possible. For instance, when selecting a number, a user ID can be placed in a field and a status field set to RESERVED. This prevents the number being taken by another user. Then, if the number is used, the record is deleted as stated. Otherwise, if the order (or whatever) is cancelled, the number can be returned to the pool by setting its status to FREE.

  14. #14
    Join Date
    Jun 2008
    Posts
    163
    If it is a matter of using a gapless number sequence (e.g. for order numbers), you can use a sequential number generator. This consists of a small table with an autonumber field. A function (GetNumber(), say) tries to select the minimum available number from the table. If one exists, the function takes the number, then deletes the record in the table. If not, it creates a record, reads the number then deletes it.
    this would have been really useful to me two months ago =D

    nice tip though!

  15. #15
    Join Date
    Aug 2008
    Posts
    25
    I'm not sure if this is related, but I've noticed that now my form is not writing to my table. When I close and re-open the form, it is starting at Record 1 will all fields blank, even though Data was entered previously and is in the table.

    any ideas?
    thx

Posting Permissions

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