how to fill table field with data from Unbound Form?

Question

so I have been making a production and manufacturing database in Access, which has a table that registers invoice documents and has attached a subform where you pick the articles you are buying.

then I created a text box that calculates the total in the subform.

afterwards in the main form I created a textbox with this control source:

=[ArticlesSub].[Form]![TotalSubForm]

so I have the data in the main form, but I cannot find a way to make the form store that data into the field named "TOTAL" from the table which the main form is based.

how do I get the form to store that data into the proper field (total on the form into the total field in the "reg1" table)?

Answer 1

Why do you want to store it, when you can calculated it always? Just use a query to do this.

Otherwise you can use a data macro to do this: Attaching Logic to Data Using Data Macros in Access 2010 Applications

Answer 2

the reason is because this are the first steps for a full manufacture line, which will require other form getting data from the first form table and then further processing it... and this is attached per document (process line and production orders, etc etc) also I am very new on access so I am still learning.... (new as in a week and been reading and studying like a madman)

Answer 3

so I have been making a production and manufacturing database in Access, which has a table that registers invoice documents and has attached a subform where you pick the articles you are buying.

then I created a text box that calculates the total in the subform.

afterwards in the main form I created a textbox with this control source:

=[ArticlesSub].[Form]![TotalSubForm]

so I have the data in the main form, but I cannot find a way to make the form store that data into the field named "TOTAL" from the table which the main form is based.

how do I get the form to store that data into the proper field (total on the form into the total field in the "reg1" table)?

You *can* do that, and I can tell you how, but first may I ask you *why* you want to store this calculated -- and always calculatable -- information?  Normally it's not a good idea to store calculated values, because it's (a) usually more efficent to calculate them when you need them, and (b) at risk of becoming incorrect when the underlying data later changes.

Suppose you store your example value of 1320 into the TOTAL field.  Then later this order is amended to add another item, or the cost or quantity of one of the existing items is changed.  At that point, the TOTAL field in your parent table is no longer correct.  Whereas, if you just use a query to return the total whenever you need it, it cannot ever be incorrect.

Now, there can be circumstances where you do need to store a calculated value.  For example, if you have a need to store the total as of the time the order is first entered, so that it can be compared to, maybe, the total when the order is shipped, that's another story.  But if all you want is to have forms or reports show the current total, I woud suggest that you never need to store that value in a table at all, and probably shouldn't.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

In every Forum whenever someone asks this question the pros send you the query way. Sometimes people want to keep it simple. In future if it is doable answer the question first then give your view or suggestions thereafter.

Answer 5

1. Create a field in your table called PurchaseTotals (DO NOT use 'Total' or 'Totals' as your field name). The Data Type should be Number. Format should be Currency with 2 decimals.
2. Bind the field Control Source on your main form to the new PurchaseTotals field.
3. Open your sub-form in design mode.
4. Since you need a way to get the sub-form order total to the new main form PurchaseTotals field, create a VBA After Update Event with the ARTICULO, ENTRADA and VALOR fields using the following line of code:

Forms![FACTURA COMPRA]![PurchaseTotals] = Me.TotalSubForm (assuming this is where the sub-form total is stored)

This should enter the sub-form total into the main form PurchaseTotals field each time ARTICULO, ENTRADA or VALOR changes.