Results 1 to 6 of 6
  1. #1
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8

    Create unique id based on date created


    I have set up a basic table on Access. Ist field is the primary key ID field which automatically increments which I don't want to touch.

    I have a field called OpenDate and another called UFN.

    I have created a form with these three fields with the form named as frmOpenFile.

    What I need is code to be entered on the AfterUpdate event in the textbox [OpenDate] to convert the format from e.g. 01/02/2019 to 010219 and auto insert that in to the UFN box with an increment of three digits at the end to give the unique Id.therefore when you select a date opened the UFN box is autopopulated with the next unused UFN. Therefore if u select 1st Feb the UFN box should autopopulate with 010219/001.

    If I open a new record and insert the same date the UFN should be 010219/002 and so on.
    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    There's no need. Create an ID field using the AUTONUM type.
    No need to re-invent the wheel.

    you can use both the Auto Field and date in conjunction to find things.

  3. #3
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    something like

    format(date(),"ddmmyy") & "/" & format(dmax("val(right(UFN,3))","myTable","left(UF N,6)='" & format(date(),"ddmmyy") & "'")+1,"000")

    But this should not be used until the record is created (not the after update of a text box) because another user may already have run that calculation but not yet created their record - so you will end up with two records with the same UFN

    Personally I would have a 'date created' field with a default of date() or now() and just store the UFN part. The you can simply concatenate together as and when required.

    Further, whilst I appreciate the potential usefulness of having a count per day, how often in reality would a user want to search for a record up by using that reference? Are they not more likely to want to ssearch by some other value, such as a date or name?

    And finally, this will not guarantee you don't get gaps - a record created and the deleted would present a gap.

  5. #5
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Where exactly do I enter the code?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I would recommend in vba in the form before update event. All depends on how you want to use it. At the moment it feels like you are going down the wrong road, and I can't help you with that.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-19-2017, 03:44 PM
  2. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  3. Replies: 3
    Last Post: 05-21-2015, 11:26 AM
  4. Target Date Based on Created Date
    By mwong1782 in forum Access
    Replies: 1
    Last Post: 12-17-2013, 05:51 PM
  5. lost - linking date field based on a record with smaller Unique ID
    By stan.chernov@gmail.com in forum Queries
    Replies: 7
    Last Post: 09-16-2010, 02:22 PM

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