If Statement

[Noreen]

Please help with If Statement

Hi there,

I have a form that has details of a competency Matrix with 5 combo boxes:

Team Leader
Rep Name
Belt
Competency
Team Leads Rating

I have the form set up to after updates etc with the following code:

Option Compare Database
Private Sub Combo12_AfterUpdate()
Me.Competency = Null
Me.Behaviour = Null
Me.Competency.Requery
End Sub
Private Sub Command50_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_Progress_Report"
DoCmd.RunMacro "mac_Progress_Update"
End Sub
Private Sub Competency_AfterUpdate()
Me.Behaviour = Null
Me.Behaviour.Requery
End Sub
Private Sub RepName_AfterUpdate()
Forms("Training Done").Controls("Training").Requery
End Sub
Private Sub Teamlead_AfterUpdate()
Me.RepName = Null
Me.RepName.Requery
End Sub
Private Sub Teamlead_Enter()
Me.Teamlead = Null
Me.RepName = Null
Me.Belt = Null
Me.Competency = Null
Me.Behaviour.Requery
Me.TeamLeaderRating = Null
End Sub
Private Sub RepName_Enter()
Me.Belt = Null
Me.Competency = Null
Me.Behaviour.Requery
Me.TeamLeaderRating = Null
End Sub
Private Sub Belt_Enter()
Me.Competency = Null
Me.Behaviour.Requery
Me.TeamLeaderRating = Null
End Sub

It's perfect except now my manager wants the Belt to come up automically based on tenure - I added the tenure box to query builder but now I need an if statement to do this right?

Could anyone be so kind to help me please?

I would really appreciate it!!!

 

[Noreen]

Re: If Statement Updated

Hi there,

I tried this but now it's selecting New Hire Belt for everybody.

Also FYI, Tenure is not displayed on the form

Option Compare Database
Private Sub Combo12_AfterUpdate()
Me.Competency = Null
Me.Behaviour = Null
Me.Competency.Requery
End Sub
Private Sub Combo90_AfterUpdate()
Me.Combo90.Requery
End Sub
Private Sub Combo90_BeforeUpdate(Cancel As Integer)
If Teamlead = Visible Then
Combo90.Visible = False
Else
Combo90.Visible = False
End If
End Sub
End Sub
Private Sub Command50_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_Progress_Report"
DoCmd.RunMacro "mac_Progress_Update"
End Sub
Private Sub Competency_AfterUpdate()
Me.Behaviour = Null
Me.Behaviour.Requery
End Sub
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
Private Sub Teamlead_AfterUpdate()
Me.RepName = Null
Me.RepName.Requery
End Sub
Private Sub Teamlead_Enter()
Me.Teamlead = Null
Me.RepName = Null
Me.Belt = Null
Me.Competency = Null
Me.Behaviour.Requery
Me.TeamLeaderRating = Null
End Sub
Private Sub RepName_Enter()
Me.Belt = Null
Me.Competency = Null
Me.Behaviour.Requery
Me.TeamLeaderRating = Null
End Sub
Private Sub Belt_Enter()
Me.Competency = Null
Me.Behaviour.Requery
Me.TeamLeaderRating = Null
End Sub

Does anyone have any suggestions?

 

[Noreen]

This if statement is incorrect as I have another combo box dependent on it and it doesn't update so I have to reselect. Is there a way to have an if statement with select rather than typing the value?