Can't set value of control on main form when in subform

[bdoiron]

I have a main form and subform (Invoice Header and detail).The detail consist of a Item,quanity,sell price and extended price. All items are entered into fields in the underlying query except Ext price which is a calculated field. In the bottom of the header I have a subtotal control (Calculated) a state tax rate field and a state tax amount (Both fields in main form query). As the user adds rows in the subform, the subtotal field is automatically updated because it is calculated. HOwver the state tax amt field is not so I am trying to run VB code to update the sales tax amount after each detail record is updated. I tried using (Forms![Invoices]![StateSalesTaxAmt] = Forms![INvoices]![SalesTaxRate] * Forms![Invoices]![Subtotal] on the after update property on the subform. For some reason this does not work. I even created 2 queries that did the same thing and it worked. Only problem is When I do it I have to repaint the form before the values will show which brings the record pointer back to the first row which is not good on a long invoice. I then added gotolastrecord in VB this worked great when you were adding a new invoice. However when you edit an invoice and start to add lines, the same code runs but it goes back to the first row in the subform which makes it hard on the user. The only diffrence I see is that the main form when opened from the ADD button it is in data entry where as the edit button opens it in filtered mode because you need to edit the invoice you picked from the invoices combo box.

What am i doing wrong I would appreciate any help with this. Also I could ftp the code to someone to give them more to look at to solve this problem.

Thanks

 

[d_swapnil14]

hi
plz describe the procedures or functions in your coding in details so we can work out and make it error free.


Swap

 

[SydneyGeek]

One way to do this involves no code at all.
1. Go into Design view in the subform (open it free-standing).
2. In the FOOTER, add a calculated field (Unbound) as follows:
Call it ExtendedTotal. Add the expression =sum([quantity]*[Unitprice]) to the Control Source. Save and close.
3. Now, in the main form, you can grab the subform's ExtendedTotal into your Subtotal control. Something like:
=Forms![Invoices]![ExtendedTotal]

Denis

 

[mdmilner]

Here's a good reference that got me started understanding the relationships.

http://www.mvps.org/access/forms/frm0031.htm

Mike

 

[bdoiron]

Let me clarify another way...

Main Form Invoices: Customer AB Bolt
Address 100 Oak .

Subform:

PartnoCombo Descr QtyShipped SellPrice Extprice
ABXX100 RAGS 10 10.00 100.00
ABC200 Bolts 1 5.00 5.00

Subform Footer 105.00
------------------------------------------------------------------

Main Form SubTotal(Footer) 105.00
Sales Tax 10.00% 10.50 (Bound StAmt)
(Can Override)
Total 115.50 (UnBound)

What I am having a problem with is making the subtotal, Stamt, and Total get refreshed when the user is in the subform adding records. These fields should be changed when in the subform when (1)Adding a Partno in the combobox because qunaity of 1 and sellprice is set on after update. (2) when you change the quanity, (3) Override Sell Price. What code do I write on the after update properties of all 3 places that will update the StAmt because it is a field in the underlying query. Basically I need all mainform amounts to change when certain subform amount are changed becuase this is what makes up to Total.

Is this any clearer.

Thanks if anyone can help me with this. I am pulling my hair out

 

[SydneyGeek]

You could try...
on the AfterUpdate event of the subform, put in a Requery for the control on the main form. Something like --

 Me.Parent![ControlName].Requery

Denis