Solved

Update Main Form source from text box

Posted on 2011-09-28
5
148 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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now