Solved

Update Main Form source from text box

Posted on 2011-09-28
5
152 Views
Last Modified: 2012-05-12
In a Main Form called frmInv I have a text box where the control source is the calculation =[ShipQty]-[frm846820]![Tot846]. ShipQty is a field from the main form.Tot846 is a calculated textbox from the footer of subform frm846820. After each record is added to the subform Tot846 is updated which in turn updates the textbox in the main form. This works as desired. I also would like to update a QtyBal field in the table tblInv that is the data source via query to the main form. Is this possible? What would be the best approach? Thanks.
0
Comment
Question by:StampIT
  • 2
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 125 total points
ID: 36719170
You can put field QtyBal to main form and in some after update event fill it with value from your text box
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36719278
This sounds like you are trying to store "Derived/Calculated" data.
This may or may not be advisable in your situation.

So you might first want to tell us why you believe this data should be stored, and not simply "calculated" in a query.

JeffCoachman
0
 

Author Comment

by:StampIT
ID: 36719616
als315: The main form is used to make the initial entry. After that all the data is entered via the subform.The QtyBal is affected by what is enterd in the subform. Thanks.

Jeff: I have two tables being updated in this form. One has the invoice records and the other has shipments and payments against the invoice records. I can join the two in one query and group on invoice number. Then I can sum the ship qtys entered but can I also calculate a balance in the same query(InitialQty-SumOfShipQtys)? Thanks.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 36770916
If you "Stored" the calculation, then you would have to continually "Re-Store" (update) this value with each new or updated or deleted shipment/payment.
This would be great if this was all done in the form, and the form was always used.

However, what happens when there are payments/shipments entered into the system and (for whatever reason) this form is not used/opened?
Then the totals do not get stored/updated, hence your totals may be wrong, and you may never know.

So I will stop short of saying that it is "wrong" to store calculated values, only that it can be "risky".
;-)

(But to be fair, I believe that als315's post has answered your question as posted, and deserves consideration/points, ..etc)

So, ...you can still use the form in it's present state for viewing detail, editing, adding, deleting, ...etc.
But with addition of a query, the totals should always reflect the current data in the DB. (Without having to open the form and use code to "store" the totals)

So, yes, ...from what I can tell you can use a query to get all the totals you need.
See the attached sample db for an example of how this can be done.
The query name is: qryPaymentBalance
(There is even a report as well)

If something like this is what you need, then I am sure you will be able to adapt what I have done in the sample to work in your database.

Keep us posted on your evaluation of each approach.

;-)

JeffCoachman

Access-SampleMultiplePaymentsBal.mdb
0
 

Author Closing Comment

by:StampIT
ID: 36815965
I scrapped the idea of updating the quantity balance in the form. However as Jeff pointed out als315 did answer the question as posted so he gets half and Jeff gets half. I was able to get the information I needed by using the examples that Jeff attached. I had tried this approach before but could not get it to work. Must have been doing somethin wrong. Appreciate all the help.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question