Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update Main Form source from text box

Posted on 2011-09-28
5
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 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 500 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

609 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