Updating a field from an unbound form

[AChir]

I have a form in which users enter exam marks for a group. In a subform the names are listed and in another sumform the Maximum marks and grade boundaries are given. Staff enter the marks and it converts them into grades using the % boundaries.

My problem is getting the command button (PreviewResults) to save the grade. I keep getting "type mismatch". I converted the database from Access 97 to 2000, if that matters.

The code I have is:

Private Sub PreviewResults_Click()
Dim y As Boolean

y = SaveGrades("Score", "MaximumPoints", "Grade")
' More odds and ends which seem to work - grading results into a report

End Sub

Function SaveGrades(Mark As Integer, Total As Integer, Grade As String)
'Allocate grades to appropriate records
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim MarkAsPerCent As Single

Set db = CurrentDb
Set rst = db.OpenRecordset("qryGroupResults")

'Start to loop through all records
Do
'Copy the current record into the copy buffer
rst.Edit
If rst(Total) <> 0 Then
'Update the grade and save it, then move on
Dim CurrentMark, CurrentTotal As Integer
CurrentMark = rst(Mark)
CurrentTotal = rst(Total)
MarkAsPerCent = (CurrentMark / CurrentTotal) * 100
Select Case rst(MarkAsPerCent)
Case Is >= [Forms]![Assignments].[CurrentA1]
rst(Grade) = "A1"
Case Is >= [Forms]![Assignments].[CurrentA2]
rst(Grade) = "A2"
‘ more of the same…
Case Is >= 0
rst(Grade) = "E"
Case Else
rst(Grade) = ""
End Select
Else
MsgBox "No grades will appear unless you indicate the total number of marks available", , "Missing data"
End If

rst.Update
rst.MoveNext
Loop Until rst.EOF
SaveGrades = True
End Function

I'd be grateful if anyone can help

Thanks

 

[AChir]

Just a quick addition here: I am desperate! ANY thoughts would be appreciated

 

[simongallop]

In the VBA editor goto Tools / References and make sure that 'Microsoft DAO 3.6 Object Library' is selected.

HTH

 

[AChir]

It is - although it is below the Access object library in the list of references. I read in a page of Dev Ashish's that I should(?) move it up in priority but they box I needed to uncheck would not play the game and I couldn't pursue it.

Might I be better to copy the whole database into a new database and then rewrite the code to accommodate Access 2000 from the start. If so, what would I need to do?

Thanks, Harry - it's a relief just to know someone else is thinking about the problem for me. I really need this working asap!

 

[simongallop]

As a wild guess (and sure to make a few graves turn!) remove the DAO bit from your declarations therefore leaving it as Dim rst as Recordset and Dim db as Database

 

[AChir]

That's how it started and it doesn't seem to make a difference. Is there another route I could try?

Thanks

Late update - I have cracked it... 4am now but at least it's done!
Thank you for the support