Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87

    Unanswered: Help with one of my functions

    I'm having trouble with one of my functions and I don't know why. Could someone help please?
    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
            NewSuffix = Format(NewSuffix, "00")
        Else
            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 "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
    Everything works EXCEPT NewPrefix. I cannot seem to have a value assigned to it. Currently I am testing it with the condition "OldPrefix = HDY", so it should return NewPrefix as "HDY", according to the select case. however, it returns a "" value. I have tried substituting "NewPrefix = OldPrefix" for "NewPrefix =" & the expression that defines OldPrefix. I have tried declaring that if OldPrefix = "HDY" then NewPrefix = "HDY". Nothing seems to work. Thanks to anyone who can help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what happens when you put a watch on the case statement
    because you don't have a case else statemet you don't sewt anything if the values isn't one of thise in the list
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Well I realized you were right, so I changed the last case to a Case Else statement. No difference. When I step through the program, after having all of the dependent information on my form filled out, everything in the function returns the value that it should, EXCEPT NewPrefix. NewPrefix gets returned as "", therefore, when NewERONo = NewPrefix & NewSuffix... NewERONo becomes "01", which is JUST the suffix. For the life of me, I cannot get NewPrefix to return anything but "". 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?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you put watches/breakpoints on the code to make certain that you have data that triggers the code
    making certain that you have datat that is going to fire the case statement

    bear in mind you are using a string datatype for OldSuffix
    so I suspect you are going to have problems in this area
    Code:
    If OldSuffix < 99 Then
    If its numeric use a numeric datatype, if its text use a text datatype

    adding one to a string /text datatype is going to give indeterminate results.

    to convert from string to a numeric datatype use an appropriate conversion function such as Cint/CLng. you may alos wna tto check if the value a number by using the IsNumeric function first

    you need to be very very clear in your mind as to what datatype your variabels are using.. its one of the reasons that many professional developers use naming conventions such as strMyStringVariiable, intMyIntVariable and so on
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I don't know how to use watches, but when I step through (F8 while debugging) from the beginning to the end, I can mouse over my variables as I go along, and it will tell me the values. They all return proper values EXCEPT NewPrefix. When I mouse over NewPrefix, it just says "".

    As far as OldSuffix being the String datatype, I changed it because I realized you are right. However, it was still giving me the proper value for NewSuffix. Mousing over NewSuffix would give me a value of "01" when OldSuffix is "00".

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok
    so if you are going to develop 'stuff' in VBA you need to get to grips with debugging...

    open a code window with the code you want to debug
    find the line(s) you are interested in, click to the left of that/those line(s), a red circle shoudl appear. this means the code will halt when it reaches those lines
    clicking on the circle again clears the break point

    once the code has halted you can use the immediate window to examine any variable in the current scope. ferinstance
    ? myvariable
    ...will display the current value of myvariable
    ? myfunction (1,2,3)
    ..will evaluate and return function

    you can also set variables so you can force a chink f code to work/not work
    myvariable = 1234
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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