Help with if statement

[Noreen Deasy]

Hi there,

I have a form (Competency Matrix) to rate a sales rep according to competencies with 5 combo boxes:

1. Team Leads Name
2. Rep Name
3. Belt
4. Competency
5. TLs Rating


My manager wants to automatically select Belt based on Tenure.

I have added tenure to the query builder and the following code for repname event after update:

Private Sub RepName_AfterUpdate()
If Tenure <= "6" Then
 Me.Belt = "New Hire Belt"
ElseIf Tenure >= "6" Then
    Me.Belt = "Purple"
End If
Forms("Training Done").Controls("Training").Requery
End Sub

It works as far as it selects New Hire Belt for everybody - even if tenure is 71, this shouldn't be Purple but I kept it simple to start with.

Could anyone give me advice as to where I have gone wrong?

[Noreen Deasy]

The above if statement isn't correct as I have an event procedure done - competency based on it so I have to reselect new hire belt to get the competency to update.

I don't understand now how I will get this to auomatically select?

Can someone please help me?

[Arevin]

Hey Noreen,

I could be wrong but looking at line 5, it just doesn't feel right.

Have you tried changing it to

Else 
Me.Belt = "Purple" 
End If

because that's what your line is saying in essence anyway, the reason I suggest this is because with Elseif, you're making a nested if with out an alternative solution.

[Noreen Deasy]

You see I was trying to keep it simple by doing this as:
>=6 and <12 is purple
>=12 and <=24 is red
>24 is brown

Is this possible?

[hype261]

I believe you problem is this...

If Tenure <= "6"

You have quotes surrounding your "6" which is causing it to be evaluated as a string.

If Tenure is an int then just do Tenure <= 6, but it Tenure is an String do CInt(Tenure) <= 6.

[Noreen Deasy]

Problem Updated

Hi there,

This actually to be totally wrong anyway because my competency combo box was dependent on the pic from belt. Therefore it needs to pick the choice from the combo box.

This is what I tried now:

I inserted a hidden tenure combo box to try make things simplier. I coded it to automatically select on after update from RepName.

Now I tried to insert the following code to see if that would work (it doesn't!)

Private Sub Belt_BeforeUpdate(Cancel As Integer)
If Me.Tenure <= 6 Then GoTo ComboBox
Me.Belt = Me.Belt.ItemData(0)
ElseIf Me.Tenure() > 6 And Tenure < 12 Then
Me.Belt = Me.Tenure.ItemData(1)
ElseIf Me.Tenure() >= 12 And Tenure < 24 Then
Me.Belt = Me.Tenure.ItemData(2)
ElseIf Me.Tenure() >= 24 Then
Me.Belt = Me.Tenure.ItemData(3)
End If
End Sub

Any ideas where I've gone wrong?

I tried it in Tenure After Update event and nothing happens either so I don't know where the problem lies?

This is really urgent as I need to have this done to present to my mgr tomorrow - please help!

[Noreen Deasy]

This is the solution I figured it out

Private Sub RepName_AfterUpdate()
Me.Tenure = Null
Me.Tenure.Requery
Me.Tenure = Me.Tenure.ItemData(0)
If Me.Tenure.ItemData(0) <= 6 Then
Me.Belt = Me.Belt.ItemData(0)
ElseIf Me.Tenure.ItemData(0) >= 7 And Tenure.ItemData(0) < 12 Then
Me.Belt = Me.Belt.ItemData(1)
ElseIf Me.Tenure.ItemData(0) >= 13 And Tenure.ItemData(0) < 24 Then
Me.Belt = Me.Belt.ItemData(2)
ElseIf Me.Tenure.ItemData(0) >= 25 Then
Me.Belt = Me.Belt.ItemData(3)
End If
Forms("Training Done").Controls("Training").Requery
End Sub