Set value of field on form as sum of values on subform

[Robbo1974]

Have posted this one before but no responses and I'm having a real 'mare with it!

I have a form holding contact information which has a subform which displays orders. This orders subform has it's own subform which displays order details. What I want to do is set the value of the field [MainForm]![OrdersSubform].Form![TotalOrderValue] to equal the sum of all the instances of [MainForm]![OrdersSubform].Form![OrderDetailSubform].Form![ItemValue] for that particular order (grouped by [MainForm]![OrdersSubform].Form![OrderID]). In most cases there is just one item in the order but occasionally the number of items can be up to 10.

The problem I have is that [Form]![OrdersSubform].Form![OrderDetailSubform].Form![ItemValue] is actually a calculated control ([Quantity]*[UnitPrice] set via a macro attached to the after update event of [UnitPrice]). So when the macro runs [ItemValue] is updated but the after update event is not triggered. I have tried to run a procedure from the field's on change event but this doesn't seem to work either.

At the moment what I'm having to do is use a query to find the sum of [ItemValue] for each order, then making a table from that query and then basing an update query on that table (I'm having to go through this rigmarole twice a week at the minute).

My skill level with VBA is pretty basic and I have trouble writing anything other than simple procedures - can anyone help me out with some code I can steal!? x-) Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to
produce bigger and better idiots. So far, the Universe is winning.

 

[ide]

I'm sorry, I dont see your problem clearly (my English...)
"[Quantity]*[UnitPrice] set via a macro attached to the after update event of [UnitPrice]"
- Why? I suppose in the [OrderDetailSubform] has both [Quantity] and the [UnitPrice] fields. In the additional [ItemValue] control why not enough a "=[Quantity]*[UnitPrice]" ?
- If [OrderDetailSubform] don't has a form header insert it and on it insert a [SumItemsValue] field with reference "=sum([ItemValue])" If you dont want to see the header and [SumItemsValue] control set these height to 0.
- After
Forms![MainForm]![OrdersSubform].Form.[TotalOrderValue] =
Forms![MainForm]![OrdersSubform]![OrderDetailSubform].Form.[SumItemsValue]

i hope i understand your question right
ide

 

[ide]

if you don't want an error message or 0 in [ItemValue] field insert and invisible [ItemValue0] field with "=iif([Quantity]>0;[Quantity];0)*iif([UnitPrice]>0;[UnitPrice];0)"
and the [ItemValue] set to
"=iif(isempty([Quantity]) or isempty([UnitPrice]);"";[ItemValue0])"

the [SumItemsValue] field:
"=sum([ItemValue0])"

 

[jebry]

Hi!

Since Item Value isn't a bound control, it will not have a before update or after update event. Would it be possible to do all of you coding in the after update event of the unit price control? Alternatively, if the user will commonly go back to the main form before exiting the record, you can use the OrderSubform or OrdersDetailSubform exit event on their parent form.

hth
Jeff Bridgham

 

[Robbo1974]

Hi ide, thanks - was quite helpful but still a few problems! I had already set [ItemValue] to [Quantity]*[UnitPrice] and [Quantity] and [UnitPrice] are both required fields so there is no need to handle 0 values so that has kept things a bit simpler.

The main problem is that [SumItemsValue] does not update unless I tab through to a new record. I have tried using the requery action in the macro which runs on [UnitPrice]'s after update event but this just returns the error You can't use the apply filter action on this window ! When I try to do it from VBA on the [UnitPrice] Lost Focus event nothing happens at all. I'm using the syntax ...[SumOfValue].requery

Also [TotalOrderValue] is not updating at all properly - each time I add a new item it gets set as the old value of [SumItemsValue]. This is the other reason that i'm trying to requery [SumItemsValue] before setting the value of [TotalOrderValue]. I can't understand why I cant use the requery action in the macro.

jebry, unfortunately the user will not be swopping back to the main form until they are done with putting in the order so I do need to keep everything in the after update event of [UnitPrice]. Ideally for user friendliness I want things to update instantly - i.e. bang after they tap in the unit price and press enter. x-0 Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to
produce bigger and better idiots. So far, the Universe is winning.

 

[rbowes]

Hey Robbo,

Try this: in the macro that calculates the [ItemValue] control place another action: Set Value Item:[MainForm]![OrdersSubform].TimerInterval Value: 800

Then create another macro (or in code, which is prefereable) in the ON Timer event for the main form
Action: Set Value Item: MainForm]![OrdersSubform].Form![TotalOrderValue]
Value: [MainForm]![OrdersSubform].Form![OrderDetailSubform].Form![ItemValue]

Action: Set Value Item:[MainForm]![OrdersSubform].TimerInterval Value: 0

Essentially, what you do is let the macro calculate the value for ItemValue and then you set the main form's timer to 800 miliseconds. At that time it runs the next macro which sets the value of the subform's control. and then turns the main form's timer off.

I ran into this problem yesterday and this was a good solution. If 800 is too long it can be adjusted down.

Hope that helps.

Bob

 

[ide]

I created a form with 3 control:
Text0, Text2, Text4
and insertd code to form:

Function CalcText4()
CalcText4 = Me.Text0 * Me.Text2
End Function

Private Sub Text0_Exit(Cancel As Integer)
Me.Text4.Value = CalcText4()
End Sub

Private Sub Text2_Exit(Cancel As Integer)
Me.Text4.Value = CalcText4()
End Sub

You need not to tab the next record, just the next field.

 

[Robbo1974]

Thanks Bob but no joy! Here is the sequence of what I am doing, perhaps it will help.

Enter [Quantity] type value, press enter. Focus moves to [UnitPrice]

TYpe Value, press enter. Focus moves to another control, [SumOfValue] does not update

Tab through the remaining controls to a new record. [SumOfValue] updates, [TotalOrderValue] does not.

enter a new item. On pressing enter in [UnitPrice], [TotalOrderValue] is updated to the old value of [SumOfValue], [SumOfValue] updates.

Incidentally the timer attempt produces an error when you try to set the value via a macro. (Value not valid for this field - access automatically brackets timerinterval when I try to refer to it so it thinks it's a field on the form I reckon)I guess I should learn VBA! (They won't even give me web access at my desk so the chances of getting them to pay for a course are zero though!) :-( Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to
produce bigger and better idiots. So far, the Universe is winning.

 

[Robbo1974]

Why is it that you think of all the complicated stuff first and then find a really simple answer? The solution was staring me in the face - Northwind. It's funny but with the amount of really useful stuff in there you'd think i'd look at it more often.

All I had to do was use the correct syntax in setting [totalordervalue]'s control source property.

ControlSource =[OrderDetailsSubform].Form![SumOfValue]

I've solved the instant update problem by removing the tab stop from two controls which didn't require it anyway (details!) so that as soon as I press enter on updating [UnitPrice] the focus moves to the first field of the next record - [SumOfValue] then updates.

Thanks for all of your help, it went a big way towards pointing me in the right direction. Since finding this forum I have found it a lot easier to solve my problems! ) Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to
produce bigger and better idiots. So far, the Universe is winning.