Results 1 to 9 of 9
  1. #1
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117

    Using VBA to autogenertae a Unique ID

    Hi guys,



    I have a project at work and my manager wants to me create a unique id that when employees use the form on access to fill out a request we do not have to assign them an ID, access will already assign them an ID with a click of a button assigned to a Unique ID field on a form or when creating a new record on a form, which will then automatically go on to the next Id. I do not have knowledge on vba and looking for some help if somebody can post some code, that would be really appreciated. The format of the ID for two out of the three forms that I have created have to be "EMR-16-001" and "GT-16-001". EMR and GT are abbreviations for the form the employee is using and 16 is the current year, and 001 or just numbers my boss has given me. Then I want both Id's to increment by 001 to 002 to 003 and so on, while EMR, GT and the year stay the same. Does anybody know how to code this to a button which is linked to the unique id field on the form that when I click it, it will automatically generate the id for the user, and when a user puts in a new form, it will automatically increment again, or when creating a new record the id automatically increments.

    Thanks, all help is appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There are some thoughts here:

    http://www.baldyweb.com/CustomAutonumber.htm

    the most common method is the DMax() near the bottom.

    I deleted your duplicate threads. Please don't post the same question multiple times.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Do keep an autonumber field (just in case)
    To create a company ID:
    If you know the batch: either EMR, or GT
    and year:
    Code:
    sub btnMakeID_click()
    dim i as integer, iNum as integer
    dim vYr,vBase,vMAxID,vNewID
       'combobox cboBatch ,user picks EMR or GT
    vYr = format(Date(),"yy")
    vBase = cboBatch & "-" & vYr
    vBase2 = left(vBase,2)
       'get the existing max number using the batch ID
    vMaxID = DMax("[CoID]","table","[CoID] like '" & vBase2  & "*'")
    select case vbase2
        case "EM"
           i = 8
        case "GT"
           i = 7
    end select
    iNum = mid(vMaxID,i)
    vNewID = vBase & "-" & format((iNum +1),"000")

  4. #4
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117
    Hey ranman,

    where would I type in this code, I know in vba but would I type in on the button code for event click?

    Thanks

  5. #5
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117

    Using VBA to autogenertae a Unique ID

    Hello everybody,

    I have posted before but my after talking to my manager we have come up with a format with a unique id that he wants me to auto generate. I have a form with three fields, which are
    1.model (name is "model_type")
    2.date
    3.Id(primary key)
    The model is a combo box with options the user chooses. Then I have the date which is automatically put in with the function "Date()" in default value. Then I have an Id field with an auto generate button I have created right next to the field. First question is, should the id field be auto number or text data type? Second question is how do I code the button to first of all to link to the field so when I click on it, it will auto generate the id( the id field name is "uniqueid"), and third, how do I code the button to auto generate the unique id. The format of the id my boss has given me is first the model, depending on what the user clicks on from the combo box, second is the current year, it can be 2016 or just 16, and then 001 or 0001, either is fine. Then after that, the last part of the id 001 will increment to 002 or 0002 to 003 or 0003 and so on after clicking the button. So for example the format of the id that can be auto generated could be "S72-16-001" or "S89-2016-0001", the first part depending on what model the user picked l and the second id generated in the database can be "S72-16-002". All help will be appreciated. I am a beginner in access and have very little knowledge on vba.

    Thanks!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    duplicate post in this forum

  8. #8
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117
    Thanks for posting, it is something to think about but this is for a company and only employees, this won't really be a problem or big issue, but if anybody can post vba code, that will be helpful.

    Thanks!

  9. #9
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117
    Hey ranman256, how does this work sorry I am new. I still want this linked to a click of a button, but if the way you say to do it through a query will autogenerate the without clicking the button, that will be fine. But will this way auto generate the id for the user on the form, without any of us going to the table or query to put in the id? Can you please explain where I would type this and do this, sorry im a noob.

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

Similar Threads

  1. Replies: 22
    Last Post: 03-03-2013, 02:00 PM
  2. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 01:28 PM
  3. More unique valuecombinations
    By superfury in forum Access
    Replies: 3
    Last Post: 08-19-2011, 06:08 AM
  4. how to set a unique ID
    By archie in forum Access
    Replies: 1
    Last Post: 09-08-2009, 04:28 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