Custom Auto Number (Access 2007)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Custom Auto Number (Access 2007)

  1. #1
    Join Date
    Sep 2009
    Posts
    9

    Custom Auto Number (Access 2007)

    I have a database that requires an auto number sequence as such,

    Box 1 A1
    Box 1 A2
    .
    .
    .up to>
    .
    .
    Box 1 A10
    Box 1 B1
    .
    .
    .up to>
    .
    .
    Box 1 J10

    After Box 1 J10>

    Box 2 A1
    Box 2 A2

    etc.

    Any help with how to create this sequence automatically appear would be much appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Oct 2009
    Posts
    8
    Put an autonumber field named ID in your table. Put a text field named ID2 in your table. Create a form bound to the table. Lock & disable the textbox bound to ID2. Add a BeforeUpdate event to the form with this code:

    If Me.NewRecord Then
    Me!ID2 = "Box " & CStr(((Me!ID \ 100) Mod 10) + 1) & " " & Chr$(65 + ((Me!ID \ 10) Mod 10)) & CStr(IIf(Me!ID Mod 10 = 0, 10, Me!ID Mod 10))
    End If

    This will automatically populate ID2 field with what you need when a new record is entered.

    Another point: Rather than have another field based on the ID, it's probably more correct to display the ID value in the format you want (using the formula above). That way you only save the ID field in your table.

  3. #3
    Join Date
    Sep 2009
    Posts
    9
    Thank you for your reply. I tried following your advise but unfortunately I can't get it to work. I'd highly appreciate if you could kindly make an access database with a table with your commands in it, so that I could have a look and see what I'm doing wrong.

    Thanks in advance.

  4. #4
    Join Date
    Oct 2008
    Posts
    142

    sql idea for your custom identity table

    hi there,

    i mean your table with the help of an identity column and a constraint expression.

    create table t1
    (id autonumber, my_custom_code some_type_of_string, contraint my_constraint (my_custom_code = A + id));

    i dont have access installed, but my answer is among these lines.

    best regards,

    tonci.

  5. #5
    Join Date
    Oct 2008
    Posts
    142

    access 2007 constraint

    hi there again,

    this is an example of a validation constraint for access 2007:
    http://office.microsoft.com/en-us/ac...896781033.aspx

    constraint default value in access 2007 (which is the one i am proposing) is something i cannot see because currently i dont have access installed.
    however, it is close to constraint default value of ansi sql.

    contraint constraint_name
    default 'A' + autonumber_column for custom_identity_column;

    good luck in this custom identity column.

    best regards,

    tonci.

  6. #6
    Join Date
    Sep 2009
    Posts
    9
    Thanks alot for the replies, I managed to get your code to work in 2003 however it doesn't work in 2007. I will use your advise and see what I can do.

    By the way, with your code I get the numbering as such;

    Box 1 A1
    .
    .
    .
    Box 1 A9
    Box 1 B10
    Box 1 B1

    How could I change it so that it would appear like,

    Box 1 A9
    Box 1 A10
    Box 1 B1

    Since after A9, B10 comes and then B1

  7. #7
    Join Date
    Oct 2008
    Posts
    142

    the answer is in you constraint default value

    you take autonumber_column remainder of autonumber_column divided by 10, and you append that number to the end of your custom code.

    it is close to that pseudocode i am sending you.
    you need to implement that in your constraint default value.

    it might be something like:

    default 'A' + (id mod 10) for custom_autonumber_column

    good luck and i am glad i was some help to you customer autonumber column.

    tonci.

  8. #8
    Join Date
    Sep 2009
    Posts
    9
    thnx i will look in2 it... really new to both programming and access... so need to learn alot... anyway thnx again for your help.

  9. #9
    Join Date
    Sep 2009
    Posts
    9
    hi

    I have tried everything possible but unfortunately i always get the following ;

    A8
    A9
    B10
    B1
    B2

    I really want A10 to appear after A9 and then B1 instead of B10

    any ideas?

    thanks

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Then use A09, B01, etc - then they will sort they way you want them to.

    However, A10 will ALWAYS come before A9 because it will sort on the A1 through A9 and disregard the second digit. Thats why you need to make all number two digit numbers and then they will sort properly.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Determine auto number using ADO schema
    By ip266 in forum ASP.NET
    Replies: 0
    Last Post: 11-11-2001, 10:59 PM
  2. Determine an auto number field in ADO
    By ip266 in forum ASP.NET
    Replies: 0
    Last Post: 10-29-2001, 09:22 PM
  3. custom Windows Domain access
    By Ory Adler in forum ASP.NET
    Replies: 1
    Last Post: 07-23-2001, 04:14 AM
  4. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 11:04 AM
  5. Auto Number in Table SQL Server
    By Mgunawan in forum Database
    Replies: 5
    Last Post: 04-27-2001, 05:11 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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center