?
Solved

Update Main Form source from text box

Posted on 2011-09-28
5
Medium Priority
?
163 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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