Function Help

**th3spankst3r** · 03-19-2010, 04:36 PM

I am very new to VBA and have done little other programming in the past. I have created a couple of Access databases, but was not required to us VBA for them, so I am having trouble. Right now, in particular, I am having trouble with a function of mine that seems as if it would work, however it depends on user input on a form, so until I test the form, I cannot verify whether the code is good. I recently got to a position with the rest of the form's code that allows me to test the function, but now there's a problem. Debugging it doesn't help much because I don't see any errors. Can anyone help?

Code:

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]![Category Code] = "S" Then
       LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
    Else
    Select Case Forms![Induct Gear]![Category Code] = "K"
                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'K'")
           Case Forms![Induct Gear]![Owning Organization] = "CLR-17 COMM CO XFA"
                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'L'")
           Case Forms![Induct Gear]![Owning Organization] = "CLR-17 COMM CO XFB"
                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'U'")
           Case Forms![Induct Gear]![Owning Organization] = "CLR-17 COMM CO XFC"
                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'V'")
           Case Forms![Induct Gear]![Owning Organization] = "CLR-17 COMM CO TECHCON"
                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'W'")
           Case Else
                LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
    End Select
    End If
    'Return the Entry Number of the first record in the Sub Shop with a "Closed" Status as FirstClosedEntryNumber.
    Select Case Forms![Induct Gear]![Sub Shop] = "4"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'" And "[Status] = 'Closed'")
            Case Forms![Induct Gear]![Sub Shop] = "K"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'K'" And "[Status] = 'Closed'")
            Case Forms![Induct Gear]![Sub Shop] = "L"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'L'" And "[Status] = 'Closed'")
            Case Forms![Induct Gear]![Sub Shop] = "M"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'M'" And "[Status] = 'Closed'")
            Case Forms![Induct Gear]![Sub Shop] = "U"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'U'" And "[Status] = 'Closed'")
            Case Forms![Induct Gear]![Sub Shop] = "V"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'V'" And "[Status] = 'Closed'")
            Case Forms![Induct Gear]![Sub Shop] = "X"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'X'" And "[Status] = 'Closed'")
            Case Else
 
    End Select
 
    '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 < 99 Then
        NewSuffix = OldSuffix + 1
    Else
        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
        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

When debugged, it gives me the error:
Compile error:
Statements and labels invalid between Select Case and first case

and then highlights

Code:

LastEntryNumber =

after the first Select Case.

**c_smithwick** · 03-20-2010, 08:04 AM

Your select case structure is horribly wrong. You should be testing the value of only one thing like below:

Code:

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 Else

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

end select

**th3spankst3r** · 03-20-2010, 12:13 PM

as you can tell, i'm not a programmer. I appreciate the advice. I used to program bits and peices of the Unreal engine, and I followed along in a C++ for beginners book once, long enough to program an error message. Other than that, I am very new to this. I have a little more knowledge about databases, but not much. This is by far a learning experience for me. Thanks again. More questions to come, I'm sure.

**th3spankst3r** · 03-20-2010, 02:37 PM

Now I get a Type Mismatch error. Here is the updated code

Code:

Option Compare Database
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.
    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 Else
                            LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
                End Select
    End Select
 
'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]", "[In Maintenance]", "[Sub Shop] = 'K'" And "[Status] = 'Closed'")
            Case "S"
                FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'" And "[Status] = 'Closed'")
            Case Else
                Select Case Forms![Induct Gear]![Owning Organization].Value
                        Case "CLR-17 COMM CO MAINT"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'" And "[Status] = 'Closed'")
                        Case "CLR-17 COMM CO XFA"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'L'" And "[Status] = 'Closed'")
                        Case "CLR-17 COMM CO XFB"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'U'" And "[Status] = 'Closed'")
                        Case "CLR-17 COMM CO XFC"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'V'" And "[Status] = 'Closed'")
                        Case "CLR-17 COMM CO TECHCON"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'W'" And "[Status] = 'Closed'")
                        Case Else
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'" And "[Status] = 'Closed'")
                End Select
    End Select
 
'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 < 99 Then
        NewSuffix = OldSuffix + 1
    Else
        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
        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

the type mismatch is on this line

Code:

FirstClosedEntryNumber = DMin("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'L'" And "[Status] = 'Closed'")

Is it because I have two "where" conditions in the DMin function? If so, how else would I go about doing that?

**th3spankst3r** · 03-20-2010, 09:30 PM

Nevermind that question. I have a new problem now. Here's the new code:

Code:

Option Compare Database
Option Explicit

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
 
'Return the Entry Number of the last record in the Sub Shop selected as LastEntryNumber.
    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 Else
                            LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
                End Select
    End Select
    
'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'")
                        Case "CLR-17 COMM CO XFA"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'L'")
                        Case "CLR-17 COMM CO XFB"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'U'")
                        Case "CLR-17 COMM CO XFC"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'V'")
                        Case "CLR-17 COMM CO TECHCON"
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'W'")
                        Case Else
                            FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = '4'")
                End Select
    End Select
 
'Return the ERO Prefix of the record who's Entry Number matches LastEntryNumber as OldPrefix.
    OldPrefix = DLookup("[ERO Prefix]", "[In Maintenance]", "[Entry Number] = " & LastEntryNumber)
 
'Return the ERO Suffix of the record who's Entry Number matches LastEntryNumber as OldSuffix.
    OldSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & LastEntryNumber)
 
'Return the ERO Suffix of the record who's Entry Number matches FirstClosedEntryNumber as NextClosedSuffix
    NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
 
'If OldSuffix is 100, Reset to NextClosedSuffix and transition to the next ERO Prefix in the Sub Shop. Also defines NewPrefix.
    If OldSuffix < 99 Then
        NewSuffix = OldSuffix + 1
    Else
        Select Case OldPrefix
            Case "HDA", "HDB", "HDC", "HDY", "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 "HDW"
                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
End Function

This function SHOULD return a value that looks like "HDY01" if Forms![Induct Gear]![Sub Shop] is set to W, which, by the time this code is ran, it is. However, this code is returning a value of "1". Please help.

**c_smithwick** · 03-21-2010, 05:51 PM

Your Function is not written to return anything, other wise your first line would look like:

Code:

Public Function CalculateNextERONo() As String

with the last line looking like this:

Code:

CalculateNextERONo = "some string value"

At what point are you looking to 'return' something?

**th3spankst3r** · 03-21-2010, 09:16 PM

Code:

NextERONo = NewPrefix & NewSuffix

if i'm not mistaken, being that "NextERONo" is dim'd as a string, this line should return "NextERONo" as a string. Perhaps I need to change the function name to Function NextERONo() as String? I don't understand why though. You should be able to define strings within the function, right?

While debugging it, i've noticed that everything works until it tries to define "NewPrefix", which for some reason ALWAYS returns a "" value, regardless of how I write it. I've tried a bunch of nested If statements, I've tried this Select Case, I've tried

Code:

Case "HDY"
   NewPrefix = "HDY"

and then setting that case to return a True value manually. Nothing seems to want to give me a value for NewPrefix. Could it be because the Select Case is nested in an If statement? if so, why doesnt a long list of nested If/else statements work?

**c_smithwick** · 03-22-2010, 06:41 AM

Without having your data to play with I can't see exactly where your function is going wrong, however two things - 1) You can combine your select case blocks as below:

Code:

    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'")
             FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'K'")
        Case "S"
             LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
             FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[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'")
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'L'")
                Case "CLR-17 COMM CO XFB"
                    LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'U'")
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'U'")
                Case "CLR-17 COMM CO XFC"
                    LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'V'")
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'V'")
                Case "CLR-17 COMM CO TECHCON"
                    LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = 'W'")
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = 'W'")
                Case "CLR-17 COMM CO MAINT"
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = '4'")
                Case Else
                    LastEntryNumber = DMax("[Entry Number]", "[In Maintenance]", "[Sub Shop] = '4'")
                    FirstClosedEntryNumber = DMin("[Entry Number]", "[Closed EROs]", "[Sub Shop] = '4'")
                    
            End Select
    End Select
    
    OldPrefix = DLookup("[ERO Prefix]", "[In Maintenance]", "[Entry Number] = " & LastEntryNumber)
    OldSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & LastEntryNumber)
    NextClosedSuffix = DLookup("[ERO Suffix]", "[In Maintenance]", "[Entry Number] = " & FirstClosedEntryNumber)
    
    If OldSuffix < 99 Then
        NewSuffix = OldSuffix + 1
    Else
        Select Case OldPrefix
            Case "HDA", "HDB", "HDC", "HDY", "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 "HDW"
                NewPrefix = "HDX"
        End Select
        NewSuffix = NextClosedSuffix
    End If
    NextERONo = NewPrefix & NewSuffix
    Forms![Induct Gear]![ERO Number] = NextERONo

End Function

and 2) You dimension FirstClosedEntryNumber As a String

Code:

 Dim FirstClosedEntryNumber As String

Yet the DMin function usually returns a numerical value (or the minimum alphabetically from a list of strings). You might want to examine to make sure your variables are all agreeable with the types of data you are asking them to hold. Step through your code using the F8 key and use the immediate window or a debug watch to track the value of your variables as you step through each line of code and I'm sure you can isolate the problem. By the way, from a programming standpoint, If your "function" doesn't return a value, you usually define it as a subroutine, although I know access form events must be assigned to a function. It is a good idea to have your function return a boolean if nothing else, and at some point in the function set it to true or false depending on operations. That way you can determine if the function executed successfully by looking at the true or false return value.

**th3spankst3r** · 03-22-2010, 08:29 AM

Well, as for the first suggestion, like I said, I have tried everything. I originally had my select case blocks combined, but when I couldn't figure out what was wrong, I started trying it different ways.

As for the second suggestion, when stepping through my function (F8 while debugging), when I mouse over all of my variables as it goes through, they all give me the values that they're supposed to give, EXCEPT NewPrefix. When I mouse over NewPrefix, even at the end of the function, it says "" as the value.

**c_smithwick** · 03-22-2010, 11:10 AM

Look at this section of your code:

Code:

If OldSuffix < 99 Then
        NewSuffix = OldSuffix + 1
Else
    Select Case OldPrefix
        Case "HDA", "HDB", "HDC", "HDY", "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 "HDW"
            NewPrefix = "HDX"
    End Select
    NewSuffix = NextClosedSuffix
End If

If OldSuffix > = 99 then you are never setting a value for NewPrefix, hence it is returning a default null string

**th3spankst3r** · 03-22-2010, 12:03 PM

So a Select Case statement doesn't qualify for the "else" portion of an If/Then/Else statement? does it need to be a seperate function?

**c_smithwick** · 03-22-2010, 12:11 PM

Yes, you can nest a select case structure within an else statement, but you should check the value you have for OldSuffix. If that value is never < 99, then you will never enter the select case structure. Like I said befor, without you data to play with I can only venture a guess, but since you are not throwing an error, then the null string value you are seeing for newPrefix is valid. All string variables are set to a null string when dimensioned, so if you never set them equal to something in the course of your subroutine, that is the value you will see for them. Step through your code carefully and I bet you are never entering that select case structure to set your NewPrefix value.

**th3spankst3r** · 03-22-2010, 12:41 PM

Wow, you're absolutely right. I added a line before the select case statement that sets NewPrefix = OldPrefix, and it works. Thanks a lot!

Function Help

Thread Tools

Function Help

hmm

New Error

Similar Threads

Want function to get current function name

Sum Function Help

function key

Avg Function

Is there a function to do this....

Posting Permissions