Form Subform Issue

[clk]

Hi. I have a really weird issue. I have a form listing job
information. This information includes a "Total PO amount". I have a
subform with change orders. So adjustments to the Total PO. I had
this as a calculated field on the main form. Total PO + Total of
change orders. That worked fine. I then needed that information in
another area of the database so I added a field to the table
"AdjustedTotalPo". I have update procedures to update that field.

Now my weird issue is that this process worked for everything until
recently. When a change order was added, it would calculate total
change orders at the bottom of subform and adjust the "AdjustTotalPO"
field on the main form with adjusted amount. Then it just didn't work
for one job. On a fluke I enlarged the subform. Once the subform was
large enough to show all the entries, the number on the main form
worked. This seems like really strange behavior. The calculated
field was right at the bottom of subform even though you couldn't see
all the entries but the number on the main form was not right.

Maybe I am doing this wrong and there is a better way. Any
suggestions would be appreciated.

Thank you.

 

[Allen Browne]

It's certainly better not to store this total. There are so many ways it can
go wrong, and your experience is one of them.

1. Create a query using the table from the subform.

2. In query design view, depress the Total button on the toolbar/ribbon.
Access adds a Total row to the grid.

3. In the Total row under the Order number field, accept Group By.
In the Total row under the amount field, choose Sum.
The query gives you the total for the order.

Now use this query (along with other tables you need) as the source for
anything else where you need the total instead of the details.

 

[clk]

It's certainly better not to store this total. There are so many ways it can
go wrong, and your experience is one of them.

1. Create a query using the table from the subform.

2. In query design view, depress the Total button on the toolbar/ribbon.
Access adds a Total row to the grid.

3. In the Total row under the Order number field, accept Group By.
In the Total row under the amount field, choose Sum.
The query gives you the total for the order.

Now use this query (along with other tables you need) as the source for
anything else where you need the total instead of the details.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.









- Show quoted text -

Thanks for the idea. I changed it and it now works perfectly.