I need to display the Monthly Payment by selecting a Month combo box satisfying condition of Financial Year and Section code (office code)

[sbaud2003]

I am using the code below: getting error (3021) no current record"
Click event:

Dim sql5, Sum
Dim FIN, CODE As Long
Dim MAS As String
FIN = Me.COMFY.value ' NUMBER
CODE = Me.TXTUC.value 'NUMBER
MAS = Me.CMPM.value 'TEXT
Dim rs As DAO.Recordset

sql5 = "SELECT * FROM [TOTAL CMP] WHERE ([section] = " & CODE & " and [finyear] = " & FIN & " and [mnth] = 'MAS' );"
Set rs = CurrentDb.OpenRecordset(sql5)
rs.MoveLast
rs.MoveFirst

While Not rs.EOF
Sum = Sum + rs("chamt")
rs.MoveNext
Wend
Me.TCMPM = Sum
end sub

'''''''
TCMPM is a text box which will display the result
chamt is the field name of amount in the "TOTAL CMP" query

 

[pbaldy]

Well, your recordset is likely empty and these lines would cause an error in that event (and aren't necessary anyway):

rs.MoveLast
rs.MoveFirst

Also, you need to concatenate the last variable as you did the others.

 

[plog]

You've reinvented the built in DSum(https://www.techonthenet.com/access/functions/domain/dsum.php) function. I advise not using recordsets and using that instead. Might not even need VBA code, just put it on the control source of where you want it to show.

 

[pbaldy]

Good point plog, I didn't pay attention to what the code was doing.

 

[Gasman]

You've reinvented the built in DSum(https://www.techonthenet.com/access/functions/domain/dsum.php) function. I advise not using recordsets and using that instead. Might not even need VBA code, just put it on the control source of where you want it to show.

O/P has already been advised to use DSum() in this thread https://www.access-programmers.co.u...ture-year-wise-on-a-form.311417/#post-1692767