"Conditional total" field in a form

[natesternberg]

This is probably simple, but I seem to be stuck...

I have a form that displays data like this (based on a query):

[FONT=Courier New]Description VehicleType Cost[/FONT]
[FONT=Courier New]F150        TRUCK       $100[/FONT]
[FONT=Courier New]Miata       CAR         $80[/FONT]
[FONT=Courier New]Taurus      CAR         $70[/FONT]

VehicleType is a combo box, so the user can select one of the two values from within the form. I want a "total" text box in the footer that displays the sum of total cost of cars and the toal cost for trucks, and that updates as the user changes VehicleType.

It's the "as the user changes" part I'm having trouble with. I have the total set to a DSUM, and that gives the correct result, but only when the form first opens: it doesn't update as users change VehicleType.

So I tried adding an event handler to Cost.AfterUpdate that called tbTotal.Requery. That didn't do it either.

Any advice?

 

[missinglinq]

If the Control Source for the 'totals' textbox is where you have the DSum() it should be updating each time you add a record or change an existing record.

If you have the DSum() elsewhere you need to make it the Control Source.

Calculated controls like these should not be stored in tables, they should simply be recalculated as needed, so the Control Source should not be set to a field in your underlying table.

Also, DSum() has to be run against the Field of a Table, not against a Control on a Form,which is a common mistake people make.

Linq ;0)>

 

[Gregof1976]

I faced with simmilar problem and so far I didn't find right expresion to get the sub total on the forms.

Enclosed is draft of my data base.

Your advice will be appreciate.

Tahnks
GREG

 

[Gregof1976]

Once more I uploded file previous one do not including dsum expresion that works, but do not give me expected result like it is in the case of total working hours.

 

[Gregof1976]

I found expected solution. Expresion should be like this and then works:

=Sum(IIf([Shift]='Shft1';[WorkedHours];0))

 

[natesternberg]

I found expected solution. Expresion should be like this and then works:

=Sum(IIf([Shift]='Shft1';[WorkedHours];0))

Ah, that works for me too! And it updates correctly when I update records in the form. Thanks!