Results 1 to 8 of 8
  1. #1
    arshadmgic is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    4

    Alpha Numeric auto id?

    Hi,


    Can someone help me how I can create alphanumeric auto id in my form?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What are the rules?
    Why does it need to be alpanumeric?
    Will Autonumber not work for your situation?

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    You can generate an Alpha/Numeric auto-incrementing 'number,' but as has been said, we really need to know what your exact requirements are, in order to help you.

    Also, is this a multi-user environment?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    arshadmgic is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    4
    I Just need the method how to generate

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The reason we are asking about your "need for an alphanumeric auto increasing number" is because an autonumber should have no meaning to you. An autonumber serves to assign a unique number to each record in your table only - Not necessarily incremental/sequential; not necessarily positive.

    see Graham Seach's article here http://www.office-archive.com/41-ms-...5e4f31bab5.htm

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    What orange has said is very true, if your only purpose for this 'Alpha Numeric auto id' is to act as a unique Record identifier. If this is the purpose, here, you should use an Autonumber, generated automatically by Access, and there is no reason for this to be Alpha Numeric, as it should never be seen by human eyes! If you're trying to do something else, here, such as generate a customer number, account number, etc., that's another story altogether.

    Assuming you're talking about the latter scenario, as I said before, you can generate an Alpha/Numeric auto-incrementing 'number.' But how you go about this, as also said before, depends on your requirements!

    Do you only want to increment the Numeric portion?

    Is the Alpha portion always the same, or does it change, as well?

    If Yes to #2, when/why does the Alpha portion change?

    If Yes to #2, is the Numeric portion reset to one when the Alpha portion changes?

    Things like that! A general method, for simply increasing a 'numbers only' ID number, would be
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     
     If Me.NewRecord Then
      Me.ID = Nz(DMax("[ID_Field]", "YourTableName") + 1, 1)
     End If
    
    End Sub

    If above would be used in a multi-user environment, because you need to assign the number at the last possible moment, to decrease the chance of two users getting the same generated number. If this is intended for a single-user environment, you can put the same code in the Form_Current event.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    arshadmgic is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    4
    Thank you very much. Now it is clear to me.

  8. #8
    arshadmgic is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    4
    Plz Explain this Portion in detail.thanks


    ===
    As for starting the Autonumber at 10,000, use the following procedure:

    1. In table design view, (if the Autonumber field is the primary key),
    remove the primary key designator.
    2. Change the Autonumber field to a Number (Long Integer).
    3. Save the table.
    4. Create an insert query to create a new record in the target table.
    INSERT tblMyTable (PrimaryKeyField, SomeOtherField) VALUES (9999, "other
    value")
    'This will insert a record whose Autonumber field = 9999.
    5. View the data in the table, and delete the record you added in Step 4.
    6. Open the table in design view, and restore the field to an Autonumber
    data type.
    7. Also restore the primary key designator.

    The next record added to this table will be 10,000. Just make sure you don't
    compact the database before entering a new record into this table, otherwise
    the Autonumber field will be reset.==

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

Similar Threads

  1. Report in alpha order
    By khartoum in forum Reports
    Replies: 12
    Last Post: 03-27-2012, 09:01 AM
  2. Acess vs Alpha Five
    By afc in forum Access
    Replies: 5
    Last Post: 03-20-2012, 11:42 AM
  3. Filtering for numeric value
    By Bear in forum Access
    Replies: 21
    Last Post: 07-31-2011, 05:08 PM
  4. Numeric vs. Text
    By Niki in forum Access
    Replies: 4
    Last Post: 06-10-2011, 01:28 AM
  5. Recordset not showing alpha-numeric entries
    By rayhawk in forum Access
    Replies: 9
    Last Post: 10-29-2010, 08:15 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