Help with code

[Martink]

Select Case [tblScript].[OriginalMark]
Case Is < [tblGradeBoundaries].[E]
[OriginalGrade] = "U"
Case Is < [tblGradeBoundaries].[D]
[OriginalGrade] = "E"
Case Is < [tblGradeBoundaries].[C]
[Original Grade] = "D"
Case Is < [tblGradeBoundaries].
[OriginalGrade] = "C"
Case Is < [tblGradeBoundaries].[A]
[OriginalGrade] = "B"
Case Is > [tblGradeBoundaries].[A]
[OriginalGrade] = "A"
End Select

I keep getting an error saying that it can not find the field.
I have no idea what to do. I can program in VB but not with databases! What am i missing or is my syntax wrong?

There are two tables tblGradeboundaries(SubjectRef, A, B,C, D, E) and tblScript(SubjectRef, OriginalGrade, OriginaMark). OriginalMark should fill in automatically. On my input form i have a button that adds the record to the database and this is where i want the code to go.

Please help me!

 

[WayneRyan]

Martin,

You don't say what context your code is in.

However,
[tblScript].[OriginalMark]
looks like you are trying to reference the table directly.

If your code is referring to values on a form, then your reference
should be something like: Me.OriginalMark.

If you have opened a recordset, then your reference should
be something like: rst!OriginalMark.

Please give more info and we'll get you started.

Wayne

 

[Martink]

All the coding is taking place in access so i have not personally connected to the database. The data is input into the table via a form, then when the recorded is added i want the grade to update itself depending on the subject and the mark.

I hope that this helps a little.

 

[WayneRyan]

Martin,

The first point: you should not store values in a database
that you can calculate. It will make things more difficult for
you in the long run.

Second you say that OriginalMark should fill in automatically
but in your code sample OriginalGrade is the field that you are
assigning to:

Select Case [tblScript].[OriginalMark]
Case Is < [tblGradeBoundaries].[E]
[OriginalGrade] = "U"
Case Is < [tblGradeBoundaries].[D]
[OriginalGrade] = "E"
Case Is < [tblGradeBoundaries].[C]
[Original Grade] = "D"
Case Is < [tblGradeBoundaries].
[OriginalGrade] = "C"
Case Is < [tblGradeBoundaries].[A]
[OriginalGrade] = "B"
Case Is > [tblGradeBoundaries].[A]
[OriginalGrade] = "A"
End Select

In your context, your references should be like:

Select Case Me.OriginalMark]
Case Is < Me.E
Me.OriginalGrade = "U"
.
.
.
End Select

hth,
Wayne

 

[Martink]

What is Me and is the ] ment to be there or should there be anothere one?

 

[WayneRyan]

Martin,


oops! One of those cut and paste errors:

Select Case Me.OriginalMark

You really only need the brackets if your field is named like
[With A Space]. Yours don't and the capital O and M are nice
things.

The Me is just a way of referencing controls on the current
form.

Me.txtMyTextBox
Me.Requery
Me.txtMyTextBox.SetFocus

are just some examples.

Forms![OtherForm]![SomeField] would reference [SomeField]
on an open form [OtherForm].

rst!SomeField would reference SomeField in an open
recordset.

Wayne

 

[WayneRyan]

Martin,

You're still referencing the table's fields directly in
your code.

If you can, post a zipped copy of your database.

Wayne

 

[WayneRyan]

Martin,

I did the following:

I renamed fields to RemarkScore and RemarkGrade. I don't
like having spaces and special symbols in field names.

I added the RemarkGrade field to your form.

I added the following in the AfterUpdate event of your
RemarkScore field:

' *************************************
Dim dbs As Database
Dim rst As Recordset
Dim sql As String
Set dbs = CurrentDb
sql = "Select * from tblGradeBoundaries Where SubjectReference = " & Me.Subject_Reference & ";"
sql = "Select * from tblGradeBoundaries;"
Set rst = dbs.OpenRecordset(sql)
rst.MoveFirst
sql = rst!SubjectReference
If Me.RemarkScore >= rst!u And Me.RemarkScore < rst!e Then
Me.RemarkGrade = "U"
End If
If Me.RemarkScore >= rst!e And Me.RemarkScore < rst!d Then
Me.RemarkGrade = "E"
End If
If Me.RemarkScore >= rst!d And RemarkScore < rst!c Then
Me.RemarkGrade = "D"
End If
If Me.RemarkScore >= rst!c And Me.RemarkScore < rst!b Then
Me.RemarkGrade = "C"
End If
If Me.RemarkScore >= rst!b And Me.RemarkScore < rst!a Then
Me.RemarkGrade = "B"
End If
If Me.RemarkScore >= rst!a Then
Me.RemarkGrade = "A"
End If
Set rst = Nothing
Set dbs = Nothing
' *************************************

While I was in the code window I added a reference to
Microsoft DAO 3.6 and elevated its priority as high as possible.

You can make the grade invisible or locked or whatever.

You can also set the checkboxes with code.

Wayne