Solved

Update Main Form source from text box

Posted on 2011-09-28
5
147 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

744 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

15 Experts available now in Live!

Get 1:1 Help Now