Populate fields based on two others.

[dave_the_bear]

The user should:
Enter the Expected Quantity in one field.
Enter the Faults in the other field.

The Lot Size field calculates and displays: [Expected Quantity] - [Faults]

Based on the Lot Size the Sample and Fail fields should be filled as follows:

Function SampleCal(Lot As Integer)
    
Select Case Lot
   Case 2 To 8
    Sample = 2
    Fail = 1
   Case 9 To 15
    Sample = 3
    Fail = 1
   Case 16 To 25
    Sample = 5
    Fail = 1
   Case 26 To 50
    Sample = 8
    Fail = 1
   Case 51 To 90
    Sample = 13
    Fail = 1
   Case 91 To 150
    Sample = 20
    Fail = 1
   Case 151 To 280
    Sample = 32
    Fail = 2
   Case 281 To 500
    Sample = 50
    Fail = 2
   Case 501 To 1200
    Sample = 80
    Fail = 3
   Case 1201 To 3200
    Sample = 125
    Fail = 4
   Case Else
    Sample = ""
    Fail = ""

End Select

End Function

The Lot Size field is being calculated but Im getting this error instead of the Sample and Fail fields filling:

"Compile error:
Sub or Function not defined"

Thanks

 

[bob fitz]

If this code is in the forms code module and "Sample" and "Fail" are controls on the form, then they should be referenced with Me.
For example:

Case 2 To 8
Me.Sample = 2
Me.Fail = 1

 

[dave_the_bear]

The forms code module?

I hit alt+F11 and created a module?

Changed the code to:

Public Function SampleCal(Lot As Integer)
    
Select Case Lot
   Case 2 To 8
    Me.Sample = 2
    Me.Fail = 1
   Case 9 To 15
    Me.Sample = 3
    Me.Fail = 1
   Case 16 To 25
    Me.Sample = 5
    Me.Fail = 1
   Case 26 To 50
    Me.Sample = 8
    Me.Fail = 1
   Case 51 To 90
    Me.Sample = 13
    Me.Fail = 1
   Case 91 To 150
    Me.Sample = 20
    Me.Fail = 1
   Case 151 To 280
    Me.Sample = 32
    Me.Fail = 2
   Case 281 To 500
    Me.Sample = 50
    Me.Fail = 2
   Case 501 To 1200
    Me.Sample = 80
    Me.Fail = 3
   Case 1201 To 3200
    Me.Sample = 125
    Me.Fail = 4
   Case Else
    Me.Sample = ""
    Me.Fail = ""

End Select
End Function

Now I'm getting "Invalid use of Me keyword"

 

[dave_the_bear]

I put the function in the form module (I think?)

No longer getting the error.
The Sample and Fail field now both display: #Size!

 

[bob fitz]

Where are you using the function

 

[dave_the_bear]

On the form.

I got rid of the module and put it in:

Microsoft Access Class Objects
Form_Sample Inspection

The exact code inside it is:

Option Compare Database

Public Function SampleCal(Lot As Integer)
    
Select Case Lot
   Case 2 To 8
    Me.Sample = 2
    Me.Fail = 1
   Case 9 To 15
    Me.Sample = 3
    Me.Fail = 1
   Case 16 To 25
    Me.Sample = 5
    Me.Fail = 1
   Case 26 To 50
    Me.Sample = 8
    Me.Fail = 1
   Case 51 To 90
    Me.Sample = 13
    Me.Fail = 1
   Case 91 To 150
    Me.Sample = 20
    Me.Fail = 1
   Case 151 To 280
    Me.Sample = 32
    Me.Fail = 2
   Case 281 To 500
    Me.Sample = 50
    Me.Fail = 2
   Case 501 To 1200
    Me.Sample = 80
    Me.Fail = 3
   Case 1201 To 3200
    Me.Sample = 125
    Me.Fail = 4
   Case Else
    Me.Sample = ""
    Me.Fail = ""

End Select
End Function

 

[bob fitz]

Where are you calling the function?
What event causes it to fire?

 

[dave_the_bear]

I have the Sample and Fail fields Control Source =SampleCal([Lot Size])

Should I have a specific event to carry out the function?

For example, After Update on the Lot Size field?

 

[bob fitz]

This what I think you need to do:

Put the following line of code in the After Update events of "Faults" and "Expected Quantity"
SampleCal (Nz([lot size], 0))

Remove the Control Source settings of "Sample" and "Fail"

 

[vbaInet]

Case Else
Me.Sample = ""
Me.Fail = ""

[/CODE]

By the way, are you sure you really want to return the zero-length string for anything else?