Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2006
    Posts
    20

    Unanswered: Help with tracking system

    I am trying to improve a particular tracking system in our office on how we handle requests for work. The current system is being done manually by logging the data into a logbook. I am trying to generate a control number that would autopopulate a given textbox in a form in MS Access.

    For example:

    - the first request for the day would be "KEZ6313001AA" ...the 2nd would be "KEZ6313002AA" ...the 3rd "KEZ6313003AA"

    - for the next day, it would be "KEZ6314001AA" ..."KEZ6314002AA" ...& so forth

    - now the 'KEZ' is a constant string so it would always begin with "KEZ"; however, the rest is more complicated to concatenate.

    - the next 3 numbers consist of the '6' from 2006 and the '313' is for the n-th day out of 365.

    - the final alphanumeric number '001AA', '002AA' are the sequence numbers ('AA' will also stay the same); this number will reset to '001' each day.

    I greatly would appreciate any assistance anyone could offer. Thank you!!!

    v/r

    aiikahn

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    First, I would disregard the KEZ and the AA since they're always the same (in other words, don't save them). You can add them on when viewing. Personally, I'd store the 6313 separately from the incrementing number. You can get the first part easily enough with Julian date function, like:

    http://support.microsoft.com/kb/209922/en-us

    The incrementing number is also fairly simple, with a DMax that finds the max number for the current date and adds one to it. I would save that as a simple number (1,2,3) and add the padded zeros using Format when displaying the result.
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    this should point you down the right track
    Code:
    Function Get_Next_Code()
        Dim DayCount As Long
        Dim FirstDay As Date
        Dim FirstBit As String
        Dim EndBit As String
        Dim YearNumber As String
        Dim TempCode As String
        Dim Check As Long
        Dim TableCount as Long
        Dim FormatCheck As String
        FirstBit = "KEZ"
        EndBit = "AA"
        YearNumber = Right(Year(Date), 1)
        FirstDay = DateSerial(Year(Date), 1, 1)
        DayCount = DateDiff("D", FirstDay, Date)
        Check = 0
        TableCount = 1
        Do Until (TableCount = 0)
            FormatCheck = Format(Check, "000")
            TempCode = FirstBit & YearNumber & DayCount & FormatCheck & EndBit
            '***************************************************
            'you need to change the tablename to the TABLENAME
            'you need to change the feildname to the FEILDNAME that holds the CODE
            TableCount = DCount("*", "tablename", "feildname= '" & TempCode & "'")
            Check = Check + 1
        Loop
            Check = Check - 1 ' NEED TO COUNT BACK ONE AS WE FOUND ONE WITH NO RECORD 
            FormatCheck = Format(Check, "000")
            Get_Next_Code = FirstBit & YearNumber & DayCount & FormatCheck & EndBit
    End Function
    just copy paste in a mobule
    haven't test it but can't see why it would not work
    Last edited by myle; 11-13-06 at 19:09.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One of the reasons I suggested the structure I did was that it would be very simple to get the next number. That method is pretty inefficient due to the loop with the DCount(), plus there are a couple of problems with it that would be found in testing.
    Paul

  5. #5
    Join Date
    Nov 2006
    Posts
    20
    Thank you, pbaldy and Myle! I will go ahead and test the code.

    I'm still somewhat a novice with Access, though; but is there any way I could incorporate that code to a button or to a form textbox to which it would generate the tracking number and autopopulate the textbox after update?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Fairly simple. Access is very event driven, so it's a matter of picking the event(s) where you want it to happen. Generally:

    Me.TextBoxName = Get_Next_Code()

    though as noted in my reply to myle, that code won't work without modification. If you're using myle's code, then I assume you've decided to store the number as a whole, instead of breaking it down as has been recommended by an Access MVP on the other site plus 2 "power users"?
    Paul

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    ADD SOME NEW LINES CODE PASSING THE ANSWER BACK TO THE FUNCTION

    As I said Not tested Just wrote off top of head

    but the idea is there .

    and yes I know inefficient due to the loop with the DCount(),

    but it only get Use onces when to get the Number


    You could take the loop out and put a something

    you could write a query that will look @ the last record in the database
    and read that in and then rebuild the string.

    or you could create a new Table that has one records in it which is the hold
    value in it
    but that will be slow to cause you would have to cal out the year,daynumber and sequence numbers


    the Dmax would be as slow as the dcount


    no matter what you do it Will be slow

    I would store the hole "KEZ6314001AA" code in the feild then it only get cal once


    there is more than one way to skin the cat

    which is the right way skin the cat
    Last edited by myle; 11-13-06 at 19:14.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Nov 2006
    Posts
    20
    It worked, Myle! I just needed to add 1 to the DayCount because it was giving me the wrong julian date for the day. Hopefully, it won't give me a problem. I greatly appreciate the help. Now, I'm just trying to understand how the do...loop works; break it down and how each line works; I've been really rusty with coding. But thank you, thank you so much - to you and pbaldy!!!

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    aiikahn that cool
    here
    cause a make it so the so not stop on the first pass
    TableCount = 1
    stop looping if it is 0
    Do Until (TableCount = 0)
    1st we format a value so its a 000 format
    if 12 it will look like 012
    FormatCheck = Format(Check, "000")
    as we broke down all the bits to form the code know join the one line
    TempCode = FirstBit & YearNumber & DayCount & FormatCheck & EndBit
    well we know have a code so lets count the number of times it in the database
    TableCount = DCount("*", "tablename", "feildname= '" & TempCode & "'")
    lets add 1 to the number for just for next number
    Check = Check + 1
    know loop back to the do and do it a again
    Loop
    Last edited by myle; 11-14-06 at 14:18.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would still have stored the values separately (as would the MVP), but at least get rid of the inefficient loop. This worked in a test:
    Code:
    Public Function GetNewNum()
      Dim intCurNum        As Integer
      Dim strSQL           As String
      Dim db               As DAO.Database
      Dim rs               As DAO.Recordset
    
      Set db = CurrentDb()
    
      'replace this with appropriate code to determine value
      'I'm lazy and hardcoded for testing
      intCurNum = 6316
    
      strSQL = "SELECT Max(Mid(FieldName,8,3)) AS MaxNum FROM TableName " _
             & "WHERE Mid(FieldName,4,4) = '" & intCurNum & "'"
    
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
      GetNewNum =  "KEZ" & intCurNum & Format(Nz(rs!MaxNum, 0) + 1, "000") & "AA"
    
      Set rs = Nothing
      Set db = Nothing
    
    End Function
    Paul

  11. #11
    Join Date
    Nov 2006
    Posts
    20
    Wow, pbaldy!!! I don't know about you but that's some hardcoding. That's pretty cool! Not that yours wasn't, Myle; and thank you so much, by the way, for explaining each line for me; it really help me understand it better. I just wasn't aware that there were many ways to do this.

    Thank you so much again!!! I will try to understand and learn more about both arenas (especially with yours, pbaldy...i liked how it was simplified; I'm just not familiar with your coding). Both of them worked. I'm just not sure which one I should use now...THANK YOU! THANK YOU! THANK YOU SO MUCH FOR EVERYTHING!!! ...and I'm sorry again for being so annoying...Take care!!!

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Ahh shucks, thanks!

    I'm of course biased, but I'd use mine. All kidding aside, the loop will cause the other method to be slower. The first record of the day, that difference may only be milliseconds. Late in the day when it has to loop through hundreds of records to find the next available number, it may become noticeable.

    At the end of the day, any method that works is fine, and you should go with what you feel comfortable with. You're the one who has to maintain it down the road.
    Paul

Posting Permissions

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