Update Queries - Inventory

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to make an update query that updates the "in stock" column of my table
after i put in a quantity on an invoice form. i have two drop down menus
(technically i have 20, i have ten sets of two) the first selects a type of
product (ie hard drive, processor) and requeries the second drop down. the
second selects the actual product and then displays the price of that
product. i actually have two questions: first, if i try to scroll down on my
form it takes the text box i used to display the prices after i select a
product and it scrolls through all the prices of all the products, i want
just one value at a time in the box and two: i have a quantity text box that
i want to subtract from the "in stock" column that matches the product i
selected in the second drop menu. i need help and be detailed cuz im new to
this program and i dont know much yet.
 
The trick is to learn about the AfterUpdate event. This is what would
trigger the update query, which is what would update the values in the
inventory table.

Explaining more right now, might be TMI, so catch up to here, and feel free
to ask more specific questions. Also, visit the Northwind database for
tidbits on a product ordering system.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
ok ill try and draw it out for you so you got an idea of what im doing.
this is my first table, just a list of product types:
[ID] [Product]
01 Hard Drives
02 Processors
etc etc

this tables links to a subtable which contains:
[Product] [Distributer] [Manufacturer] [Description] [Unit Price]
[Sale Price]..
hard drive D&H western digital 80GB $60.00
$95.00

[In Stock] [Re-order]
4 2

on my form i have ten places where i have two drop boxes. the first selects
a product from the first table through a query and the second drop box is
requeried to display the description from the second table (which is linked
to the first table) through a query. then it displays the sale price in a
text box next to it from the column it is held in the query and then
multiplied by one in a curreny expression to put it in currency cuz for some
reason thats the only way to do it but just leave that its fine. then you
enter a quantity is the quantity text box and it gives you a total price for
that item then when you are all done you click the command button total
prices and it gives subtotal, tax, and total at the bottom of the page. i
have twenty drop boxes each paired so you can select up to ten items. what i
need is that when you enter a value in quantity and then you are all done
with the invoice (including all items and total prices) you click the update
command button and it automatically subtracts the quantity from the quantity
text box from the in stock value on the second table in relation to the
description selected in the second drob box. when you explain how to do this
make it as clear as possible and use things like [your quantity text box] and
[form name] instead of examples of these. thanx a bunch if you can help me.
 
Back
Top