Apply Calculations to only Current Record of Access Continuous Form

I need to do some calculations on each record on a subform set up as a continuous form but have those calculations applied to only the current record.  For example if I have an order form with detail records for the subform and an order total, I need the subform to calculate totals based on qty of each detail line independent of the other detail lines.
yoducatiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
I am not quite visualizing what you want.

You have a continuous subform.
On each line of that subform, you want controls that calculate something for that line only?
So lets say that each line has Qty and Rate, and you want Extended:Qty * Rate, is that right?

If that is right, there are two ways to get after it.
One is in the query driving the subform.
Create your calculation there
Extended:[Qty] * [Rate] or whatever
And then set the control's ControlSource to Extended
The other is on the subform.
Set the ControlSource of the calculating control to
=[Qty] * [Rate]

Qty and Rate being theoretical names of fields and controls respectively, and Extended being an alias in the query
0
Jeffrey CoachmanMIS LiasonCommented:
I am not quite visualizing what you want.
Neither am I...
It is always best to first state "why" you need this functionality...

To me, ...the sub form should behave in this fashion already.
In the subform you will have the product and the qty.
So you will then also have a control that multiplies the two (as Nick stated above)
This will give you a total for just that line item (record)
...To me, that would meet your requirement of:
totals based on qty of each detail line independent of the other detail lines
In other words, I am not quite sure I understand why the other totals are "interfering" with each other.

Would it be simpler to just create a single form view (instead of a continuous form?)?

All of this would be clearer if you could post a sample database illustrating this issue, ...then post a clear graphical example f the exact output you are expecting...

JeffCoachman
0
yoducatiAuthor Commented:
That's sort of what I want.  The complication is that on the detail subform the rate is calculated in another query depending upon what is ordered and when its ordered.  When the user selects the drop down on the subform for what he wants the "amount" is calculated based upon the item he selected, the date of the order, and the quantity.  I have that working, the problem is it applies that calculated amount to the current record and the next record on the subform as well.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

yoducatiAuthor Commented:
Everything would be more clear if I could post a sample database, unfortunately because of the nature of the work I can't.  Let me try to come up with a better explanation.  I don't know if it matters but I am not simply trying to calculate the amount for a report or static screen, its for a data entry form that calculates as the entries are made.  So if you imagine a simple order form with a unique order number, and a subform with the detail records for that order, as the user is placing their order when they select the item and quantity the "amount" will be calculated for each line.  Right now the calculation is correct but its being applied to more than just the current record.

A Cost is $10
B Cost is $20
C Cost is $30

Order #123456

Item             QTY             Amount
A                    1                     $10
B                    2                     $40
C                    1                     $30

                        Order Total  $80

That's the way it should work.  Right now when I enter line Item A the amount is calculated correctly but is applied to line item B and any other line items I add to this subform.  I read somewhere that its because its a continuous form and I need to be able to isolate the current record not just the field name.
0
Nick67Commented:
Right now when I enter line Item A the amount is calculated correctly but is applied to line item B and any other line items I add to this subform.

Ding Ding Ding!
We have a winner!
Unbound control on continuous form
http://www.experts-exchange.com/articles/6692/Overcoming-unbound-continuous-forms-limitations-by-staging-data.html

<shameless plug>
Have a look and be nice to me :)
If this post gets some credit as an assisted answer in the end, then my article gets some kudos, too
</plug>

That CANNOT work.
Controls on continuous forms HAVE to have ControlSources.
I've lined out two ways.
The article lines out more.
Have a read, and then get back to me.

Nick67
0
yoducatiAuthor Commented:
Ok what if I want to calculate AND store the amount?  I'm pretty sure I've done this before even though it was on a continuous subform.  I have a query that calculates the "price" for each item and will only ever have one record based on the criteria.  On a continuous form can I simply leave off the "price" as it is calculated anyway, allow the user to change the quantity which is bound, and have Amount which is also bound calculated by multiplying the two? or is it no longer a bound control because its calculated? I was thinking that the bound controls could be calculated as long as they had a place to store the data.
0
yoducatiAuthor Commented:
When I say I've done this before it was with help from this site and there was some sort of code to check each record but it was relatively straightforward.  I think the same caveat applied though it had to be a bound control as opposed to just an unbound calculated field.  I want the amount to be calculated and stored so the user doesn't have to think about it.  Maybe I am confusing bound and control source stuff.
0
Nick67Commented:
The definition of a bound control is that there is something in the ControlSource property.
That something can be straight text like ="hello"  although who needs one control per record that says "hello" !?!
It can be a valid fieldname or alias from the underlying query like Extended, or SomeField from the ControlSource property dropdown.
It can be any valid expression created with functions and control names from the form like =Round([qty]*[rate],2)

A fun one is that it can be a function =SomeCustomFunction() or even  =SomeCustomFunction([qty],[rate]) if your function took arguments.

I want the amount to be calculated and stored so the user doesn't have to think about it.
Ok.
Then you have to calculate the value and commit it to a table and then requery a control bound to the place you stored it.
Generally though, we don't as Access guys store calculated values.  Because the calculation may change later, and then what?

I think you're probably looking at bind controlsource to a custom function as a best option.
The function needs to be Public and go in a code module, most likely.

Work out a function on the form that takes values from the form and calculates correctly.
Then change the form values into arguments for the function. and feed it in like I mentioned.
If you can't get it, if you can create a sample .mdb with a dummy form and dummy data for us to tweak, that'd be great.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yoducatiAuthor Commented:
I was typing this as you responded.  I'm headed home for the day but I will try to work something out for a dummy version tomorrow.  This is what I was thinking so I think I'm in line with what you are suggesting although maybe not the best function

So with the same example, the main order form with the detail records,

Item A costs $10 (cost calculated from a separate query but not displayed or stored in form)



Item (user selected and stored)          Quantity  (user entered and stored)                         Amount (Calc. and Stored)
                                   
A                                                             5                                                               DLookup([Price],[qryPricing]) * [Quantity] = 50
0
Nick67Commented:
DLookup() is a performance issue waiting to happen and should be avoided.
Now,
Item A costs $10 (cost calculated from a separate query but not displayed or stored in form)
Somewhere along the line the ingredients of this calculation for this particular transaction should be stored -- or perhaps the results of the calculation might be a good candidate for storage.
Even though we generally don't store calculated values, for some stuff like inventory valuation at a point in time, it can make sense.  Say for gasoline inventory value.  The price of that bops around, and so does the quantity in storage, so to save price, quantity and price * quantity as value when associated with a date can make sense.

Invoicing sales can be like that, too, because prices are not static and the calculation can be complex and changeable (SALE PRICES!! TODAY ONLY! -- who wants to code for every time a sale happens -- between now and eternity?)
So, there really wouldn't be anything wrong with adding a field to whatever table stores Item and Quantity called LineItemExtended.  Both the control for Item and Quantity would have an AfterUpdate event that would take Price * Quantity if both were non-null, numeric values and put that value in the table's LineItemExtended field and requery the control bound to LineItemExtended.

Over time, you might be glad you did it that way.
Calculating amounts extended is a nice idea -- but boy can there be hell to pay when you want to do something like sum sales by year!
0
yoducatiAuthor Commented:
Ok here is a demo of the form.  The item number is filtered based upon the vendor selected on the main form showing only items offered by that vendor.  The unit price is determined by the result of the qryPriceLookup based upon when the order is placed.  All the prices are entered up front in the pricing table and then depending upon the date of the order the system assigns the corresponding price.  That unit price from the query should be multiplied by the quantity entered and assigned as the amount for that line item only.  I have all of this working except that the amount ends up being calculated for each record as opposed to only the current record. The order total would obviously be the total of all the line items for that order.  In the event a line item does not have a fixed price the user will have to enter a bid number to establish the amount for that line item.  I think if we can get it working for the fixed price items I can apply the logic to the others.
Demo.accdb
0
PatHartmanCommented:
I didn't look at the demo but Nick's original suggestion to calculate the total in the subform's RecordSource query is the correct solution.  It is NOT necessary to store the result since the fields necessary to calculate it are in the same record.  Any time you need to see the total, add the calculated field to your query and you will always have the current value.
0
yoducatiAuthor Commented:
I guess Im not sure how to get the value for [UnitPrice] which is calculated by another query which runs based upon user input from the order form, into the query for each record on the subforms recordsource.
0
Nick67Commented:
Can you make your sample an .mdb.
I cannot run .accdb until after 7pm MDT
0
yoducatiAuthor Commented:
It says it cant save to an earlier version because it uses features that require the current format.  Any creative things I can do to make it convert?
0
Nick67Commented:
Not that I can tell you not looking at it :(
You can try creating a new blank mdb
Open your sample and try to export all of the objects to the new file
Whatever one is the problem (if there is one) will knock you on the head

Attachment fields
Multi-values fields
new-style switchboard

Those are the biggies
0
yoducatiAuthor Commented:
It didn't give me any errors.  Here it is.
Database3.mdb
0
yoducatiAuthor Commented:
Any luck with this?  I've been playing with the sample database you provided trying to figure out how to make it work for me.  I understand the logic but I still don't understand how to apply it to my case.  All I want to do is create a simple order form.  If you were going to order a pizza you would have an overall order with detail lines for toppings, amount of toppings, line item price, line item total, and an overall order total that adds up the price of all the detail lines.  Is there really no good way to do this in access?  When would anyone ever want a continuous form to work the way it appears to in access by default?
0
Nick67Commented:
When would anyone ever want a continuous form to work the way it appears to in access by default?
If you mean that an unbound control on a form taking the same value when set by code on every line of a continuous form, the answer is never

Your sample blew up continuously on me.
I have it going, but it doesn't have enough data in it for me to tell what you want to accomplish.
Can you refine the attached file?
yoducati1.mdb
0
yoducatiAuthor Commented:
Sorry about that.  I had a tough time making a sample.  I wish I could just upload the real thing.  But here is a new wrinkle for you.  It works now.  I didn't change anything on the forms from what I had before other than I made a copy of the order form and made that copy of the form unbound as well as all of its controls.  I was trying to step through building a temp table and unbound order form that would commit  the records to the order table when done.  I gave up and went back to my original form and now the detail section works.  Its a continuous form, each detail has a price, quantity, and extended amount (calculated but not stored) and it works perfectly.  I am very confused now because although its doing what I want I know its not supposed to be doing this.
0
Nick67Commented:
If you look carefully at the problem control, you will see that -- if it is working -- that ControlSource has something in it.
So the control is now bound, and can show a different value for each record.
and extended amount (calculated but not stored) and it works perfectly.
You have apparently made the calculation the ControlSource.

In broad terms:
Let's say I have a subform with a control called Extended on that has no ControlSource.
And on the main form I have a button call Calculate.
And Calculate_Click is

With rs
    .AddNew
    !qty = me.qty
    !Rate = me.rate
    .Update
End with
Me.mySubform.Form!Extended = Me.qty * Me.Rate

This will misbehave.
Extended is unbound
Any value assigned to it shows on every record in the continuous subform.

Now let's say that I have the .ControlSource of Extended be "= [qty] * [Rate]"
Because the subform has two controls called qty and Rate on it.
This will work swimmingly because the control is bound, and it will do the calculation for each row's qty and Rate.

Make Sense?
0
yoducatiAuthor Commented:
That part does.  I just don't really understand why the fields I had before wouldn't work until I added another field that was calculated but not stored.  In other words I had the field "Amount" and "quantity" before and the calculation was correct for the first detail line, but as you know when I added a second detail it would carry that amount down to the new detail.  What does adding the new calculated field "extendedAmount" have to do with the original amount?  I guess I just don't understand why adding that field fixed the other problem.
0
Nick67Commented:
Without seeing everything or screenshots, it is hard to say.
But perhaps in your subform, you had Extended bound to
=Me.Parent.qty * me.Parent.rate
Now, that would make Extended change each time the parent's controls values changed, and they'd all be the same.
0
PatHartmanCommented:
Unbound controls don't store data.  Tables store data.  Bound controls pull data from tables.  Unbound controls have only one value no matter how many rows are visible.

Look at your continuous form in design view so you will understand the problem.  Notice that no matter how many rows the form shows in data sheet view, it shows only a SINGLE row in design view.  That means that there is only one set of properties for all the controls in the form no matter how many rows are visible.  Change the color of a label.  It will change for ALL rows.  The unbound control has only the single ControlSource defined for the form.  That is why is always shows the same thing on every row.  You are simply looking at a copy of the control.  Whereas the bound controls pull data from the tables so they can show different values for each row.

If you want Access to remember something, it MUST be stored in a table or calculated in a query.

The calculated field in a query works because the query is instantiated in memory and so it can keep a separate value for each row.
0
yoducatiAuthor Commented:
I think before I was trying to do too much in one field.  I still need to make the amount pre populate depending upon whether or not what is ordered has a fixed price, but regardless its still working fine as far as calculating the extended amount which is [Quantity]*[Amount] and its not duplicating that figure for each row.  I have a new problem now with a primary key error which it wasn't doing before so Ill ask another question. Thanks to you both for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.