Generate next letter when certain event is true in MS Access

[th3spankst3r]

I am writing a database, which I am very new to, and have come across a problem. Maybe someone can find a way around the problem or solve the problem directly. The table that I'm working on will manage equipment repair orders (EROs). EROs are assigned a 3 letter prefix and a 2 number suffix. e.g. HDD03. When HDD99 is the last ERO used, the next ERO would be HDE00 and so on until HDZ, after which, it would use HDA00 or the first unopened equipment repair order number (they would close when complete). I made seperate fields for the prefix and suffix. However, I need some sort of code or work-around to automatically increase the prefix when the last suffix used equals 99. Also, I haven't looked into it yet because I have been stuck on this issue, however I also would need the code to increase the suffix, without it being an autonumber field, because I already have one of those. Thanks a lot.

 

[Golom]

Before we start on a solution, I have some questions. I infer that the sequence of EROs would be something like
[tt]
HDD00 - HDD99
HDE00 - HDE99
: :
HDY00 - HDY99
HDZ00 - HDZ99
[/tt]
But then you state that the next one would be HDA00.

Would that value not have been used already, assuming that the sequence starts with HDA00 - HDA99 ?

You also state "... or the first unopened equipment repair order number ...".

How do you determine that an equipment repair order number has or has not been opened?

Does this rule about unopened numbers apply only when moving from HDZ99 to HDA00 or does it apply every time you want to generate a "Next" number?

Do the first two characters (i.e "HD") change in this scheme or are always those values?

 

[NXMold]

Hmm, its fairly straightforward but requires a lot of code. Are you familiar with VB? I'll throw some ideas out here, but don't have the time to code a complete solution. You'll have to handle the transition from strings to numbers a little better, and figure out if you're incrementing just one letter or all three, etc.

dim strERO as string
dim intIncrement as integer
dim strPrefix as string

'Set starting value (How... maybe use DMax if your data sorts properly?)
strERO = "HDD22"

'Increment the two-digit number
intIncrement = right(strERO,2) + 1
if intIncrement = 100 then intIncrement = 1

'Increment the prefix character
strPrefix = mid(ERO,3,1)
if lcase(strPrefix) = "z" then
msgbox "ERROR - No more letters"
else
strPrefix = asc(chr(strPrefix)+1)
end if

'Output new incremented ERO
strERO = "HD" & strPrefix & intIncrement

 

[th3spankst3r]

Sorry for not being descriptive enough. The change from HDD to HDA was a mistake in writing the question. This code, when finished, will be used for several sets of EROs ranging from HDA to HDZ. Most instances will just include HD(D)00 to HD(D)x99, but certain situations will require HD(A)00 to HD(C)99. These are in parenthesis because they are examples. As far as the looping part of the question, if the "HD(A)00 to HD(C)99" set reached HDC99, I would want it to revert back to HDA and search for the first available ERO number, as the records would be deleted over time, as the repair was completed.

 

[TheAceMan1]

How are ya th3spankst3r . . .

I disagree with your use of looping back to an already used ERO ... deleted or not! [blue]Lets say your repair time is so good that 2, 3, or more sets have to loop back![/blue] ... [red]What then?[/red] How would you know which set your dealing with?

Just what determines a set? ... a number of repairs by the same company ... what?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[th3spankst3r]

Well, let me clarify. I work in a maintenance shop for the US Marines. We induct gear from other platoons to repair the gear. Upon induction, we write up an ERO. The ERO number is the next in the sequence for that platoon's "sub shop". Each platoon is assigned a sub shop, including a sub shop "4" for external platoons not in our command. Other sub shops include "K, "L", "U", "V", and "W". Each sub shop has a set of "prefixes" that are allowed to be used. "V", for example, has all of the prefixes from HDF to HDX, meaning the first ERO would be HDF00 and the last ERO would be HDX99. These different sub shops are currently managed by a seperate logbook per sub shop, so there's no confusion as to which set is looping back. When the last entry in the "V" logbook is HDX99, we would look at our Daily Progress Report, or DPR, (kind of like a table of what is currently in maintenance) and find the first ERO number in that sub shop that does not show up on the DPR. That would be our next ERO number.

What I am trying to do is to make this whole process electronic and automated, along with other processes. When repairs are finished, the database will delete the record for that ERO, freeing up the ERO number for when it loops back. If it became the case where more ERO numbers were needed than allotted for the sub shop, somebody with more rank than me would have to make the decision to allow HEA-HEZ EROs, at which point, the database would have to be rewritten. I'm not worried about that though, for in my time, I have never seen that happen, and if it did, there would be no problem reverting to the old system until someone smarter than me edited my database to accomodate.

Right now, my only problem is getting the ERO number to work. I need the suffix to increase from 00 to 99 and then the prefix needs to change to the next prefix that is allotted to the sub shop. After all prefixes have been used, I want it to loop back to the first unused ERO number. Thanks again for any help.

 

[NXMold]

Don't delete the record. Trust us, you'll apreciate this some day.

Add a primary key, even an autonumber if you have to, and keep all these records. Let the HDX00 field repeat, but have a unique ID field in addition.

From the sounds of things, with manual interaction with someone elses written logbooks, when you system assigns a HDX00 number it should 'propose' it to the use and give the user an opertunity to override it.

The field needs to be essentially meaningless to the structure of your database.

 

[NXMold]

In fact, you could even create an ERO lookup table with two columns, Increment and ERO. Populate the ERO column with every possible ERO number that you could ever use. The Increment column is simply an integer 1-500 or however many thier are.

Now, when you enter a new ticket, look back at the previous record (by entry date/time or by the unique autonumber index field), then find its Increment value from the ERO lookup table. Your new ticket is ERO WHERE INCREMENT = Previous INCREMENT + 1.

 

[th3spankst3r]

Alright what if I keep all of the records and add a new boolean field "Open" or "Closed"? Add an autonumber primary key (which I already added just in case), and when it needs to loop back, have it look for the first ERO number in the "Closed" status, and then create a new record with the same ERO number. Is that what you are getting at? The problem still persists. I don't know how to get it to loop at all.

 

[Golom]

Were I doing this I would probably set up a table that contained all possible ERO numbers from HDA00 to HDZ99 (2,600 of them) with a flag for each indicating "Open" or "Closed". For example
[tt]
EROCodes
EROCode Open
HDA00 False
HDA01 True
: :
HDZ99 False
[/tt]

Second I would have a table that specifies the range of ERO numbers available to each sub-shop. Something like
[tt]
SubShopCodes
SubShop StartCode EndCode
V HDF00 HDX99
[blue] etc.[/blue]
[/tt]

Then, when you want a new ERO for a particular Sub-Shop

Select MIN(EROCode] As {NextERO]

From EROCodes As E, SubShopCodes As SS

WHERE E.EROCode BETWEEN SS.StartCode AND SS.EndCode
  AND E.Open = FALSE
  AND SS.SubShop = [Enter Sub Shop Code]

You would probably supply the Sub-Shop code from a form control rather than the Input Box version that I have shown.

You would then need to write code to manage the "Open" field in EROCodes whenever the status changes.

If anything is challenging about this it is the maintenance of the SubShopCodes table. You need to write some code (either VB or SQL) to be sure that none of the Start-End pairs overlap.

There are techniques for cycling through codes to get a "next" one but they involve interactive steps to generate the "Next" code and then check if it is actually available. By building this rather trivial table you combine both steps into some fairly simple SQL.

 

[th3spankst3r]

Hmm by the time I read your post, I had figured out a way to do it using bits and pieces of others' suggestions, however, what I wrote does not work for some reason. I'm sure it is a simple fix though, because it is all simple code, however there's a ton of it. Since this is already written, if you could tell me how to post it here, like you did, and then help with what is wrong, I would rather fix what I have. If that's not possible, I'll try your idea.

 

[Golom]

Sure it's possible. Just use the
[ignore]

[/ignore]

[blue]Paste your code here[/blue]

[ignore]

[/ignore]

tags to get the formatting.

When you post make sure that you

- Explain what is happening. Just "doesn't work" isn't very illuminating.

- If SQL is involved, post the SQL view rather than trying to describe what you see in the query designer.

 

[th3spankst3r]

Public Function CalculateNextERONo()
    Dim LastEntryNumber As String
    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
    
    'Return the Entry Number of the last record in the Sub Shop selected as LastEntryNumber.
    If Forms![Induct Gear]![Sub Shop] = "4" Then
        LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = 4")
    Else
        If Forms![Induct Gear]![Sub Shop] = "K" Then
            LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = K")
        Else
            If Forms![Induct Gear]![Sub Shop] = "L" Then
                LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = L")
            Else
                If Forms![Induct Gear]![Sub Shop] = "M" Then
                    LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = M")
                Else
                    If Forms![Induct Gear]![Sub Shop] = "U" Then
                        LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = U")
                    Else
                        If Forms![Induct Gear]![Sub Shop] = "V" Then
                            LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = V")
                        Else
                            LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = W")
                        End If
                    End If
                End If
            End If
        End If
    End If
    
    'Return the Entry Number of the first record in the Sub Shop with a "Closed" Status as FirstClosedEntryNumber.
    If Forms![Induct Gear]![Sub Shop] = "4" Then
        FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
    Else
        If Forms![Induct Gear]![Sub Shop] = "K" Then
            FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
        Else
            If Forms![Induct Gear]![Sub Shop] = "L" Then
                FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
            Else
                If Forms![Induct Gear]![Sub Shop] = "M" Then
                    FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
                Else
                    If Forms![Induct Gear]![Sub Shop] = "U" Then
                        FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
                    Else
                        If Forms![Induct Gear]![Sub Shop] = "V" Then
                            FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
                        Else
                            FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", "Sub Shop = 4" & "Status = Closed")
                        End If
                    End If
                End If
            End If
        End If
    End If
    
    'Return the Prefix of the record who's Entry Number matches LastEntryNumber as OldPrefix.
    OldPrefix = DLookup("Prefix", "In Maintenance", "Entry Number = LastEntryNumber")
    
    'Return the Suffix of the record who's Entry Number matches LastEntryNumber as OldSuffix.
    OldSuffix = DLookup("Suffix", "In Maintenance", "Entry Number = LastEntryNumber")
    
    'Return the Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
    NextClosedSuffix = DLookup("Suffix", "In Maintenance", "Entry Number = FirstClosedEntryNumber")
    
    'If OldSuffix is 100, Reset to NextClosedSuffix and transition to the next prefix in the Sub Shop. Also defines NewPrefix.
    If OldSuffix < 100 Then
        NewSuffix = OldSuffix + 1
    Else
        If OldPrefix = "HDA" Then
            NewPrefix = OldPrefix
            NewSuffix = NextClosedSuffix
        Else
            If OldPrefix = "HDB" Then
                NewPrefix = OldPrefix
                NewSuffix = NextClosedSuffix
            Else
                If OldPrefix = "HDC" Then
                    NewPrefix = OldPrefix
                    NewSuffix = NextClosedSuffix
                Else
                    If OldPrefix = "HDD" Then
                        NewPrefix = "HDE"
                        NewSuffix = NextClosedSuffix
                    Else
                        If OldPrefix = "HDE" Then
                            NewPrefix = "HDD"
                            NewSuffix = NextClosedSuffix
                        Else
                            If OldPrefix = "HDF" Then
                                NewPrefix = "HDG"
                                NewSuffix = NextClosedSuffix
                            Else
                                If OldPrefix = "HDG" Then
                                    NewPrefix = "HDH"
                                    NewSuffix = NextClosedSuffix
                                Else
                                    If OldPrefix = "HDH" Then
                                        NewPrefix = "HDI"
                                        NewSuffix = NextClosedSuffix
                                    Else
                                        If OldPrefix = "HDI" Then
                                            NewPrefix = "HDJ"
                                            NewSuffix = NextClosedSuffix
                                        Else
                                            If OldPrefix = "HDJ" Then
                                                NewPrefix = "HDK"
                                                NewSuffix = NextClosedSuffix
                                            Else
                                                If OldPrefix = "HDK" Then
                                                    NewPrefix = "HDL"
                                                    NewSuffix = NextClosedSuffix
                                                Else
                                                    If OldPrefix = "HDL" Then
                                                        NewPrefix = "HDM"
                                                        NewSuffix = NextClosedSuffix
                                                    Else
                                                        If OldPrefix = "HDM" Then
                                                            NewPrefix = "HDN"
                                                            NewSuffix = NextClosedSuffix
                                                        Else
                                                            If OldPrefix = "HDN" Then
                                                                NewPrefix = "HDO"
                                                                NewSuffix = NextClosedSuffix
                                                            Else
                                                                If OldPrefix = "HDO" Then
                                                                    NewPrefix = "HDP"
                                                                    NewSuffix = NextClosedSuffix
                                                                Else
                                                                    If OldPrefix = "HDP" Then
                                                                        NewPrefix = "HDQ"
                                                                        NewSuffix = NextClosedSuffix
                                                                    Else
                                                                        If OldPrefix = "HDQ" Then
                                                                            NewPrefix = "HDR"
                                                                            NewSuffix = NextClosedSuffix
                                                                        Else
                                                                            If OldPrefix = "HDR" Then
                                                                                NewPrefix = "HDS"
                                                                                NewSuffix = NextClosedSuffix
                                                                            Else
                                                                                If OldPrefix = "HDS" Then
                                                                                    NewPrefix = "HDT"
                                                                                    NewSuffix = NextClosedSuffix
                                                                                Else
                                                                                    If OldPrefix = "HDT" Then
                                                                                        NewPrefix = "HDU"
                                                                                        NewSuffix = NextClosedSuffix
                                                                                    Else
                                                                                        If OldPrefix = "HDU" Then
                                                                                            NewPrefix = "HDV"
                                                                                            NewSuffix = NextClosedSuffix
                                                                                        Else
                                                                                            If OldPrefix = "HDV" Then
                                                                                                NewPrefix = "HDW"
                                                                                                NewSuffix = NextClosedSuffix
                                                                                            Else
                                                                                                If OldPrefix = "HDW" Then
                                                                                                    NewPrefix = "HDX"
                                                                                                    NewSuffix = NextClosedSuffix
                                                                                                Else
                                                                                                    If OldPrefix = "HDX" Then
                                                                                                        NewPrefix = "HDF"
                                                                                                        NewSuffix = NextClosedSuffix
                                                                                                    Else
                                                                                                        If OldPrefix = "HDY" Then
                                                                                                            NewPrefix = OldPrefix
                                                                                                            NewSuffix = NextClosedSuffix
                                                                                                        Else
                                                                                                            If OldPrefix = "HDZ" Then
                                                                                                                NewPrefix = OldPrefix
                                                                                                                NewSuffix = NextClosedSuffix
                                                                                                            End If
                                                                                                        End If
                                                                                                    End If
                                                                                                End If
                                                                                            End If
                                                                                        End If
                                                                                    End If
                                                                                End If
                                                                            End If
                                                                        End If
                                                                    End If
                                                                End If
                                                            End If
                                                        End If
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
    
    'Combine NewPrefix and NewSuffix to make NextERONo
    NextERONo = NewPrefix & NewSuffix
    
    'Set InductGear_ERONumber to NextERONo
    Forms![Induct Gear]![ERO Number] = NextERONo
End Function

When the function above (sorry for the length) is called by the macro RunUpdateInductGearForm Function which is run at the AfterUpdate function of the Forms![Induct Gear]![Sub Shop] combo box, I get an error:

Run-time error '3075':

Syntax error (missing operator) in query expression 'Max(EntryNumber)'.

When debugging, the error shows up in the line:

LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = V")

 

[Golom]

You need a statement like

LastEntryNumber = DMax("Entry Number", "In Maintenance", "[red][Sub Shop] = 'V'[/red]")

When you are comparing a field (Sub Shop, in this case to a character (V) then the character must appear in quotes. Because "Sub Shop" contains a space it must appear inside square brackets.

I have done some tinkering with your code that may simplify it somewhat

Public Function CalculateNextERONo()
    Dim LastEntryNumber             As String
    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

    'Return the Entry Number of the last record in the Sub Shop selected as LastEntryNumber.
    '[red]This statement will do the same thing as your nested IFs below.[/red]
    '[red]Sub Shop is a field name containing a space so it must be in brackets.[/red]
    '[red]When comparing a field to a character string, you need to enclose the character string in single quotes.[/red]
    LastEntryNumber = DMax("Entry Number", "In Maintenance", _
                            "[Sub Shop] = '" & Forms![Induct Gear]![Sub Shop] & "'")

    'Return the Entry Number of the first record in the Sub Shop with a "Closed" Status as FirstClosedEntryNumber.
    '[red]When comparing a field to a character string, you need to enclose the character string in single quotes.[/red]
    '[red]You want to use 'AND' ... not '&'. You are trying to compute a logical condition ... not concatenate fields.[/red]
    FirstClosedEntryNumber = DMin("Entry Number", "In Maintenance", _
                  "[Sub Shop] = '" & Forms![Induct Gear]![Sub Shop] & "' AND Status = 'Closed'")


    '[red]The next three statements with the variable names inside the quotes, will attempt[/red]
    '[red]to compare the field with the character string (LastEntryNumber for example)[/red]
    '[red]rather than the value of that variable.[/red]
    '[red]I have moved them outside the quotes.[/red]

    'Return the Prefix of the record who's Entry Number matches LastEntryNumber as OldPrefix.
    OldPrefix = DLookup("Prefix", "In Maintenance", "Entry Number = " & LastEntryNumber)

    'Return the Suffix of the record who's Entry Number matches LastEntryNumber as OldSuffix.
    OldSuffix = DLookup("Suffix", "In Maintenance", "Entry Number = " & LastEntryNumber)

    'Return the Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
    NextClosedSuffix = DLookup("Suffix", "In Maintenance", "Entry Number = " & FirstClosedEntryNumber)

    'If OldSuffix is 100, Reset to NextClosedSuffix and transition to the next prefix in the Sub Shop. Also defines NewPrefix.

    '[red]This block seems to have some strange things. Some of them may be typos or they may be[/red]
    '[red]what you intended. For example [/red]
    '[red]If OldPrefix = "HDE" Then[/red]
    '[red]   NewPrefix = "HDD"[/red]
    '
    '[red]Should it be NewPrefix = "HDF"  ?[/red]
    '
    '[red]Similarly[/red]
    '[red]If OldPrefix = "HDX" Then[/red]
    '[red]   NewPrefix = "HDF"[/red]
    '
    '[red]Should it be NewPrefix = "HDY"  ?[/red]
    '
    '[red]If you just want to move through the letters of the alphabet then[/red]
    '[red](... and assuming the above were just typos.)[/red]

    If OldSuffix < 99 Then                            ' Note 99 ... not 100
        NewSuffix = OldSuffix + 1
    Else
        Select Case OldPrefix
            '[red]From your code it appears that for A, B, C, Y and Z you want to keep the same letter.[/red]
            Case "HDA", "HDB", "HDC", "HDY", "HDZ"
                NewPrefix = OldPrefix
            Case Else
                NewPrefix = "HD" & Chr(Asc(Right(OldPrefix, 1)) + 1)
        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
End Function

BTW: This is a function but you have not set the return value (CalculateNextERONo). That may not be important since you are setting a form field.

 

[th3spankst3r]

a couple of questions about your solution. For this section...

'Return the Entry Number of the last record in the Sub Shop selected as LastEntryNumber.
    'This statement will do the same thing as your nested IFs below.
    'Sub Shop is a field name containing a space so it must be in brackets.
    'When comparing a field to a character string, you need to enclose the character string in single quotes.
    LastEntryNumber = DMax("Entry Number", "In Maintenance", _
                            "[Sub Shop] = '" & Forms![Induct Gear]![Sub Shop] & "'")

you seem to have put random apostrophe's around what doesn't look to be a character string that makes sense there. When I read your explanation, I thought maybe it was supposed to look like this...

'Return the Entry Number of the last record in the Sub Shop selected as LastEntryNumber.
    'This statement will do the same thing as your nested IFs below.
    'Sub Shop is a field name containing a space so it must be in brackets.
    'When comparing a field to a character string, you need to enclose the character string in single quotes.
    LastEntryNumber = DMax("Entry Number", "In Maintenance", _
                            "[Sub Shop] = 'M'")

that's question 1. question 2 has to deal with my "typos". they were actually not typo's. those places where it seemingly went to the wrong letter, is where the subshop ends, so I was having it reset back to the beginning of the subshop. Sometimes it went back one letter, and sometimes it went back 10 letters or whatnot. either way, those weren't typos. alright the question is, why did you change

If OldSuffix < 100 Then

to

If OldSuffix < 99 Then

?
that doesn't make sense if I want a value of 99 to make the statement true also. It will never have a decimal number anyway, so if it's less than 100, that would be 99, 98, 97, and so forth. If the statement is less than 99, then it would change right when it hit 99, but not use 99 as a value. correct me if i'm wrong.

NewPrefix = "HD" & Chr(Asc(Right(OldPrefix, 1)) + 1)

This line looks much more efficient than all of my nested if's, but how would you read that in english? I haven't tried it yet, so I don't know if it does the same thing mine does, but it has been suggested earlier in this topic, but I'm afraid it will not work. To me, this says that if OldPrefix is not one of the ones that remains the same after 99, then it will keep increasing every time it gets to 99. However, I cannot have it increase into another sub shop's ERO numbers. So it would have to stop increasing before it got to the next sub shop, and search for FirstClosedEntryNumber.

Other than that, I still have to look up the meaning of the word "concatenate" that you used, but I think all will end up working. I greatly appreciate your help. I know what forum I'll post to if I ever need help again (which I'm sure I will by the end of this database).

 

[th3spankst3r]

I ran the database with your code and tried to execute the function and it gave me the same error...

Run-time error '3075':

Syntax error (missing operator) in query expression 'Max(EntryNumber)'.

in the line...

LastEntryNumber = DMax("Entry Number", "In Maintenance", _
                            "[Sub Shop] = '" & Forms![Induct Gear]![Sub Shop] & "'")

I don't know what operator is missing, but from what i read about DMax, that's how it is supposed to work.

 

[th3spankst3r]

Alright I think I fixed that problem. Because "Entry Number" is referring to a field's value on my table, it must be in brackets.

now I get the error:

Invalid use of Null

in your code at line

FirstClosedEntryNumber = DMin("[Entry Number]", "In Maintenance", _
                  "[Sub Shop] = '" & Forms![Induct Gear]![Sub Shop] & "' AND Status = 'Closed'")

I edited my code, and came up with this...

If Forms![Induct Gear]![Sub Shop] = "4" Then
        FirstClosedEntryNumber = DMin("[Entry Number]", "In Maintenance", "[Sub Shop] = '4'" And "[Status] = 'Closed'")

and got the error message:

Type mismatch

on the same line. Any ideas?

 

[Golom]

The 99 Vs. 100 issue

If you use

If OldSuffix < 100 Then
   NewSuffix = OldSuffix + 1

Then, when OldSuffix = 99, the statement will evaluate to TRUE and
NewSuffix will be computed as 99 + 1 = 100 and that is not
a legal value for the suffix.

Conversly, If you use

If OldSuffix < 99 Then
   NewSuffix = OldSuffix + 1

when OldSuffix = 98 then the statement is TRUE and NewSuffix is computed as 98 + 1 = 99 so 99 does indeed get used.

when OldSuffix = 99 then the code will pick up "NextClosedSuffix"
as its value and will not increment to 100.

Quotes and Brackets
You had constructs of the form

If Forms![Induct Gear]![Sub Shop] = "4" Then
   LastEntryNumber = DMax("Entry Number", "In Maintenance", "Sub Shop = 4")
[blue]etc.[/blue]

That is, you were testing the value found in
Forms![Induct Gear]![Sub Shop] and then creating a DMax
call incorporating the same ... now hard-coded, value
that you found. I just simplified it to

LastEntryNumber = DMax("Entry Number", "In Maintenance", _
"[Sub Shop] = '" & Forms![Induct Gear]![Sub Shop] & "'")

which, after the value in Forms![Induct Gear]![Sub Shop] is resolved, would reduce to

LastEntryNumber = DMax("Entry Number", "In Maintenance", _
"[Sub Shop] = '4'")

Given the spaces in the names, "Entry Number", "In Maintenance" and "Sub Shop", you should probably have
brackets around all of them as in

LastEntryNumber = DMax("[red][[/red]Entry Number[red]][/red]", "[red][[/red]In Maintenance[red]][/red]", _
"[red][[/red]Sub Shop[red]][/red] = '" & Forms![Induct Gear]![Sub Shop] & "'")

That applies to all your DMin, DMax and DLookup calls.

NULLs
The functions DMax, DMin and DLookup will return NULL if the
underlying SQL returns no records. The variables to which
you are assigning the retrieved values are declared as Strings and a string cannot accept NULL as a value. Change
the type to Variant and, after issuing the DMin, DMax or DLookup call
test for a NULL in the returned value. Since I don't know
the structure of these tables I'm not really sure about
what action should be taken when a NULL is returned.

Different 'Next' Values
Since those were not typos then you can just expand the
SELECT CASE

Select Case OldPrefix
    Case "HDA", "HDB", "HDC", "HDY", "HDZ"
         NewPrefix = OldPrefix
    Case "HDE"
         NewPrefix = "HDD"
    Case "HDX"
         NewPrefix = "HDF"
    Case Else
         NewPrefix = "HD" & Chr(Asc(Right(OldPrefix, 1)) + 1)
End Select

and whatever other exceptions you have.

Type Mismatch
Not really sure about that one. The message is usually
associated with attempting a comparison between a field
and a value where the types don't match. Check the
data types for [Sub Shop] and [Status] in the table to
be sure that they are both Text fields.

 

[Golom]

Upon further reflection, the approach that you are adopting here may cost you much grief later.

The "rules" about which ERO ranges are associated with which sub-shops are enshrined in code. That means that any change to those rules will require that you modify the code. That in turn, involves the risk of creating overlapping use of ERO codes or of skipping a set of ERO codes so that they are unused.

It also makes answering questions like
- What ERO codes are used by Sub-Shop "V"?
- What ERO codes are currently not in use?

difficult to answer.

Adding a new Sub-Shop and associated ERO codes becomes a significant programming task.

You may benefit from thinking about making that information resident in a table (after all, it's just data) rather than hard-coding it in VBA.

 

[th3spankst3r]

Can I do that without needing to mess with my current data? I'm not sure I know how to do all of that. There may be other problems as well. I don't plan on this database being editable by everyone. I'm the only one around here that knows any kind of programming or anything about databases, for that matter. If I leave, and they change those rules, you're right. My database will be useless. However, in order to make it so that the "average user" could easily update those "rules", I forsee my workload increasing 3 fold at least. As it is, I have little time. I am out of the Marines in Aug and barely starting this database. I'd like to get it functional before I leave. Maybe all I will have to leave behind is instructions on how to enter raw data.