Solved VBA codes to display total expenditure year wise on a form

[sbaud2003]

Hi all
I have a form "Linked DV" where i want to display the Total expenditure on a text box TXTEXP from the table "ECS" having a field "Chqamt" after clicking a command button CMDEXP.
is it possible to do using record set.

 

[cheekybuddha]

>> is it possible to do using record set. <<

Yes, but also unnecessary!

Make the ControlSource of your textbox TXTEXP:
=DSum("Chqamt", "ECS")

 

[theDBguy]

Hi. It might be possible to just simply use the DSum() function. Have you tried it?

Edit: Ah, too slow...

 

[sbaud2003]

>> is it possible to do using record set. <<

Yes, but also unnecessary!

Make the ControlSource of your textbox TXTEXP:
=DSum("Chqamt", "ECS")

thanks ....its fine
but if I want to put a condition where the financial year is controlled by a combo box "COMFY" and category of expenditure as "COMCAT'
is it feasible.
I was trying with the code:
Dim Sum
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("ecs")
Set rst = qdf.OpenRecordset

rst.MoveLast
rst.MoveFirst

While Not rst.EOF
Sum = Sum + rst("chqamt")
rst.MoveNext
Wend

Me.TXTEXP = Sum

 

[cheekybuddha]

=DSum("Chqamt", "ECS", "Category = 'COMCAT' AND FinancialYear = " & Me.COMFY)

You need to adjust the field names for Category and FinancialYear to match the field in your table.

Hi DBG! 🖐

 

[sbaud2003]

=DSum("Chqamt", "ECS", "Category = 'COMCAT' AND FinancialYear = " & Me.COMFY)

You need to adjust the field names for Category and FinancialYear to match the field in your table.

Hi DBG! 🖐

the text box rmain blank

 

[cheekybuddha]

More info please!

What is the name of the category field in query 'ECS'?

What is its datatype?

What is the name of the financial year field in query 'ECS'?

What is its datatype?

Is combobox 'COMFY' on the same form as textbox 'TXTEXP'?

What is the SQL of query 'ECS'?

 

[sbaud2003]

More info please!

What is the name of the category field in query 'ECS'?

What is its datatype?

What is the name of the financial year field in query 'ECS'?

What is its datatype?

Is combobox 'COMFY' on the same form as textbox 'TXTEXP'?

What is the SQL of query 'ECS'?

category (in qry ECS) data type text
finyear (in qry ECS) data type number
yes both are on the same form

 

[cheekybuddha]

So did you try:
=DSum("Chqamt", "ECS", "category = 'COMCAT' AND finyear = " & Me.COMFY)
?

 

[cheekybuddha]

Or is COMCAT also a textbox ?

 

[cheekybuddha]

Probably you need:
=DSum("Chqamt", "ECS", "category = '" & [COMCAT] & "' AND finyear = " & [COMFY])

 

[cheekybuddha]

 

[sbaud2003]

=DSum("Chqamt", "ECS", "Category = 'COMCAT' AND FinancialYear = " & Me.COMFY)

You need to adjust the field names for Category and FinancialYear to match the field in your table.

Hi DBG! 🖐

Probably you need:
=DSum("Chqamt", "ECS", "category = '" & [COMCAT] & "' AND finyear = " & [COMFY])

THANKS I HAVE REMOVED THE RECORD SET AND ITS FINE...
PLEASE HELP ME OUT IN THE SAME LINE
IF HAVE ANOTHER CONDITION LIKE MONTH CONTROLLED BYCOMBOBOX COMMNTH CAN IT BE DONE ...
I MEAN CAN IT BE DONE WITH THREE CONDITION (THE FIELD IS A INTEGER LIKE 1,2 3 AS JANUARY FEBRYARY)

 

[cheekybuddha]

I MEAN CAN IT BE DONE WITH THREE CONDITION

Yes. Just add in the extra condition:
=DSum("Chqamt", "ECS", "category = '" & [COMCAT] & "' AND finyear = " & [COMFY] & " AND [month] = " & COMMNTH)

The third argument of the DSum() function is the condition/filter expression. It is the same as a WHERE clause in an SQL query (except without the word 'WHERE'!)

If the field for month in query ECS is called 'Month' then it must be surrounded in square brackets because 'Month' is a reserved word in Access/SQL.

If the field for month in query ECS is called something different then replace [Month] in the expression with the actual field name.

hth,

d

 

[sbaud2003]

Yes. Just add in the extra condition:
=DSum("Chqamt", "ECS", "category = '" & [COMCAT] & "' AND finyear = " & [COMFY] & " AND [month] = " & COMMNTH)

The third argument of the DSum() function is the condition/filter expression. It is the same as a WHERE clause in an SQL query (except without the word 'WHERE'!)

If the field for month in query ECS is called 'Month' then it must be surrounded in square brackets because 'Month' is a reserved word in Access/SQL.

If the field for month in query ECS is called something different then replace [Month] in the expression with the actual field name.

hth,

d

Thanks a lot I got it now.