Calculation in Unbound Text Box

[noboffinme]

Hi

I have two fields on a form, one is Owner (Text) & the other is Cost (Currency).

Each record will have a Cost value entered & the Unbound Text box should sum these amounts for that Owner as they are entered - There will be a separate Text box for each Owner.

Does anyone have any examples that can do this or is the only way to add a subform working on a query?

Thanks.

 

[mcalex]

hi noboffin

a bit confused as to where the cost items (that need summing) are coming from.

If they are being entered into text boxes on the form, then each textbox could have a LostFocus event that adds the textbox content to the totalCost

If they are coming from the database, you could just sum all the costs in your query.

hth
mcalex

 

[noboffinme]

Thanks msalex

I was hoping to use a formula in the unbound text box rather than use vba code.

You're correct as to the source of the cost, the user simply types them in & the total automatically increases for that 'Owner'.

Do you know if it can be achieved using something like;

(Sum([Cost])where[Owner]="Bill")

I realise the above won't work, but it illustrates what I'm trying to achieve, Thanks

 

[mcalex]

OK, think i've got it.
Your total textbox can be set to something like:

=[detailItem1]+[detailItem2]+[detailItem3]

etc
where detailItem# is the name of the text box with the item cost in it.
I'm not sure about your formula, coz the inputted item costs won't be 'owned' by Bill until the data is saved to the database. However, once they have been saved, you can do a query that includes a calculated total

hth
mcalex

 

[missinglinq]

I, too am a bit confused, but this sort of thing is, in fact, usually done with a second table displayed as a Subform.

The Main Form would contain an OwnerID, as the Primary Key, Owner's name and any other ID/Contact info on the Owner.

The Subform would contain the OwnerID as the Foreign Key and cost data.

This way allows you to enter multiple costs for a single owner without having to enter the OwnerID/OwnerName over and over again, and allows for as many or as few cost items as is needed, without having a separate field for each one on the record.

Linq ;0)>

 

[noboffinme]

Thanks mcalex & missingling

mcalex, I'm not sure how your solution will work as there's only one field called 'Cost' so to aggregate it, i would need to use 'Sum(Cost)' to sum any value under any record ID to display a total in the unbound text field.

I did start with Sum([Cost]) which works fine - what I want add is the where condition of a specific 'Owner'.

Can your solution factor this condition in?

Thanks missingling, I know how to add a form, but I want to know if I can do this with a formula I have very little room on the form, Cheers

 

[mcalex]

hi,

nice step-in missinglinq, i was visualising things wrong - thanks for the clues. (I gotta get better at this helping stuff)

noboffin: what Linq said is right. Sounds like you need a form with 'Owner' and 'Total' textboxes, and a subform with a 'Cost' box, and unless the data coming in can't be sorted, you don't want to be rekeying owner details again and again.

The form doesn't need to be much larger than the controls on it, so a form/subform solution won't take up much more space.

hth
mcalex