 Help! Queries and Calculations!
Hello,
I have a table that stores stock movement transactions for products I
have.
It looks like this...
Table = tblTrans
Fields =
Key (Primary Key)
Product (The name of the product)
Goods In (Amount recieved into the warehouse)
Goods Out (amount taken from the warehoue)
I also have a query that 'sums' up the 'Goods In' and 'Goods Out'
fields and returns the results ordered by product.
Something like:
SELECT [tblProducts].[key], [tblProducts].[product],
Sum([tblTrans].[goods in]) AS SumOfin,
Sum([tblTrans].[goods out]) AS SumOfout,
[SumOfin]-[SumOfout] AS totals
FROM tblProducts
INNER JOIN tblTrans ON [tblProducts].[key]=[tblTrans].[product]
GROUP BY [tblProducts].[key], [tblProducts].[product]
ORDER BY [tblProducts].[key];
Now I have created a form that allows me to update the 'tblTrans' with
goods I take out of stock.
What I would like to do is for the form to warn and stop me somehow,
if I attempt to take something from stock that is not there.
I am using text boxes to enter values and command buttons to execute
VBA code that in turn run SQL queries to SELECT a product I have and
to INSERT records into the 'tblTrans' table.
Something Like:
DoCmd.RunSQL ("INSERT INTO tblTrans (tblTrans.product, tblTrans.[goods
out]) VALUES (form1.SelectedProduct, form1.AmountToTakeOut);")
I am pulling hair out like no mans business and cannot for the life of
me fathom out what phrase Google would like to give me an answer!
Can anybody help!
My primary goal is to run an SQL SELECT query in VBA and return the
value into a temporary variable in order to perform a simple
calculation to find out whether I have the stock available to take
out.
Regards,
Damo.
| 
