Page 1 of 4 123 ... LastLast
Results 1 to 15 of 53
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    digit number with checker

    friends,

    I'm trying to generate a number of protocol automatically to the User when registering a type of process. I'm trying to register the number with four digits for the year, three digits for the sequence number and a check digit. The year I get to Now (). But how do I create also the three digit number and the digit to the checker, automatically
    Access.

    Thank you.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Hi Fabio,

    Is this something that will be used in a multiuser environment or a single user one?

    Regardless of that, the solution for the sequence number, IMO, has to be a table where you keep the last number generated, and that you access through a VBA function, say GetNextNumber(). In this function you just obtain the last number and increment it, saving the incremented value.
    If you have multiple users doing this, you'd need to ensure no simultaneous access would be allowed, to prevent two users from getting the same number, but you don't really have to worry about that if you will only have one user using your application.

    Not sure I understand what the checker digit is meant for.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-10-29)

  4. #3
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Hi Rui, thanks for replying.

    yes, it is a multiuser environment. How do the concatenation of the year with the number obtained with GetNextNumber function ():

    ex.: 958859854-17, the numbers 1 and 7 after - is the digit checker.

    Thank you.

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Is the intented sequence yyyysssc? Do you want it to be number or a string? If a number, once you get the number relative to the year, multiply it by 1000 and add the sequence number. Multiply the resulting number by 10 and add the check digit. If it is a string, simply use a concatenation op.

    One way or the other, probably doing this in a function would be an option. You could call this function even from a query and this function could call GetNextNumber() (explained before), as part of the process.

    If you are working in a multi-user environment and you can have several users requesting sequence numbers at approximately the same time, care must be taken to ensure they don't get the same number. One way to achieve that is explained here: http://www.tek-tips.com/viewthread.cfm?qid=153007

  6. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-10-30)

  7. #5
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, sorry, I'm a little confused. Let me explain it another way:

    I'm trying that when you register a process, generate a number of automatic protocol, thus:

    2012/0001-09 ...
    2012/0002-09

    2012 is the year. 0001 ... is the sequence number. and 09 is the digit checker (not sure if this is correct digit checker, I'm just showing
    an example). How am I doing test will be generated by many automated system id.

    You can do this using GetNextNumber ()?

  8. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Fabio,

    I would use two functions: GetProtocolNumber(), the main one and GetNextNumber(). The latter would just deal with the sequence number, to keep it separate and thus simpler. GetProtocolNumber() would get the year, would invoke GetNextNUmber() to get the sequence number and would add the other control digits.

    I noticed that the dimension of each component here is different than you had said before (3 digits for sequence number and one control digit only). Also, it seems that you are keeping different sequence numbers per year, so that may lead to a need to keep the year in that table too, and when the year changes, you will know that you need to restart the sequence.

  9. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-10-30)

  10. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fabio,

    If I might chime in here one thing you have to take into consideration, since it is a multiple user environment, is the possibility that one user may request a number and before he/she posts the data another user requests a number. If the first person then cancels the transaction w/o saving you will have a missing sequence number. If this is not a problem then you can ignore this post. If, however, this does cause a problem you need to delay the process of generating the number until the user Posts, Hits the Save button, or however they finish the process and then open the sequence number table, locking it, retrieve the current sequence number, generate the ProtocolNumber, save the record, write back the incremented sequence number, then finally unlock the sequence number table.

    I hope I've made the process relatively clear but post back if you need more explaination.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    fabiobarreto10 (2012-10-31)

  12. #8
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, did not understand very well use the function to create GetNextNumber function (). I did it a different way:

    Private Sub btnNumberProtocol_Click ()

    As Variant Dim get_year

    Dim join As Variant


    get_year = Year (Now ())


    get_process_Current_Year = DCount ("*", "Query_Process_Current_Year")
    Stop



    join = [get_year] & "/" & [get_process_Current_Year]
    Stop

    end Sub


    I made an appointment with records only processes the current year and concatenei the year and the number of records. Just not sure how to keep the number
    three digits. And create the digit checker.

    I made a simple bd, table, query and form, is in gmail:

    Login: windowssecretslounge

    Password: windowssecretslounge

  13. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I will try to do this tomorrow, Fabio.

  14. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-11-01)

  15. #10
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Thanks for helping.

  16. #11
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Fabio, your settings will only handle 999 process numbers per year. Is that enough? Should the code handle error situations with this?

  17. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-11-01)

  18. #12
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, this is a pretty safe number. I think not arrive in half. Thank you.

  19. #13
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Fábio,

    I left an almost complete solution in your mailbox.

  20. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-11-01)

  21. #14
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, thank you so very much. I tried to see now, but I'm falling asleep. Tomorrow morning, I'll do the rest.

    Fabio.

  22. #15
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, got a tip? I'm not able to resolve the question of the year.

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •