Grade and Percent fields.

[Joe M]

Hi, in a Score table I have two fields. Field 1 is "Percent" and Field 2
is "Grade". The "Grade" field will be programmed so that it will
automatically enter the grade based upon the percent score in "Percent"
field.
eg. 95% - 100% is A+ grade
85% - 94% is A grade
75% - 84% is B grade.
How do I do this? And what's the code to use??? So that if the teacher
enters 88% in PERCENT field , it will automatically enter A grade into the
GRADE field.

 

[Allen Browne]

Use the After Update event procedure of the Percent field to lookup to
Grade:

Private Sub Percent_AfterUpdate()
If Not IsNull(Me.Percent) Then
Me.Grade = DLookup("Grade", "Score", "Percent >=" & Me.Percent))
End If
End Sub

 

[Joe M]

Thanks Allen, just what I needed. But I am not too good with VB.
Could you please detail the code that I must use to do this in the
Procedure.
95% - 100% is A+ grade
85% - 94% is A grade
75% - 84% is B grade.
Thanks.

 

[Allen Browne]

1. Open your form in design view.

2. Right-click the Percent text box, and choose Properties.

3. On the event tab, set the AfterUpdate property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Paste in the 3 lines of code from the previous thread.

 

[Mike Painter]

Allen Browne wrote:

-1. Create a table named Score with fields name Grade and percent.
-5. Fill it in.

 

[Allen Browne]

Yes, thanks for clarifying, Mike.

That is assumed: that you have a table with these 2 fields, so you can
lookup the grade letter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne wrote:

-1. Create a table named Score with fields name Grade and percent.
-5. Fill it in.

1. Open your form in design view.

2. Right-click the Percent text box, and choose Properties.

3. On the event tab, set the AfterUpdate property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Paste in the 3 lines of code from the previous thread.

 

[Joe M]

Thanks,
But when I run it, it says compile error "Sub or Function not
defined" for Me.Percent. How and where do I define it??

Yes, thanks for clarifying, Mike.

That is assumed: that you have a table with these 2 fields, so you can
lookup the grade letter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne wrote:

-1. Create a table named Score with fields name Grade and percent.
-5. Fill it in.

1. Open your form in design view.

2. Right-click the Percent text box, and choose Properties.

3. On the event tab, set the AfterUpdate property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.

5. Paste in the 3 lines of code from the previous thread.

 

[Allen Browne]

In your original thread, you say that Field1 is named Percent.
If it is named something else, substitute the actual name of your field.

 

[Guest]

An alternative to this solution is to place the DLookup function in the
control source for the Grade on your form. This requires that you create
another table with your grades and scores in it.

I called my new table "Grades". It has two fields, "AGrade" and "AScore".
To make it work, the first record had to match the worst grade, that is
record 1 contains the values "F" and "60". Starting with "A+" and "95" for
mathematical reasons didn't work with Allen's code.

Next, I set the Control Source for "Grade" on the form to:

=DLookUp("[AGrade]","Grades","[AScore] >=" & [Forms]![frmScores]![Percent])

I call my form "frmScores".

This code(?) looks up the grade in the table "AGrade" (I should have named
it tblAGrade but I was sloppy). It then compares the value of "Percent" in
your table to the form called "frmScores" and inserts the appropriate grade
in the field "Grade" in your table.

Like yourself, I am not a programmer. Allen's example got me started and
then I used the on-line help in trial-and-error mode.

David

 

[Guest]

Well Joe,

After taking lunch and trying this solution again it failed. Now I am don't
know why it worked the first time except perhaps for leaving some of Allen's
code in place. And before my lunchbreak, the "Scores" table wasn't being
updated anyways.

Sorry.

David

An alternative to this solution is to place the DLookup function in the
control source for the Grade on your form. This requires that you create
another table with your grades and scores in it.

I called my new table "Grades". It has two fields, "AGrade" and "AScore".
To make it work, the first record had to match the worst grade, that is
record 1 contains the values "F" and "60". Starting with "A+" and "95" for
mathematical reasons didn't work with Allen's code.

Next, I set the Control Source for "Grade" on the form to:

=DLookUp("[AGrade]","Grades","[AScore] >=" & [Forms]![frmScores]![Percent])

I call my form "frmScores".

This code(?) looks up the grade in the table "AGrade" (I should have named
it tblAGrade but I was sloppy). It then compares the value of "Percent" in
your table to the form called "frmScores" and inserts the appropriate grade
in the field "Grade" in your table.

Like yourself, I am not a programmer. Allen's example got me started and
then I used the on-line help in trial-and-error mode.

David

In your original thread, you say that Field1 is named Percent.
If it is named something else, substitute the actual name of your field.