I can't update a field through VBA

[dealwi8me]

Hello all,

this is the code i have on beforeUpdate property of a form (record source of the form is a table called tblApplicant).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSQL      As String
Dim rs          As DAO.Recordset
    
    strSQL = "SELECT * FROM tblGrades " & _
                "WHERE [RecordID] = " & Me.RecordID & ""
                
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    While Not rs.EOF
     total = total + rs.Fields("total_score").Value
     rs.MoveNext
    Wend
   
 'me.total_score is a field of the tblApplicant  
 Me.total_score = total / 5
   
   Set rs = Nothing
End Sub

...the problem with the above code is that i get no value to me.total_score field.

Any ideas what might be wrong?

Thank you in advance.

 

[Bat17]

you have not Dim'd total which may be the problem. I would add a break point and step through the code checking values as you go.

Peter

 

[DB7]

As Peter said but, the code wouldn't run, if total wasn't declared.
I'm assuming it's public.

Possibly the event you're using.
I don't know what your underlying table is, tblGrades?
maybe the fields haven't been saved yet?

but i'd think you'd get "division by zero" error?

try Me.Refresh and Form_AfterUpdate?

 

[dealwi8me]

the problem is on the while loop. Even though i have more than one records with the same recordId i only get one record at the recordset.

any ideas what's wrong with the while loop?

 

[Bat17]

As Peter said but, the code wouldn't run, if total wasn't declared.
I'm assuming it's public.

Yes it will as long as Option Explicit has not been added to the module.
My concern was that Access may set it to null and null + anything is still null.
Access is usualy bright enough not to do this though

i only get one record at the recordset.

any ideas what's wrong with the while loop?

if you only get one record in the recordset then there is nothing wrong with the loop but with the SQL.

Try this to doublecheck the number of records

 Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    rs.MoveFirst
    MsgBox rs.RecordCount & " Records Found", vbOKOnly, "Record Count"
    While Not rs.EOF

HTH

Peter

 

[dealwi8me]

1. You should not be storing calculated values.
2. Don't use a code loop when a query will do the work for you. You could use a totals query to obtain the sum which would obviate the need for a loop.
3. Why are you dividing by 5? If you are trying to average something, use the Avg() function in a totals query.
4. Did I say that you should not be storing calculated values?

i used a total query with the avg fuction and works ok thanks, but i have a question.

I know i shouldn't store calculated values, but what's happening if i want to group my records based on the calculated values?