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

    Create ID

    Caveat:I am new to access therefore require step by step instructions.



    I was wondering whether you could write us some code so I can create file numbers automatically on entering the date file is opened.

    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
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Apart from recording the opendate twice (remember you already have it in the OpenDate field) , what is the overall purpose of this field ?

    You seem to be creating something unnecessary If all you want is a unique file name simply format the OpenDate with the primary key -

    Code:
    MyCalculatedField: Format([OpenDate],"ddmmyyyy") & "/" & [YourIDField]
    No need to store it you can always calculate it.
    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 ↓↓

  3. #3
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Thank you for your reply.

    If I use the PK field would it not just keep incrementing. I only need the last three to increment but dependant on the date opened.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It will just increment.

    But I'd still like to know the actual purpose of this UFN ? Please describe in simple terms what it achieves in your system?
    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 ↓↓

  5. #5
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    When I open a client file it needs to be assigned a unique file number (ufn) this number is specific to that client and will need to be populated in other forms. One client may have multiple ufns. However on any given day I may have several new clients so therefore I may have the following
    010219/001 client a
    010219/002 client b
    010219/003 client c and so on.

    A few days may pass before I open a new file. Therefore the UFN will be 060219/001

    Apologies for being unclear and for reposting.

  6. #6
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Any update to my previous post? Any help would be most appreciated.

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I understand the numbering sequence, but how does that actually help you in any way?
    I'm not being obtuse here , but I'm trying to get you to think about a couple of factors.

    Just because you have historically named your files this way, doesn't mean it works well once you computerise your system.

    For example I'm Fred your customer and I can't remember when I came in or phoned you and you opened the file...
    Currently how does your file reference help you find my file?

    Moving forwards If I could easily find Fred (or his company) and magically list all the files I ever opened for him, latest first , click on the list and open that file in the blink of an eye, wouldn't that be a improvement?

    Does this get you to think outside your immediate current requirement and maybe look at approaching it from a different angle?
    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 ↓↓

  8. #8
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Sorry Minty. I was planning on adding further fields such as name,address etc but I've been stuck on the first hurdle and thought I would focus on the numbering issue first.

  9. #9
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In that case for the time being, ignore your file name issue, take a step back and draw out on paper (whiteboard) your current process.

    Work out what you want to improve by moving it into a database.
    Work out your desired Outputs & Reports.
    Then you can start to design some tables to store enough data to correctly achieve your outputs.

    Once you are storing your data correctly then and only then!, you get into forms to enter and manipulate that data.

    Most new arrivals do it all the wrong way around, and get tied up in knots because they have dumped a spreadsheet into an access table.

    Bottom line - if you can't move bits of paper around on a desk to follow a process successfully, there's no way you'll do it access.

    Lots of people here will be happy to help with most stages of the above, but only you know your business process.
    Have a read up here on normalisation http://rogersaccessblog.blogspot.com...on-part-i.html
    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 ↓↓

  10. #10
    IS2019 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    8
    Ok will do. Thanks for your advice. Rest assured in the words of Mr Schwarzenegger I'll be back!.

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

Similar Threads

  1. Replies: 14
    Last Post: 12-28-2015, 07:51 AM
  2. Replies: 3
    Last Post: 05-22-2014, 09:23 AM
  3. Replies: 5
    Last Post: 04-04-2014, 03:33 PM
  4. Replies: 7
    Last Post: 01-16-2014, 09:17 AM
  5. Replies: 1
    Last Post: 05-20-2013, 01:45 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