can't get my head around this If statement

[sdel_nevo]

Hi Guys


hope you all had a great christmas and a happy nerw year

i have a problem with some code that works out the speed of a machine

Private Sub SOIMachineConditiontxtbox_AfterUpdate()

'This will Work Out the Chartered Speed For A machine
Dim Speed As String
Speed = 61 / Me.ACTUALLAYLENGHTTXTBOX * 1000

MsgBox Me.SOIMachineConditiontxtbox

If Me.SOIMachineConditiontxtbox = 3 And Speed < 6000 Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 6000
End If

end sub

MsgBox Me.SOIMachineConditiontxtbox i show the id of the machine when the afterupdate event fires

Me.ACTUALLAYLENGHTTXTBOX is a feild on a form that a user fills in "This is a number feild"

Me.SOIMachineConditiontxtbox is a drop down list containing machine names, 3 in the above code is it's id number

Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX is a feild on the form that will record the the sum

the code above works when selecting the machine with the ID of 3 and the correct value is placed into the me.CHARTEREDSPEEDTWISTPERMETERTXTBOX on the form


The problem is that i have 6 machines in total, if i replace the above code with the code below, no matter what i do the variable "Speed" always shows 5000?

Private Sub SOIMachineConditiontxtbox_AfterUpdate()

'This will Work Out the Chartered Speed For A machine
Dim Speed As String
Speed = 61 / Me.ACTUALLAYLENGHTTXTBOX * 1000


MsgBox Me.SOIMachineConditiontxtbox

If Me.SOIMachineConditiontxtbox = 1 And Speed < 4500 Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 4500
End If

If Me.SOIMachineConditiontxtbox = 2 And Speed < 4500 Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 4500
End If


If Me.SOIMachineConditiontxtbox = 3 And Speed < "6000" Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 6000
End If

If Me.SOIMachineConditiontxtbox = 4 And Speed < 5000 Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
End If

If Me.SOIMachineConditiontxtbox = 5 And Speed < 5000 Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
End If

If Me.SOIMachineConditiontxtbox = 6 And Speed < 5000 Then
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
Else
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
End If

end sub

i can't get my head around how to do this, im sure i am doing something silly but for the life of me i can't seem to work it out.
any help would be fantastic

Kind regards

Steve

[ranman256]

your 6000 is set as a string, i think you mean a number, so remove the quotes.
Below is your code as a SELECT statement. because of your separated IF statements, it ignores everything but your LAST IF statment, so its always 5000.
The below code will not.

Select Case SOIMachineConditiontxtbox
  Case 1, 2
     If Speed < 4500 Then
         CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
     Else
         CHARTEREDSPEEDTWISTPERMETERTXTBOX = 4500
     End If
     
  
  Case 3
     If Speed < 6000 Then
         CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
     Else
         CHARTEREDSPEEDTWISTPERMETERTXTBOX = 6000
     End If
  
  Case 4, 5, 6
     If Speed < 5000 Then
         CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
     Else
         CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
     End If
End Select

[sdel_nevo]

Hi ranman256

many thanks for getting back

since posting my original code i have managed to get it to work with the following, its pritty dirty but does what i want.

Private Sub SOIMachineConditiontxtbox_AfterUpdate()

'This will Work Out the Chartered Speed For A machine
Dim Speed As String
Speed = 61 / Me.ACTUALLAYLENGHTTXTBOX * 1000


'MsgBox Me.SOIMachineConditiontxtbox

If Me.SOIMachineConditiontxtbox = 1 And Speed < 4500 Then
'MsgBox "<4500"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
ElseIf Me.SOIMachineConditiontxtbox = 1 And Speed > 4500 Then
'MsgBox ">4500"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 4500
Exit Sub
End If

If Me.SOIMachineConditiontxtbox = 2 And Speed < 4500 Then
'MsgBox "<4500"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
ElseIf Me.SOIMachineConditiontxtbox = 2 And Speed > 4500 Then
'MsgBox ">4500"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 4500
Exit Sub
End If


If Me.SOIMachineConditiontxtbox = 3 And Speed < 6000 Then
'MsgBox "<6000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
ElseIf Me.SOIMachineConditiontxtbox = 3 And Speed > 6000 Then
'MsgBox ">6000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 6000
Exit Sub
End If

If Me.SOIMachineConditiontxtbox = 4 And Speed < 5000 Then
'MsgBox "<5000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
ElseIf Me.SOIMachineConditiontxtbox = 4 And Speed > 5000 Then
'MsgBox ">5000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
Exit Sub
End If

If Me.SOIMachineConditiontxtbox = 5 And Speed < 5000 Then
'MsgBox "<5000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
ElseIf Me.SOIMachineConditiontxtbox = 5 And Speed > 5000 Then
'MsgBox ">5000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
Exit Sub
End If

If Me.SOIMachineConditiontxtbox = 6 And Speed < 5000 Then
'MsgBox "<5000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = Speed
ElseIf Me.SOIMachineConditiontxtbox = 6 And Speed > 5000 Then
'MsgBox ">5000"
Me.CHARTEREDSPEEDTWISTPERMETERTXTBOX = 5000
Exit Sub
End If

End Sub

your solution looks much more graceful so i may well use that

many many thanks for the reply

Kind regards
Steve