Maintaining total of a subform records

[morsagmon]

Hello all!

I have two peer subforms on a main form: sfrmOrders, sfrmOrderLines.
The sfrmOrders has a control (as part of the "Detail" area) bound to a table field: Total_Before_Discount. In this way, the user can see each line of order-header with its total.

sfrmOrderLines has two bound fields (in the "Detail" area), from two separated tables(!):
Unit_Price - bound to table tblProducts
Bottles_Ordered - bound to table tblOrderLines

What I need to achieve:
Upon changing Bottles_Ordered, I need Total_Before_Discount of the current row in sfrmOrders to be updated and refreshed.

Initially, I thought DSum is the natural way, but it doesn't work (probably because the two fields that needs to be multiplied don't share the same domain/table).

I currently have hidden fields in sfrmOrderLines that hold the multiplication (per row) and the total (in the "Header" area) that can serve a solution, if necessary - I couldn't get it to work properly.

Any ideas?
Thanks!

 

[PNGBill]

If the Values are on the Form, or subforms, you can use an Unbound Textbox Control for a Calculated value using these.

Your "script" will reflect where the Text Boxes are (Main Form, Sub Form or even sub, sub Form) and in the first instance, refer to the Control Source and after this to the Text Box control Name.
By first instance I mean where you are referring to the original Bound text box control and after this, when referring to an unbound textbox control.
You can keep going all day long with calculations all over your form.

You will need to Requery your form when some or all changes are made and this can be built into some controls or add a command button to Requery.
Often "Refresh" doesn't achieve much.

See this link that was supplied by another member:

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

 

[vbaInet]

What I need to achieve:
Upon changing Bottles_Ordered, I need Total_Before_Discount of the current row in sfrmOrders to be updated and refreshed.

I'm guessing your subforms are in datasheet or continuous form view? Regarding what I highlighted, it isn't possible. You can get it to recalculate for the whole recordset. If you're happy for this to happen then all you do is perform the calculation in the Control Source property of the unbound text box.

 

[morsagmon]

Both are continuous forms.
I don't mind that the whole recordset is requeried and refreshed upon each change of the relevant field in sfrmOrderLines. I couldn't get it to work.
What unbound textbox are you referring to? I know how to make an unbound textbox reflect such changes. The problem is that the updated field I need in sfrmOrders is a BOUND control - part of the recordset.

Thank you!!
Mor

 

[PNGBill]

If the Form Control you want to reflect the change is bound then look at the Record Source and see why this is not reflecting the new data.

At the end of the day, all Bound Controls only show data the underlying query or table have.

If the changes are done in a control not part of the Record Source, above, then an Unbound Control may be your solution in which case you will need to get your syntax correct for the expression in the source property of this control given you have MainForm and SubForm.

 

[vbaInet]

I thought I saw unbound. Anyway, the only way for a particular record to be affected would be to use an UPDATE query or use an UPDATE sql statement in vba.

http://www.databasedev.co.uk/update_query.html

 

[morsagmon]

I don't have a MainForm and a SubForm.
I have two PEER forms, placed within two neighbouring form controls, on a blank empty form. (I access one subform from the other by me.parent.sfrmX...).

Your suggestion, if I understand correctly, is to update the Orders row through a DB update (update query) instead of through the forms. I can do that. However, I wonder, if I have all data on my forms, isn't there a way to update the data through the controls on the forms and requery/refresh?

Thanks guys!

 

[PNGBill]

You do have Main Form and Sub Forms. Just because the main form does little doesn't mean it is not there.

You should be able to get an Unbound Text Box Control to calculate using data on your other subform. you just need to get the syntax correct.

If the data is displayed in Bound Text Box Controls then surely this data source is exactly the same as the data displayed on the Form / SubForm.
Only an Unbound Control can have data that is not in your Record Source - I think.
If correct, then your query (Record Source) can also provide the data you want.

 

[morsagmon]

Getting the result I want to be displayed in an unbound textbox is not a problem. I got that already. The point is, the target of this calculated value is a BOUND control. In other words, manipulations of subform B needs to update a field in the underlying source of subform A, and immediatelly reflect this change on form A so the user sees this also.
The only question, is how to do this:
1. by way of updating the BOUND control (assuming the form mechanism updates the underlying table) and requery/refresh.
2. Coding an update SQL statement that updates the tables directly and then refresh form A.

I tried option 1, without success thus far. The most I got, is that an intermediate (hidden) unbound textbox reflects the calculated sum OK, but the BOUND control gets updated with a delay of one cycle (subsequent updates in form B and change to the unbound control causes the PREVIOUS calculated value to show up on form A in the BOUND contro).

How would you go about doing this?
Thanks!!

 

[PNGBill]

One question is does the Bound Control in SubForm A need to be Bound ?

If this is a Calculated Field, does it need to be Bound ? What is to happen in life after this Form?
Is the Value required elsewhere?

If not, can you make it an unbound Control?

If it must be bound, is the record source a table field or a New calculated field in a query?

I suppose your problem is that if you build into one of the Controls a Requery of SubForm A, you will loose some Unbound Data, or will you?

You can have an sql update query in an AfterUpdate event on a form/subform control that will update a record field in a table and I guess you could also have an sql that even became the record set for subform A as vba can change a forms record set. Just depends on how you want to do it and again, what is the record source for subform A and are you sure the control must be bound?

If the Form is for viewing then consider having the control unbound and either the table is updated, from this control, by a Command Button click or when the form is closed which will save the record value if it must be done.

 

[morsagmon]

Bill,
form A shows all order headers: OrderNo, Customer, Total
Naturally, these are stored in a DB table called tblOrders.
The Total field above is the bound control we're talking about.

form B shows the records from tblOrderLines.
When the user changes the quantity (for example) of an order line - that line's total changes, and consequently needs to update the Total field of tblOrders that is shown on form A.

How would you approach this?
Thanks!!

 

[PNGBill]

I wouldn't have the total in a table.

You will find numerous posts here from the senior members all saying do not have calculated fields in your tables.

You now have one example of why they say that.

Any time you want you can have this value but it will come from a Calculated text Box Control on a form or a Query Record Source for a Form or Report.

Remove or move aside this Form Control and add an unbound Text Box Control, give it the required expressions and see your Total, if satisfied, delete the old control.

Then, consider removing the field from your table.
You will no longer need worry if the data you see has been updated or not.

Store SaleQty, SalePrice, maybe TaxRate in your tables and then at any time in the future, the Invoice Total will always be the same.

Month End Reports, or similar, will have the Total in them (calculated) and provide historical records should they be required.

 

[boblarson]

What Bill said is good advice.

 

[morsagmon]

Bill,

Thank you for this.
It is a good design practice for a normalized DB, and typically that's what I do.

At times, given logic/storage tradeoffs, I opt to divert from this practice and store redundant data so that a lot of logic work is saved.

However, giving it a second thought, in this case I believe you're right and I can drop the total fields from the DB.

Many thanks!
Mor