MS Access User-Function Runs But Doesn't display value-CodePudding

MS Access newbie here. I've written a VBA UDF to calculate an AQL Sample Size by the Inspection Level (IL Variable in my code), and Lot size (Batch Variable in my code)

In an access userform, I have the two values for my two variables displayed in a text box. I then have a third text box which has my UDF in it, and that I want to auto-update to the new value, when I add or change either of the two variables, but it never changes. It just stays on zero.

When I update either variable, if I put a stop in the UDF VBA code, the code does run, and provides me with the correct answer in a Watch in my VBE as I step through it. So the code seems to be OK, I'm assuming. Just something I'm doing wrong in the text box properties I'm trying to return the value in, I'm guessing.

The code is below if it helps.

Also, somewhat related question, I'm guessing you cannot use a UDF in a table? I can use the same UDF in an excel table, so I was thinking I should be able to, but it appears I cannot.

Thanks

 Public Function SampleSize(IL As String, Batch As Long) As Long
If IL = "S1" Then
    Select Case Batch
    Case 2 To 50
        Sample = 2
    Case 51 To 500
        Sample = 3
    Case 501 To 35000
        Sample = 5
    Case Is >= 35001
        Sample = 8
    End Select
End If

If IL = "S2" Then
Select Case Batch
    Case 2 To 25
        Sample = 2
    Case 26 To 150
        Sample = 3
    Case 151 To 1200
        Sample = 5
    Case 1201 To 35000
        Sample = 8
    Case Is >= 35001
        Sample = 13
End Select
End If

If IL = "S3" Then
Select Case Batch
    Case 2 To 15
        Sample = 2
    Case 16 To 50
        Sample = 3
    Case 51 To 150
        Sample = 5
    Case 151 To 500
        Sample = 8
    Case 501 To 3200
        Sample = 13
    Case 3201 To 35000
        Sample = 20
    Case 35001 To 500000
        Sample = 32
    Case Is >= 500001
        Sample = 50
End Select
End If

If IL = "S4" Then
Select Case Batch
    Case 2 To 15
        Sample = 2
    Case 16 To 25
        Sample = 3
    Case 26 To 90
        Sample = 5
    Case 91 To 150
        Sample = 8
    Case 151 To 500
        Sample = 13
    Case 501 To 1200
        Sample = 20
    Case 1201 To 10000
        Sample = 32
    Case 10001 To 35000
        Sample = 50
    Case 35001 To 500000
        Sample = 80
    Case Is >= 500001
        Sample = 125
End Select
End If

If IL = "G1" Then
Select Case Batch
    Case 2 To 15
        Sample = 2
    Case 16 To 25
        Sample = 3
    Case 26 To 90
        Sample = 5
    Case 91 To 150
        Sample = 8
    Case 151 To 280
        Sample = 13
    Case 281 To 500
        Sample = 20
    Case 501 To 1200
        Sample = 32
    Case 1201 To 3200
        Sample = 50
    Case 3201 To 10000
        Sample = 80
    Case 10001 To 35000
        Sample = 125
    Case 35001 To 150000
        Sample = 200
    Case 150001 To 500000
        Sample = 315
    Case Is >= 500001
        Sample = 500
End Select
End If

If IL = "G2" Then
Select Case Batch
    Case 2 To 8
        Sample = 2
    Case 9 To 15
        Sample = 3
    Case 16 To 25
        Sample = 5
    Case 26 To 50
        Sample = 8
    Case 51 To 90
        Sample = 13
    Case 91 To 150
        Sample = 20
    Case 151 To 280
        Sample = 32
    Case 281 To 500
        Sample = 50
    Case 501 To 1200
        Sample = 80
    Case 1201 To 3200
        Sample = 125
    Case 3201 To 10000
        Sample = 200
    Case 10001 To 35000
        Sample = 315
    Case 35001 To 150000
        Sample = 500
    Case 150001 To 500000
        Sample = 800
    Case Is >= 500001
        Sample = 1250
End Select
End If

If IL = "G3" Then
Select Case Batch
    Case 2 To 8
        Sample = 3
    Case 9 To 15
        Sample = 5
    Case 16 To 25
        Sample = 8
    Case 26 To 50
        Sample = 13
    Case 51 To 90
        Sample = 20
    Case 91 To 150
        Sample = 32
    Case 151 To 280
        Sample = 50
    Case 281 To 500
        Sample = 80
    Case 501 To 1200
        Sample = 125
    Case 1201 To 3200
        Sample = 200
    Case 3201 To 10000
        Sample = 315
    Case 10001 To 35000
        Sample = 500
    Case 35001 To 150000
        Sample = 800
    Case 150001 To 500000
        Sample = 1250
    Case Is >= 500001
        Sample = 2000
End Select
End If
End Function

CodePudding user response：

You never assign a return value to the function itself!

You should place this line of code as indicated:

    SampleSize = Sample
End Function

Also it is good practice to declare variables within the scope of your function.

Dim Sample As Long

When declared it will be initialized to 0, which will also be your default return value and the same Type as your Function.

Undeclared variables will be Variant type and initialized to Empty...