By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

Help! Queries and Calculations!

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"damoyoung" wrote in message
news:43*************************@posting.google.co m...
Hello,

I have a table that stores stock movement transactions for products I
have.

It looks like this...

[SNIP!]

Yup...

I finally found that DLookup was the answer to my own question :)

Heh...

Damo.


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.