Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87

    Unanswered: help with logic!

    argh! I found a HUGE problem with my database's functionality. It should have been spotted when planning the database, but I overlooked it. Now I need a solution.

    My database manages Equipment Repair Orders (EROs), both open and closed. EROs are assigned a number that has a 3-letter prefix and a 2-integer suffix, such as HDA32. The criteria for the assignment of the ERO number is that it must be the next available ERO number in what's called a "Sub Shop". Basically, what the Marines did was take the letters A-Z, assigned each letter 00-99 for ERO numbers, and then split it all up into groups called Sub Shops, so that they could be sorted by who owns the equipment or what's wrong with the equipment. For instance, HDA00-HDA99 has its own Sub Shop: Sub Shop 4. There are other Sub Shops, and don't ask why they're named the way they are because I don't know. Sub Shop V has HDF00-HDX99 and is the largest Sub Shop. That means that there's more ERO numbers available to that organization, because they bring in the most equipment. After HDF99 is reached, the next opened ERO would be numbered HDG00 and continue on to HDG99 and then go to HDH00, and so forth.

    When HDX99 is reached, it "resets" to the beginning of the sub shop with certain conditions. Lets say, hypothetically that HDF00 is still open by the time HDX99 is opened, because we're still fixing the equipment or whatnot. The procedure, and what the database does, is skips over that one and finds the FIRST closed ERO and uses that ERO number. So if HDF05 was the first one with a "closed" status, the next ERO number after HDX99 would be HDF05.

    Now, my database already does all of that. I have a function, that will be posted at the end of this post, that does all of that. The problem is that I didn't anticipate what happens next. The way I programmed it, each ERO has a unique number (my primary key) called "Entry Number". Until the function resets to the beginning of the sub shop, the way it calculates the next ERO number is that it finds the maximum "Entry Number" in that Sub Shop, and uses "ERO Prefix" & "ERO Suffix" + 1 for the ERO Number.

    After the function resets to the beginning of the Sub Shop, it correctly finds the first closed ERO Number to use as the new entry's ERO Number. The problem lies with the very next ERO.

    Lets use Sub Shop V again to give an example. If HDF00-HDX99 are used, and a new ERO is opened, it would take the first closed ERO Number, lets say HDF05. But then it reverts to the "ERO Prefix" & "ERO Suffix" + 1 part of the function, which would make the next ERO HDF06, whether it's opened or closed. I need it to search for the next closed ERO number, in case HDF06 was still open. I thought it would be a simple fix, I could just use the same lines as when it resets, which would find the record with the LOWEST "Entry Number" with a status of "Closed". The problem is that since it keeps all the old records, the lowest one would still find HDF05. I need a solution to this, but don't want to rethink the entire function, because quite frankly, I don't know how else I would do it.

    The function is below since this post is too long for the forum.

    Thanks for any help. I hope I made it clear enough. If you have any questions, I'll try to explain as much as possible. It's hard to explain Marine Corps stuff to civilians because the Marine Corps often doesn't make any sense, but I'm not the one making the decisions, so all I can do is work with what they give me.

  2. #2
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Code:
    Public Function CalculateNextERONo()
        Dim LastEntryNumber As Long
        Dim FirstClosedEntryNumber As String
        Dim NextClosedSuffix As String
        Dim OldPrefix As String
        Dim OldSuffix As String
        Dim NewPrefix As String
        Dim NewSuffix As String
        Dim NextERONo As String
     
        Select Case Forms![Induct Gear]![Category Code].Value
                Case "K"
                     LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'K'")
                Case "S"
                     LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
                Case Else
                    Select Case Forms![Induct Gear]![Owning Organization].Value
                            Case "CLR-17 COMM CO XFA"
                                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'L'")
                            Case "CLR-17 COMM CO XFB"
                                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'U'")
                            Case "CLR-17 COMM CO XFC"
                                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'V'")
                            Case "CLR-17 COMM CO TECHCON"
                                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'W'")
                            Case "CLR-17 COMM CO MAINT"
                                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4' AND [Owning Organization] = 'CLR-17 COMM CO MAINT'")
                            Case "EXTERNAL ORGANIZATION"
                                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4' AND [Owning Organization] = 'EXTERNAL ORGANIZATION'")
                    End Select
        End Select
     
        OldPrefix = DLookup("[ERO Prefix]", "[In Maintenance]", "[Entry Number] = " & LastEntryNumber)
    
        OldSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & LastEntryNumber)
     
        If OldSuffix < 99 Then
            NewSuffix = OldSuffix + 1
            NewSuffix = Format(NewSuffix, "00")
            NewPrefix = OldPrefix
        Else
            'Return the Entry Number of the first record in the Sub Shop with a "Closed" Status as FirstClosedEntryNumber.
        Select Case Forms![Induct Gear]![Category Code].Value
                Case "K"
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'K'")
                Case "S"
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = '4'")
                Case Else
                    Select Case Forms![Induct Gear]![Owning Organization].Value
                            Case "CLR-17 COMM CO MAINT"
                                FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = '4'")
                                'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
                                Select Case OldPrefix
                                        Case "HDE", "HDX", "HDD", "HDF", "HDG", "HDH", "HDI", "HDJ", "HDK", "HDL", "HDM", "HDN", "HDO", "HDP", "HDQ", "HDR", "HDS", "HDT", "HDU", "HDV", "HDW", "HDX"
                                            NextClosedSuffix = "00"
                                        Case Else
                                            NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
                                End Select
                            Case "CLR-17 COMM CO XFA"
                                FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'L'")
                                'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
                                Select Case OldPrefix
                                        Case "HDE", "HDX", "HDD", "HDF", "HDG", "HDH", "HDI", "HDJ", "HDK", "HDL", "HDM", "HDN", "HDO", "HDP", "HDQ", "HDR", "HDS", "HDT", "HDU", "HDV", "HDW", "HDX"
                                            NextClosedSuffix = "00"
                                        Case Else
                                            NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
                                End Select
                            Case "CLR-17 COMM CO XFB"
                                FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'U'")
                                'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
                                Select Case OldPrefix
                                        Case "HDE", "HDX", "HDD", "HDF", "HDG", "HDH", "HDI", "HDJ", "HDK", "HDL", "HDM", "HDN", "HDO", "HDP", "HDQ", "HDR", "HDS", "HDT", "HDU", "HDV", "HDW", "HDX"
                                            NextClosedSuffix = "00"
                                        Case Else
                                            NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
                                End Select
                            Case "CLR-17 COMM CO XFC"
                                FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'V'")
                                'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
                                Select Case OldPrefix
                                        Case "HDE", "HDX", "HDD", "HDF", "HDG", "HDH", "HDI", "HDJ", "HDK", "HDL", "HDM", "HDN", "HDO", "HDP", "HDQ", "HDR", "HDS", "HDT", "HDU", "HDV", "HDW", "HDX"
                                            NextClosedSuffix = "00"
                                        Case Else
                                            NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
                                End Select
                            Case "CLR-17 COMM CO TECHCON"
                                FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'W'")
                                'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
                                Select Case OldPrefix
                                        Case "HDE", "HDX", "HDD", "HDF", "HDG", "HDH", "HDI", "HDJ", "HDK", "HDL", "HDM", "HDN", "HDO", "HDP", "HDQ", "HDR", "HDS", "HDT", "HDU", "HDV", "HDW", "HDX"
                                            NextClosedSuffix = "00"
                                        Case Else
                                            NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
                                End Select
                            Case Else
                                FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = '4'")
                                'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
                                Select Case OldPrefix
                                        Case "HDE", "HDX", "HDD", "HDF", "HDG", "HDH", "HDI", "HDJ", "HDK", "HDL", "HDM", "HDN", "HDO", "HDP", "HDQ", "HDR", "HDS", "HDT", "HDU", "HDV", "HDW", "HDX"
                                            NextClosedSuffix = "00"
                                        Case Else
                                            NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
                                End Select
                    End Select
        End Select
            Select Case OldPrefix
                Case "HDA"
                    NewPrefix = OldPrefix
                Case "HDB"
                    NewPrefix = OldPrefix
                Case "HDC"
                    NewPrefix = OldPrefix
                Case "HDY"
                    NewPrefix = OldPrefix
                Case "HDZ"
                    NewPrefix = OldPrefix
                Case "HDE"
                    NewPrefix = "HDD"
                Case "HDX"
                    NewPrefix = "HDF"
                Case "HDD"
                    NewPrefix = "HDE"
                Case "HDF"
                    NewPrefix = "HDG"
                Case "HDG"
                    NewPrefix = "HDH"
                Case "HDH"
                    NewPrefix = "HDI"
                Case "HDI"
                    NewPrefix = "HDJ"
                Case "HDJ"
                    NewPrefix = "HDK"
                Case "HDK"
                    NewPrefix = "HDL"
                Case "HDL"
                    NewPrefix = "HDM"
                Case "HDM"
                    NewPrefix = "HDN"
                Case "HDN"
                    NewPrefix = "HDO"
                Case "HDO"
                    NewPrefix = "HDP"
                Case "HDP"
                    NewPrefix = "HDQ"
                Case "HDQ"
                    NewPrefix = "HDR"
                Case "HDR"
                    NewPrefix = "HDS"
                Case "HDS"
                    NewPrefix = "HDT"
                Case "HDT"
                    NewPrefix = "HDU"
                Case "HDU"
                    NewPrefix = "HDV"
                Case "HDV"
                    NewPrefix = "HDW"
                Case Else
                    NewPrefix = "HDX"
            End Select
            NewSuffix = NextClosedSuffix
        End If
     
    'Combine NewPrefix and NewSuffix to make NextERONo
        NextERONo = NewPrefix & NewSuffix
     
    'Set InductGear_ERONumber to NextERONo
        Forms![Induct Gear]![ERO Number] = NextERONo
        
    'Set ERO Prefix and ERO Suffix for current record
        Forms![Induct Gear]![InductGear_EROPrefixBox] = NewPrefix
        Forms![Induct Gear]![InductGear_EROSuffixBox] = NewSuffix
        
    End Function

  3. #3
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I even had to cut out some of the notes.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd do this with tables as opposed to trying to define the relationships between a code and a prefix programmatically...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm still trying to understand the whole concept, but you could probably simplify the function. For instance the part that computes the NewPrefix could be replaced by:
    Code:
            Select Case OldPrefix
                Case "HDA", "HDB", "HDC", "HDY", "HDZ"
                    NewPrefix = OldPrefix
                Case "HDE"
                    NewPrefix = "HDD"
                Case "HDX"
                    NewPrefix = "HDF"         
                Case "HDD", "HDF" To "HDV"
                    NewPrefix = "HD" & UCase(Chr(Asc(Right(OldPrefix, 1)) + 1))
                Case Else
                    NewPrefix = "HDX"
            End Select
    Have a nice day!

  7. #7
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Sinndho, thank you for the simplified code. As for the other suggestions, I don't really know how I would implement that, given my task. I don't know what information to put into seperate tables. I'd like to be able to put the Sub Shops and such in a table, that could be controlled with a form, because the Marine Corps can change the ERO matrices without notice, and I may not be around to reprogram the database. I'd like for someone to be able to change the ERO matrices without me, but I have no clue how any of that would be done. Putting data into the table, such as +1 doesn't seem too beneficial. I agree with the article that you posted, however in this application, an ERO number would never be anything but +1. I don't know how to split all of this up into tables effectively.

    I've asked other Marines in my shop to read what I wrote and if a civilian would get the jist of how the ERO matrices work. They started reading it and said that it would be too confusing. I'm hoping they're wrong, but if ya'll don't understand completely, maybe I can try explaining it another way.

  8. #8
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I hope I am not out of line in suggesting a solution that might be an oversimplification of the problem.
    As I understand it, you put codes for old "closed" orders back in with the open orders after you run out of codes to use, and you don't delete the old orders.
    Can you base your search for the next available closed code on a query that filters out all open order codes? The query, maybe one you are already using, would filter on whatever field identifies "open" and "closed".
    Jerry

  9. #9
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Maybe I can use a query. If I could have the code select the next record in the query, I might be able to have the parameters for the query bring up only closed records in that Sub Shop that are "closed", and then assign them in the order of the records in the query. I'm afraid it would still be based on the Entry Number (PK) though, and the query would inevitably bring up sporadic Entry Numbers, due to filtering it by Sub Shop. The query might look like this:
    Code:
    EntryNumber            Description         Status                Sub Shop
         1                 ERO8                Closed                       L
         7                 ERO12               Closed                       L
         15                ERO60               Closed                       L
    I'm not good at this, but if it were sequential Entry Numbers, I could just add one to the last query record used. But since they're not sequential, is there a "next" method I could use?

    The problem is that the query won't ever have fewer records, because the records in the query will never have their status changed from "closed" to "open", but rather, a new record will be made that has an open status. It must be this way because we need to keep maintenance history for all of the EROs that have been opened at one point in time.
    Last edited by th3spankst3r; 04-20-10 at 11:37.

  10. #10
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Are open ERO descriptions unique within the table or can the same ERO descr. be assigned to more than one sub shop?

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    After calling the function CalculateNextERONo, can't you simply (if I dare say so!) check whether the returned value is in use or not, and if it is in use call the function again (with the result of the previous call as parameter? You could include this process in a loop (go on until the value returned by the function is not in use), or possibly write a recursive function.

    Testing if a ERONumber is in use could work with a DLookup function based on a SELECT Max or SELECT Top(1) query, or possibly with a DMax function based directly on the table.
    Have a nice day!

  12. #12
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    They're unique to the Sub Shop. In my example, ERO12, or any of the others, will never appear any any Sub Shop other than L.

  13. #13
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Quote Originally Posted by Sinndho View Post
    After calling the function CalculateNextERONo, can't you simply (if I dare say so!) check whether the returned value is in use or not, and if it is in use call the function again (with the result of the previous call as parameter? You could include this process in a loop (go on until the value returned by the function is not in use), or possibly write a recursive function.

    Testing if a ERONumber is in use could work with a DLookup function based on a SELECT Max or SELECT Top(1) query, or possibly with a DMax function based directly on the table.
    So something like "If NextERONo is found in ThatSubShopsOpenEROsQuery Then CalculateNextERONo(NextERONo)" and then somewhere put "NOT NextERONo"?

    I hope that's what you mean. Obviously that would be written in code, which I'll have to figure out, but I think that's the jist of what you're getting at.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's it. I have a copy of an ancient version of your database, so I don't know whether the following code is pertinent or not, but to determine if an ERO number is closed or not:
    Code:
    IsClosed = DCount("[Entry Number]", "[In Maintenance]", "[ERO Number] = '" & ERONumber & "' AND [Open/Closed] = 'Open'") = 0
    So you could try:
    Code:
    'If OldSuffix is 100, Reset to NextClosedSuffix and transition to the next ERO Prefix in the Sub Shop. Also defines NewPrefix.
     
    ' SinnDHo: 2010-04-20
    ' -------------------
    '
        If OldSuffix < 99 Then
            If DCount("[Entry Number]", "[In Maintenance]", "[ERO Number] = '" & Format(OldSuffix + 1, "00") & OldPrefix & "' AND [Open/Closed] = 'Open'") > 0 Then
                OldSuffix = 100
            End If
        End If
    ' -------------------
        If OldSuffix < 99 Then
            NewSuffix = OldSuffix + 1
            NewSuffix = Format(NewSuffix, "00")
            NewPrefix = OldPrefix
        Else
    ' . . ., etc.
    Have a nice day!

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There was an error in the proposed code (I inverted OldPrefix and OldSuffix). Moreover, there is a better solution:
    Code:
    ' SinnDHo: 2010-04-20
    ' -------------------
    '
    '    If OldSuffix < 99 Then
    ' --> Error:
    '        If DCount("[Entry Number]", "[In Maintenance]", "[ERO Number] = '" & Format(OldSuffix + 1, "00") & OldPrefix & "' AND [Open/Closed] = 'Open'") > 0 Then
    ' --> Should be:
    '        If DCount("[Entry Number]", "[In Maintenance]", "[ERO Number] = '" & OldPrefix & Format(OldSuffix + 1, "00") & "' AND [Open/Closed] = 'Open'") > 0 Then
    '
    '            OldSuffix = 100
    '        End If
    '    End If
    ' -------------------
    ' Probably better:
    '
        Dim i As Integer
        For i = OldSuffix + 1 To 99
            OldSuffix = i
            If DCount("[Entry Number]", "[In Maintenance]", "[ERO Number] = '" & OldPrefix & Format(OldSuffix, "00") & "' AND [Open/Closed] = 'Open'") = 0 Then Exit For
        Next i
        If OldSuffix < 99 Then
    '        NewSuffix = OldSuffix + 1
    '        NewSuffix = Format(NewSuffix, "00")
            NewSuffix = Format(OldSuffix, "00")
    ' -------------------
            NewPrefix = OldPrefix
        Else
    Have a nice day!

Posting Permissions

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