Select Case Help 1

[Davidwazza]

Hi, I am a bit of a beginner in VBA, so...

I am writing a database based on students grade. The user inputs their percentage, and the grade is automatically calculated. It prints the grade in an "unbound textbox". How would I go about setting this up? I have the following thus far.

Select Case [intARI]
Case If >= 90
debug.print "HD"
Case 80 <= [intARI] < 90
debug.print &quot;D&quot;
Case 65 <= [intARI] < 80
debug.print &quot;C&quot;
Case 50 <= [intARI] < 65
debug.print &quot;P&quot;
Case Else
debug.print &quot;F&quot;
End Select

How do I link this VBA code to the textbox (named &quot;ARIGrade&quot, and also is the debug.print correct? Doesnt appear to be doing anything? Like, as an example, I tried putting this in an onClick, and put a MsgBox prompt also, and this worked fine, but the textbox was still empty.

Thanking in advance, David.

 

[VBslammer]

Your syntax won't work in vb, try this:

  Select Case [intARI]
   Case Is >= 90
    Me![txtGrade] = "HD"
   Case 80 To 89
    Me![txtGrade] = "D"
   Case 65 To 79
    Me![txtGrade] = "C"
   Case 50 To 64
    Me![txtGrade] = "P"
   Case Else
    Me![txtGrade] = "F"
  End Select

Assumes your textbox is named [tt]txtGrade[/tt]

VBSlammer

Unemployed in Houston, Texas

 

[Paladine]

VBSlammer is correct. Debug.print just displays the result in the &quot;Immediate window&quot; (under view in VBA Editor), so using his code will work!



_________________
Paladine

&quot;Doing what others find difficult is talent.....

 

[Davidwazza]

Thanks for that

Just one quick thing - the cases have been changed from say, for a credit, 65 - 79. What happens if the user puts in 79.3 ? Would this work as a case? Round down?

Thanks again.

 

[VBslammer]

If you use integer division you'll get rounding down for values of .5 and less, and rounding up for values .5+ and more.

  Select Case ([intARI] \ 1)    '<-- integer division
   Case Is >= 90
    Me![txtGrade] = &quot;HD&quot;
   Case 80 To 89
    Me![txtGrade] = &quot;D&quot;
   Case 65 To 79
    Me![txtGrade] = &quot;C&quot;
   Case 50 To 64
    Me![txtGrade] = &quot;P&quot;
   Case Else
    Me![txtGrade] = &quot;F&quot;
  End Select

VBSlammer

Unemployed in Houston, Texas

 

[Davidwazza]

And so hence last thing!

How do I link this code up to my form?

So far, Ive got it in a &quot;current&quot; form function - but it obviously doesnt automatically update once you change it - just only when you enter the form? How would I go about doing this?

So far;

Private Sub Form_Current()

Select Case ([intARI] \ 1) '<-- integer division
Case Is >= 90
Me![txtGrade] = &quot;HD&quot;
Case 80 To 89
Me![txtGrade] = &quot;D&quot;
Case 65 To 79
Me![txtGrade] = &quot;C&quot;
Case 50 To 64
Me![txtGrade] = &quot;P&quot;
Case Else
Me![txtGrade] = &quot;F&quot;
End Select

End Sub

What function then???

 

[VBslammer]

I'm not sure what you're doing with this value once you calculate it, but I normally make a separate routine for stuff like this so it can be called from multiple places in code:

Private Sub Form_Current()
  Call UpdateGrade
End Sub

Private Sub txtPoints_AfterUpdate()
  Call UpdateGrade
End Sub

Private Sub UpdateGrade()
    Select Case ([txtPoints] \ 1)
   Case Is >= 90
    Me![txtLetterGrade] = "HD"
   Case 80 To 89
    Me![txtLetterGrade] = "D"
   Case 65 To 79
    Me![txtLetterGrade] = "C"
   Case 50 To 64
    Me![txtLetterGrade] = "P"
   Case Else
    Me![txtLetterGrade] = "F"
  End Select
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[Davidwazza]

Beautiful! Your a legend.

Works great. Ta.