if function?

[hazell]

Hi,
I am trying to get a form to display certain information based on the data in another box. I have to input some exam results and want to assign a level to them. So, if they score < 13, the result would read "Below Entry 3" if between 13 and 21 "Entry 3" etc
If this was a spreadsheet I would use an if function, but I am not sure that I can do this here
Any suggestions
thanks
Hazell

 

[pr2-eugin]

Hello hazell, you can use the Select Case statement..

 

[hazell]

I have never done this and would need some step by step instructions if anyone can help. How would I formulate the statement and where would I put it?

 

[pr2-eugin]

Okay I will try my best here..
1. Goto the Form design mode, in the Property sheet. You will have several tabs, look for the tab called 'Events'.
2. Select the Form Current, if this is your First VBA check the link : http://www.baldyweb.com/FirstVBA.htm
3. In the Event Procedure, you should have the code something like..

Private Sub Form_Current()
    Select Case Me.scoreFieldName
        Case Is < 13
            Me.resultControlName = "Below Level 3"
        Case 13 To 21
            Me.resultControlName = "Level 3"
        Case Else
            Me.resultControlName = "Whatever you want more"
    End Select
End Sub

Save and Compile the CODE. Then finally Check if it works.

 

[hazell]

thank you so much for your help

 

[hazell]

Still struggling with this, if anyone can help. this is what I have written, based on the previous advice:

Private Sub Form_Current()
Select Case BS_Literacy.Score
Case Is < =13
BS_Literacy.Level = "Below Entry Level 3"
Case 14 To 32
BS_Literacy.Level = "Entry Level 3"
Case 33 To 52
BS_Literacy.Level = "Entry Level 2"
Case 53 To 65
BS_Literacy.Level = "Entry Level 1"
Case 66 To 72
BS_Literacy.Level = "Level 1"
End Select
End Sub

the form is called BS_Literacy, Score is a records the level the candidate achieves in the test and I wanted Level to display the corresponding level. What have I done wrong?
thanks
Hazell

 

[pr2-eugin]

What have I done wrong?

You have not followed the code format I gave you.. That's all. Maybe it was my fault of not tell what to replace.

See if this works..

Private Sub Form_Current()
    Select Case Me.Score
        Case Is < = 13
            Me.Level = "Below Entry Level 3"
        Case 14 To 32
            Me.Level = "Entry Level 3"
        Case 33 To 52
            Me.Level = "Entry Level 2"
        Case 53 To 65
            Me.Level = "Entry Level 1"
        Case Else
            Me.Level = "Level 1"
    End Select
End Sub

 

[hazell]

I may not be entering it in the right place. I clicked on the ellipsis in the on click section of the event property box and copied it in there. Is this right or have I done something silly?
thanks for your help

 

[pr2-eugin]

Not On Click, you need the On Current event

 

[Cronk]

Hazell

You did replace in your code, BS_Literacy. with Me. ?

You also ignored Paul's Case Else part. What do you want displayed when the score is > 72? Your code will display what ever it was for the previous record, or blank on opening the form with the first record having a score of >72

 

[hazell]

Thanks for the advice. I seem to be getting a little lost.

There can't be a score of > 72, though I suppose it should perhaps say error if you try to enter anything higher than 72.

Also, I am trying to enter this code into the Level box on the BS Literacy form. Is this right? Should I be looking at the property box for the form or the score box or the level box. I assumed as it was being entered into the box that is marked level, that is what I was looking at

This sounds garbled but I hope you can understand me
thanks
Hazell

 

[Cronk]

Whenever a user tells me something cannot happen as in cannot be greater than 72, I would have the following message show when/if it did happen
Msgbox "Cronk was told this would never happen",64,"The impossible has happened"

In other words, I don't like not covering all possibilities.

As to where the code goes, I thought you had, rightly, put it in the form Current event.

 

[hazell]

Apparently I have invalid syntax. I copied and pasted the code in. Any suggestions. thanks for your help. I would be lost without you

 

[pr2-eugin]

Show the code you currently have.

 

[hazell]

Just exactly what was written above

 

[pr2-eugin]

If it is exactly the way written above, where is the Syntax error then?

 

[hazell]

I'm not sure. Message just says I may have entered an operand without an operator

 

[pr2-eugin]

Again, please show us the code !

It's alright if you have made changes.. We just cannot make assumptions ! Help us to help you.

 

[hazell]

Private Sub Form_Current()
Select Case Me.Score
Case Is < = 13
Me.Level = "Below Entry Level 3"
Case 14 To 32
Me.Level = "Entry Level 3"
Case 33 To 52
Me.Level = "Entry Level 2"
Case 53 To 65
Me.Level = "Entry Level 1"
Case Else
Me.Level = "Level 1"
End Select
End Sub

 

[pr2-eugin]

When the compiler error occurs it will highlight the section where the error falls, did it do that for you? If it did, can you show which line it is talking about~?