Getting Total of a field in a continuous form (1 Viewer)

[Naqibullah]

Dear Valued colleagues,
Hope each one of you are doing all well, please help me to solve a problem, let me explain my situation:
i have three tables (Group, Customer, Savings).
every member of a group do savings once every 15 days and each group has maximum 20 members, group has one to many relationship with customer and customer having one to many relationship with Savings (there is no direct relationship between group and savings) i have created a continuous form for saving entry. in this form i select a group which is unbound and based on that group, its members are listed in the combo box named CusID, after selecting customer other saving details are entered.
What i want, is that for every group i want a total saving for all that specific group's members selected in the mentioned unbound combo box in that continuous form, this total should be based on the group selected in the mentioned unbound combo box, in case i select another group the total should also be based on newly selected group

 

[Naqibullah]

I have used Dlookup function with a query name fixedTotal, but it doesn't work...
in this query

TotalFixed is the name of unbound field showing total of SumofFixed in the query fixedTotal having group_ID same as it is selected in the form and SavingDate is the same date selected in the form.


TotalFixed = DLookup("[SumOfFixed]", "[FixedTotal]", "[Group_ID]=" & [Forms]![maintbl]![Groups] _
& " and [Date]= #" & Forms!maintbl!SavingDate & "#")

 

[JHB]

Is [Forms]![maintbl]![Groups] showing a number?
If not then try the below:

TotalFixed = DLookup("[SumOfFixed]", "[FixedTotal]", "[Group_ID]=[B][COLOR=Red]'[/COLOR][/B]" & [Forms]![maintbl]![Groups] _
& "[B][COLOR=Red]'[/COLOR][/B] and [Date]= #" & Forms!maintbl!SavingDate & "#")

 

[Naqibullah]

Dear JHB,
Is [Forms]![maintbl]![Groups] showing a number? this line returns a number that is groupID, in this dlookup i am asking return sumofixed column where group groupID is same as the group selected in the form and date is same as the date entered in the form, this function worked after making few changes, i have placed this function on the after update event of fixedamount field of this current continuous form, but it does not colculate the first record i enter and colculates other values, for example if i enter 200 for the first record, 200 for second record and 200 for the third record, it ignores the first record that is 200 and gives me total 400, plz help me solve that

 

[JHB]

What if you close the form and go into it again, (what I'm thinking of is if the data is written to the table when you do the calculation, if not then the last entered value isn't in the result)?
Create a query with the same criteria, do you get the correct result then?

 

[Naqibullah]

Dear JHN,
thanks for the reply, i have tried the steps you told me, but didn't work, so finally i have place this code on the on click event of a button(at the end of form i have created a button named total for this group after entering one group's data, and pressing the button, it works, i wanted to use it after update event of fixed amount field so that i should see status of entry dynamically rather than pressing a button and seeing the final total.

 

[JHB]

If it works with a button then I can't see why it not should work in the after update event, (ofcause if you've some other code running after the update or a requery, then it is another case).
What happen if you call the button's code in the after update event?
Else post your database with some sample data + the name of the form, (zip it).

 

[Naqibullah]

Dear JHB,
thank you for your continuous support, i have called the button on which i have set the code on the after update even of fixedSaving but the same problem is there it does ignore one record...

 

[JHB]

Post your database with some sample data + the name of the form, (zip it).

 

[Naqibullah]

Sorry for a short delay as i was out of the country,
please find attached in this post my db, in location filter you can select from province section kabul, from district section again kabul kabul and from subdistrict select district 6, some other improvements i would also like to bring but wasn't able, i have attached a screenshot explaining improvements i want...

Regards

 

[JHB]

Replace your code with the below:

Private Sub Text21_AfterUpdate()
Me.Dirty = False
TotalFixed = DLookup("[SumofFixed]", "[FixedTotal]", "[Group_ID]=" & Me.[Groups] _
& " and [date]= #" & Format(Me.Date, "mm/dd/yyyy") & "#")

End Sub

 

[Naqibullah]

worked, thanks

 

[JHB]

You're welcome.