Access "inventory control"

  • Thread starter Thread starter eliud villanueva
  • Start date Start date
E

eliud villanueva

If I have a table with "products" and "quantity in stock"
and I want to change the quantity in stock for all products
to 6 pcs. at the beginning of each day, what is the easiest
way to do it, (there may be 10,000 products in the table)
This is for a radio station that wants to limit the number
of times that a song is played in a day to 6, or other number.
The DJ must generate a songs play list that will mimic a
sales order that when shipped will reduce the quantity in
stock.
I believe a macros is the right path but I don't know where
to start.
 
You don't event need to use a macro. The easiest way to do this is to use
a query. You can even type the query into the SQL View screen. Rename
the field names to whatever you are using:

UPDATE TableName
SET [Quantity in Stock] = [Quantity in Stock] + 6;

That's it! Run this query whenever you want to update the quantity by 6.
 
An update query should do the job. You would simply update the "quantity in stock" field
to equal 6 (or whatever number you decide upon). To create an update query, start with a
normal Select query. Add the "quantity in stock" field to the QBE grid. Then click on
Query > Update Query, to convert your Select query into an Action query. You should see a
new row in the QBE grid titled "Update To:". Enter 6 in the "quantity in stock" field.
Run the query.

Once you get your query working correctly, you can run it via a macro if you'd like. You
can also attach VBA code to a command button on a form which will cause your query to run.

Tom
_______________________________________


If I have a table with "products" and "quantity in stock"
and I want to change the quantity in stock for all products
to 6 pcs. at the beginning of each day, what is the easiest
way to do it, (there may be 10,000 products in the table)
This is for a radio station that wants to limit the number
of times that a song is played in a day to 6, or other number.
The DJ must generate a songs play list that will mimic a
sales order that when shipped will reduce the quantity in
stock.
I believe a macros is the right path but I don't know where
to start.
 
Tom's right, I didn't read it carefully. I thought you wanted to add six
to whatever the quantity is. If you want to change it to six, then what
Tom said is exactly what you do.
 
Back
Top