How to update data thru query instead of using On Current() on form?

[awiezzz]

Hi,
I have query with data from tblStaff and tblEntitle. I've created form just to update the ANNUAL field for each staff. This is the code,

Private Sub Form_Current()
If YEAR_SERVICE > 10 Then    
Select Case Me.GRADE        
Case "E1", "E2", "E3", "E4"            
     Me.ANNUAL = 23        
Case "E5", "E6", "E7", "E8"            
     Me.ANNUAL = 23        
Case Else            
     Me.ANNUAL = 21    
End Select

ElseIf YEAR_SERVICE > 5 And YEAR_SERVICE < 11 Then    
Select Case Me.GRADE        
Case "E1", "E2", "E3", "E4"            
     Me.ANNUAL = 23        
Case "E5", "E6", "E7", "E8"            
     Me.ANNUAL = 20        
Case Else            
     Me.ANNUAL = 18    
End Select

ElseIf YEAR_SERVICE < 6 Then    
Select Case Me.GRADE        
Case "E1", "E2", "E3", "E4"            
     Me.ANNUAL = 20        
Case "E5", "E6", "E7", "E8"            
     Me.ANNUAL = 17        
Case Else            
     Me.ANNUAL = 15    
End Select
End If
End Sub

The ANNUAL field is updated but i need to go through by record (i have more than 1000 records). It waste my time. I tried to update this field directly at query but it doesn't work.
Any idea? thanx in advance..

 

[maxmangion]

try using an update query instead.

 

[Rich]

Since this is a calculated value you shouldn't store the result, just use a query to return the entitlement.