Grade and Percent fields.

  • Thread starter Thread starter Joe M
  • Start date Start date
J

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.
 
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
 
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.
 
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 wrote:

-1. Create a table named Score with fields name Grade and percent.
-5. Fill it in.
 
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.

Mike Painter said:
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.
Click to expand...
Click to expand...
 
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??



Allen Browne said:
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.

Mike Painter said:
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.
Click to expand...
Click to expand...
Click to expand...
 
In your original thread, you say that Field1 is named Percent.
If it is named something else, substitute the actual name of your field.
 
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
 
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

roccogrand said:
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


Allen Browne said:
In your original thread, you say that Field1 is named Percent.
If it is named something else, substitute the actual name of your field.
Click to expand...
Click to expand...
 
Back
Top