Kiang- A particular transaction should be able to add or subtract inventory. If you receive more product, enter a positive transaction. As you use up product, enter multiple negative transactions. If you run out and find some product was wasted, enter an "adjustment" transaction with a negative amount for the "shrinkage." Your new table should be absolutely unnecessary. And the new table you described is badly designed - there should never be a "repeating group" in any table (Qty01, Date01, Qty02, Date02). In the footer of the subform recording transactions, it should be a simple matter to add a text box that uses the Sum function to display the current balance for the product.
If you need to keep track of separate batches of chemicals, either create a new product for each new batch or create a two-level subform. The outer form edits products, the first subform edits batches, and the innermost subform edits received-used transactions for each batch. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of yong_yong6666 > Sent: Wednesday, August 03, 2005 1:35 PM > To: [email protected] > Subject: [ms_access] Re: Code to duplicate a field data form > one table to another > > > Hi John, > > I have modified the template of MS inventory control to construct a > system control of the activities in our chemistry laboratory. In the > template, transaction is a subform of product form, of which the > activities are well defined by per record of the product Table (or > Form). The trouble lies with 2 fields viz. "expenditure" > and "shrinkage" which affects in the transaction activities as both > can have only one row of record, but it has to be sub-mulitple > updates of data in the same row. For example, we ordered 100 units of > X compound, expending out 20 units will create a balance of 80 units > which is okay for the first entry. Subsequently, in the next round of > expending, a portion of the balance has to be deducted again & it > goes on a diminishing method, however, relecting in another row of > transaction will not be appropriate as every row is controlled by the > same batch lot of the compound. To resolve, I have created a new > table & pop-up Form "SubExpend" with 15 quantity fields (Qty01 to Qty > 15) and 15 date fields on click of field "expenditure" linked (with > integrity to TransactionID). A calculate field named [TotalExpend] > (sum of Qty01 to 15) is there in the "SubExpend". Now I need to > reflect the latest [Total] expended out in the field of "expenditure" > of the Product/Sub-Transaction Form which permits one time entry. Of > course, "SubExpend" has its other functions linking to Purpose and > several documentations. It is actually quite a complicated mess that > I'm attempting as we have no budget for professional LIMS softwares > of which we have no relevant knowledge with. > > Trust my elaboration is adequately comprehensive. > > Regards > Kiang > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Kiang- > > > > It is not recommended to store calculated values in your database. > Why do > > you think you need this total in another table? What is the > business > > problem you are trying to solve? > > > > John Viescas, author > > "Building Microsoft Access Applications" > > "Microsoft Office Access 2003 Inside Out" > > "Running Microsoft Access 2000" > > "SQL Queries for Mere Mortals" > > http://www.viescas.com/ > > > > > -----Original Message----- > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] On Behalf Of yong_yong6666 > > > Sent: Wednesday, August 03, 2005 12:16 PM > > > To: [email protected] > > > Subject: [ms_access] Code to duplicate a field data form one > > > table to another > > > > > > > > > I'm trying without success to automatically copy and paste a > > > field data > > > in my form to a another table field. Illustration as follows : > > > > > > Table A : > > > Key Field [Expend] > > > > > > Table B : > > > Key Field [Qty] > > > Key Field [Qty1] > > > Key Field [Qty2] > > > > > > Form is created from Table B with all fields of Table B included. > > > An addition unbound field viz. [Total] with expression : > > > =[Qty]+[Qty1]+ > > > [Qty2] has been added to sum up Qty. > > > A command Button has been created with intention, at click > [Expend] > > > from Table A should = Me.Total > > > > > > In the event procedure, how should be written ? > > > > > > Many thanks for your help ! > > > > > > Regards > > > Kiang > > > > > > > > > > > > > > > > > > ------------------------ Yahoo! Groups Sponsor > > > --------------------~--> > > > <font face=arial size=-1><a > > > href="http://us.ard.yahoo.com/SIG=12hqhe48d/M=362131.6882499.7 > > > 825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123096576 > > > /A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org > > > ">Get Bzzzy! (real tools to help you find a job) Welcome to > > > the Sweet Life - brought to you by One Economy</a>.</font> > > > -------------------------------------------------------------- > > > ------~-> > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > ------------------------ Yahoo! Groups Sponsor > --------------------~--> > <font face=arial size=-1><a > href="http://us.ard.yahoo.com/SIG=12htj238g/M=362131.6882499.7 > 825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123101308 > /A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org > ">Get Bzzzy! (real tools to help you find a job) Welcome to > the Sweet Life - brought to you by One Economy</a>.</font> > -------------------------------------------------------------- > ------~-> > > > Yahoo! Groups Links > > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12h4gtk62/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123102607/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org ">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</a>.</font> --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
