Why does it not work!

[Martink]

Select * from tblGradeBoundaries Where SubjectReference = " & Me.Subject_Reference & ";

This is the code that is attached to the on update event.
I am trying to select a record from a given value from a drop down box. I just can not seem to get it to work.

Any ideas would be much appreciated.

Thanks a lot Martin

 

[Mile-O]

What data type is SubjectReference? Text? Numeric? etc.

Are you trying to put the SQL into a combobox's RowSource?

More information please!

 

[Martink]

More details

Subject reference is a text.
The sql is on the on update event of the drop down box Subject_Reference.

tblScript(CandidateID, SubjectReference, OriginalMark, OriginalGrade) This table contains more stuff but is of not consequence.
tblGradeBoundaries(SubjectReference, a, b, c, d, e, u)

Private Sub Subject_Reference_AfterUpdate()
Dim rst As Recordset
Dim sql As String
Dim dbs As database
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.OriginalMark >= rst!u And Me.OriginalMark < rst!e Then
Me.OriginalGrade = "U"
End If
If Me.OriginalMark >= rst!e And Me.OriginalMark < rst!d Then
Me.OriginalGrade = "E"
End If
If Me.OriginalMark >= rst!d And OriginalMark < rst!c Then
Me.OriginalGrade = "D"
End If
If Me.OriginalMark >= rst!c And Me.OriginalMark < rst!b Then
Me.OriginalGrade = "C"
End If
If Me.OriginalMark >= rst!b And Me.OriginalMark < rst!a Then
Me.OriginalGrade = "B"
End If
If Me.OriginalMark >= rst!a Then
Me.OriginalGrade = "A"
End If

Set rst = Nothing
Set dbs = Nothing
End Sub

Hope that helps

 

[Martink]

It only ever changes the grade from the first subject reference(01325), it does not pick out the right subject! There are several subjects with different grade boundaries.

I hope that you can understand my ramblings.

Thanks Martin

 

[Mile-O]

Re: More details

sql = "Select * from tblGradeBoundaries Where SubjectReference = " & Me.Subject_Reference & ";"
sql = "Select * from tblGradeBoundaries;"

First problem - you assign the SQL statement to sql and then totally reassign it in the next line.

MyName Is Mile-O-Phile
MyName Is Thor

"What is my name?"
Obviously, my name is Thor because the second statement cancelled the first.

The other thing with that first SQL statement is that text must be treated as a string so you'll have to put it within apostrophes.

sql = "Select * from tblGradeBoundaries Where SubjectReference = '" & Me.Subject_Reference & "';"

This line: sql = rst!SubjectReference has no relevance within your code.

Also, you'll find it a better practice to use the Select Case statement in your code rather than all those nasty IF...END IF statements.

Select Case Me.OriginalMark
   Case Is >= rst!u And < rst!e 
      Me.OriginalGrade = "U" 
   Case Is >= rst!e And < rst!d 
      Me.OriginalGrade = "E" 
   Case Is >= rst!d And < rst!c 
      Me.OriginalGrade = "D" 
   Case Is >= rst!c And < rst!b 
      Me.OriginalGrade = "C" 
   Case Is >= rst!b And < rst!a 
      Me.OriginalGrade = "B" 
   Case Else
      Me.OriginalGrade = "A" 
End Select

...or something like that!