Solved update querry (1 Viewer)

[sbaud2003]

how to make an update querry from the values in subform included in the main form

 

[Minty]

Not enough information to give any sort of answer. Update what?
What have you tried?
Have you tried the expression builder in the query window, it helps you build references to loaded forms.

 

[sbaud2003]

Not enough information to give any sort of answer. Update what?
What have you tried?
Have you tried the expression builder in the query window, it helps you build references to loaded forms.

Thanks for your prompt reply!!!!
I have a form name FrmSale and a sub form Invoice_details in which Item, Qty and Amount are specified. I want to run a update query to change the Stock in the table TblStock on entering the Item qty.

 

[Minty]

Okay, that makes much more sense. However, you are possibly heading in a design direction that will almost certainly cause you a lot of problems.
Stock levels should always be calculated from transactions, never stored. So your sales quantities should be a sum of stock received records and stock sold records. These should simply be transaction records , and you calculate the stock from these records.

Stockcode	LocationQty	TransactionDate	TransactionType
RMR0010	100	13-Jul-20	Moved to Production
RMI0040	100	13-Jul-20	Moved to Production
RXC0183	100	13-Jul-20	Moved to Production
BAT0004	25	26-Jun-20	Goods Received
FHD0017	11	24-Jun-20	Stock Take
OLE0005	13	24-Jun-20	Stock Take
OLE0006	20	24-Jun-20	Stock Take
RMI0003	180	24-Jun-20	Stock Take

A lot more salient advice given here http://allenbrowne.com/AppInventory.html

 

[jdraw]

I suggest you read this Stock Taking article by Allen Browne for ideas.

 

[sbaud2003]

Okay, that makes much more sense. However, you are possibly heading in a design direction that will almost certainly cause you a lot of problems.
Stock levels should always be calculated from transactions, never stored. So your sales quantities should be a sum of stock received records and stock sold records. These should simply be transaction records , and you calculate the stock from these records.

Stockcode	LocationQty	TransactionDate	TransactionType
RMR0010	100	13-Jul-20	Moved to Production
RMI0040	100	13-Jul-20	Moved to Production
RXC0183	100	13-Jul-20	Moved to Production
BAT0004	25	26-Jun-20	Goods Received
FHD0017	11	24-Jun-20	Stock Take
OLE0005	13	24-Jun-20	Stock Take
OLE0006	20	24-Jun-20	Stock Take
RMI0003	180	24-Jun-20	Stock Take

A lot more salient advice given here http://allenbrowne.com/AppInventory.html

where to call the function for getting stock at any time

 

[Minty]

There are many ways to do this, depending on the number of records and stock codes you might have.
Personally I would build a query that returns a sum of all quantities and simply link that into whatever form you need to display it on.