Modules & VBA :: Changing Stock Levels Of Multiple Product Components By One Command

Feb 11, 2014

I have the following form and subform combination:

The subform is linked to the main form by way of the "ProductID" control. (It's hidden in the subform).

Basically, each product (the finished article) is made up of many components.

What I would like to do is this:

1. Increase the stock of a "Product".
2. The Stock level control on the main form will then increment as much as selected
3. Contrary to that, I would like each component to decrement the relevant amount based on how much of the component is used up to make the product.

E.g. If I have Product A and it contains 2 components of which component A is used once per product but component B is used twice, then when I increase stock of product by 1, then component A goes down by 1 and component B goes down by 2. But If I put two of a product in to stock, I would obviously need component A to go down by 2 but component B to go down by 4.

I am thinking I need some way to loop through each record in the sub form for the current form, such as "Do While Not", or a "For Each". And then stick an Update query within the loop, but to be honest I can't think of how I need to address it!

View Replies


General :: Changing Stock Levels For Inventory Database?

Feb 7, 2015

Im building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.

My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.

View 3 Replies View Related

Stock Levels

Oct 16, 2007

Hi hope someone can help. Thankyou in advance.

I have a small database for my warehouse that sells washing machines, fridges etc.

I am trying to get a grip on the stock control.
What I am looking for when a order is filled out on our order form is the stock number will reduce by the amount sold. Not sure how to make this happen.
I have the number of units in stock under the products table

ProductsID PK

Sounds simple but for some reason I cannot figure it out.


View 1 Replies View Related

Stock Levels

Feb 7, 2007

hello, i am new to this forum. i have been struggling on a project i am doing where i am tring to calculate the stock levles and keep track of them along with the re order level
i have calculated the current stock levlel, in a query called "stock level 3"
Here the total amount prodcuts sold are taken from the field called stock level.

Here is a break down of my original tables


And all their personal details such as contact details

This is where the order takes place and contains the following information

Delivery costs
Overall discount
Final Costs

Here is the specific details of one order.



Prod_ Unit_Details

SUPPLIER TABLE – with detaisl e.g Supp Id and account number


Money_Owed Delivery
Supp_Credit_ Limit
Overall discount





•In the query “All customer order details”, I have worked out the line total for each prodcut with in one order. I have timed the Qty_on_Order (customer order details table) by price (in the produt table) and by the discount(customer order details) this then gives the line total

Now to update the stock level I made a new query called “stock level Query”, where I added up the total number of each products sold, by adding the “Qty_on_Order” from the query “All customer order details”

I then made a new query called “stock level 2” where I calculated the amount left on the shelves by taking “SumOfQty_on_Order” of each product from “Qty_in_Stock” (form prodcuts table) to produce the field called “Qty_in_Stock”

I then made a new query called “stock level 3” where I inserted the same fields but an extra field called “Re_Order_level” for some reason could not add this to the query "stock level 2"

So far the system can add up the total number of products sold, for all the orders, and then automatically calculate the Qty_ in_ stock by taking away the “SumOfQty_on_Order” once a new order is made.

I then made forms where I go in my form to “customer”" and then to “making new order”.
Below is a sub form called “all customer order details sub form” . Here orders are made with defining "Qty_on_Order

I want to make a system which calculates for me i have ordered more stock then there is.
Howcan i now when i make sure when i place a new order in the query"All customer order details" , that if i order above the number of "Qty_in_Stock" (from query srock level 3), from the query “stock level 3”, that when I order more products then ““Qty_in_Stock”, in the query “stock level 3”, does not allow me to enter this and if it is possible please can you insert error messages on it.

I do not know if this helps .- I can not make the “Qty_in_Stock”, to appear in the actual table “Products”.
At the moment I can only update this in the query “stock level 3”. I am not sure if I need this so I can then set re order level which then comes up with an error message telling me when the re order level has been reached. -

As later on I will need to add on any products brought in from the suppliers to the Qty_in_Stock”, in the products table to be automatically updated.

"stock level 3".
here are the fields

Prod_ID (Products)
Prod_Name (Products)
SumOfQty_on_Order (from "stock level Query")
Qty_in_Stock: Products.Qty_in_Stock-[SumOFQTY_on_Order]
Re_Order_level (Products)

here are both sql from both queries

All customer order details

SELECT [Customer Order Details].Prod_ID,
[Customer Order Details].Qty_on_Order,
[Customer Order Details].Discount,
(1-[Discount])/100)*100 AS Line_Total,
[Customer Order Details].Order_ID,
[Customer Order].Cu_ID
FROM [Customer Order] INNER JOIN
(Products INNER JOIN [Customer Order Details]
ON Products.Prod_ID=[Customer Order Details].Prod_ID)
ON [Customer Order].Order_ID=[Customer Order Details].Order_ID
ORDER BY [Customer Order Details].Order_ID;

stock level 3

SELECT [All customer order details].Prod_ID,
Products.Prod_Name, [stock level Query].
Products.Qty_in_Stock-[SumOFQTY_on_Order] AS Qty_in_Stock,
[stock level Query].
FROM (Products INNER JOIN [All customer order details]
ON Products.Prod_ID = [All customer order details].Prod_ID)
INNER JOIN [stock level Query]
ON Products.Prod_ID = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID,

please may help me and gice m,e soem tips it will be much appreciated or website/downloads.
Thankyou for your help, and if you find easier ways to do this can you help me.
Thank you so muh.

View 1 Replies View Related

Subtracting Stock Levels

Nov 9, 2005

Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.

View 2 Replies View Related

Adding Stock Levels Together

Nov 12, 2005

I have a database which seems to open many copies when it is accessed over the internet (via ASP) but If I open it on my compter over my LAN then it seems to open 'exclusive' and those on the Internet get the error message that 'file is already in use'.

So I have devised a method where I will make a copy of my database on my computer, clear all the stock levels and then add in the new stock.
My question is - how do I write a query that adds the 2 stock levels together?


View 1 Replies View Related

Queries :: Update Query For Stock Levels

May 12, 2015

I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....

I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.

View 6 Replies View Related

Queries :: Check For Expiry Date And Stock Levels

Apr 23, 2013

What I am trying to do is create a stock database and iv imported a blank version of the northwind template and filled it in with my own products which is fine up to a point.

What i also have though is i get weekly reports of the stock of each product with their batch numbers, expiry date and quantity remaining for that individual lot sent to me from another company. (So i might have several lines with the same batch number and expiry date but diffeent quantities) So what i want to ask is:

Is there any way for me to combine the data in the weekly reports with the stock level data in the northwind template?

What i want to be able to have is where somehow i can see the total quantity of eac product with the same batch number and expiry date and then it tells me when that batch has expired and it also automatically deducts the stock from my total stock when it gets to within e.g 9 months of the expiry date as we will not likely sell those.

I need to do this as at the moment all i can get from my database is how much stock has been ordered, sold and what we have in total on hand but if the stock is close to expiring then i wouldnt know and i need to be able to see when to order stock in advance as i want a minimum of 6 months cover and ideally 9 months cover as orders for stock dont come in straight away.

Also, how i can import the data from the weekly reports into excel/access easily as they come as word files presented as a table but the data is put in frames i think and iv tried removing them but then it all looks messed up.

View 1 Replies View Related

Product/Stock Access Database

Jun 28, 2005

I need a basic Product database that allows you to have a drop down menu to select a product from a table. In turn this puts the unit price in for you, you then put the quantity in and it calculates the total.

I also have a stock control. When one is bought it takes the quantity down. Very simple I know but I can’t get it to work….

If there are any good basic database templates i could use let me know

I have attached a sample zip file


View 2 Replies View Related

Auto Creating Record In One Table For Groups Of Related Records? + Stock Levels

Mar 23, 2008

Hey guys,

OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:

At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:

If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.

My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so

Any help?

Thanks a lot in advance!


View 4 Replies View Related

Modules & VBA :: Changing Every Value With Multiple Criteria

Jun 7, 2013

I made this code in excel, and I'd want to get it working in access.

Sub Satunnaisluvut()
Dim OmaAlue As Range
Dim Solu As Range
Set OmaAlue = Range("A1:A5")
For Each Solu In OmaAlue
If Solu.Value = "aa" Then
Solu.Value = "bb"
End If
Next Solu
End Sub

How do i set the wanted field? And how about the loop?

View 3 Replies View Related

Multiple Levels Of Data

Jan 14, 2008

I'm fairly new to access, having done a lot of what would have been easier with access in excel. I've decided to make the switch but need one particular thing that I can't seem to work out.

I have a series of "Courses" which contain a series of "modules" and each module has various amounts of data that needs to be accessed.

Is it possible to have module tables embedded in the courses table. Or, do I have to just have one massive table that contains ALL the modules with a field that says what course they belong to. Then make queries that display only the modules of that course? Or is there another way to do it?

I also haven't gotten to learning about queries yet, so if this is the way to do, can anyone explain how or better yet, point to a tutorial that can explain how this is done?

View 6 Replies View Related

How To Handle Multiple User Levels

Aug 23, 2011

I've got this database that needs some users to be read only and some users to have full control. I applied the appropriate ntfs permissions but there is a problem. If a read only users open the database, and then a full control users opens the database its stuck in read-only.

The database is a 2007 accdb.

View 2 Replies View Related

Modules & VBA :: Email Query - Changing FROM ID If Have Multiple Accounts?

Aug 5, 2013

How do I change the account from which to send the email if I have multiple accounts, or can I?

View 14 Replies View Related

Reports :: Access Report Chart Across Multiple Group Levels

Jan 22, 2015

I know how to create a chart in an access report, put it in the group header or footer and link it such that the the grouping filters the chart.

My VP wants me to create a chart that compares each invididual providers episiotomy rate (something that is done to pregnant women during delivery) to the overall divisional rate on a monthly basis.

I know how to do this with 2 charts, ie 2 different group levels. One chart goes in the date grouping level (the overall rate) and another goes in the provider grouping level (the individual provide rate).

is there a way to have this in one graph in an access report?

View 5 Replies View Related

Queries :: SQL Crosstab Query With Multiple Grouping Levels - Transform Statement?

Mar 24, 2015

I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.

The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)

The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.

This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!

TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

View 2 Replies View Related

Modules & VBA :: Command Button Press Multiple Times Based On Value In Text Box

Jul 23, 2015

Is there any way to press a command button a certain amount of times based on a value in a text box.


text box value = 5

button gets pressed 5 times with one click...

View 14 Replies View Related

Update Multiple Product IDs

Feb 21, 2012

I've got an order detail form which has

product id, product description, customer, quantity and below the form a Total quantity

When I enter the same product id, and when i press the button to update, the table of stock quantity deducts from total quantity

If I use different product ids, it doesn't work, but updates only the current product id, where the mouse current record is set by mouse.

By the way i use dlookup to check if the stockquantity has enough items.

View 10 Replies View Related

Web App - Product With Multiple Colors

Jun 25, 2014

I was creating a access web app for my company and want to create a data for its product. Each product will have multiple colors and different material are used for each color. The number of colorways for a product varies.

We want to be able to select a product and a lookup field will appears for us to choose its colorway.

View 6 Replies View Related

Company Db With Multiple Product Categories

Jul 12, 2005


I need to create a database of the info of a few hundred companies. Each company (food producers) specializes in various product categories, not just one. This is my problem/question: each company entry should then have a possibility to have as many product categories as needed. How can I do this without using "free text" fields? The website user should obviously also be able to search the db - by clicking checkboxes with the categories maybe?

(+Also: When the admin user needs to add the info of a new company (with, say, 9 product types) into the database, what kind of an SQL clause I need to INSERT INTO the db? For example if the product categories where laid out as checkboxes, and the user would just click the ones he wants.)

View 8 Replies View Related

General :: Multiple Prices Per Product

Dec 4, 2013

I am looking for a way to have different prices saved for each individual client.

I would have one main price list but i want to set certain items to have special prices for different clients. How is this possible in Access in excel its so simple.

View 14 Replies View Related

Modules & VBA :: User Login Form With Different Levels Of Access?

Feb 21, 2015

I have recently started a project and become stuck already. I have a login form created that works (code below). What i want to do is allow access to the users of 1-7 in table (level of access) which will determine which page they view after logging in. IE if the have Access in table "Users" as "1" then when they log in the will see a form thats called L1. Same with 2,3,4,5,6 & 7.

Private Sub LoginButton_Click()
If IsNull(Me.LoginUsernameText) Then
MsgBox "Please Enter Username", vbInformation, "Username Required"


View 5 Replies View Related

Queries :: Summing Quantities Of Same Product In Multiple Locations?

Sep 30, 2014

I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc

View 3 Replies View Related

Form Input Of Product - Send To Multiple Plant Locations

Mar 20, 2013

What I have is a table that contains plant info (plants that produce a product not vegetation plants). I also have a table that contains all the fields related to the products themselves. Finally I have a third table that contains the constituent items that make up products (think raw ingredients if you will). I have various junction tables set up to define relationships between all these.

What I'm trying to do is create a way of inputting a product and having an option on the form to send that product information to multiple plants. I don't know why but I cannot visualize an approach to doing this.

View 14 Replies View Related

Multiple Stock Locations

Nov 21, 2006

We have a stock situation, where the balance on hand is calculated by taking into account receipts and issues/sales from a historic balance.

how do stock systems like this normally deal with stock that can be located in multiple locations.

sounds very hard to me!

Thanks in advance

View 10 Replies View Related

Changing Command Bar Fonts

Jul 7, 2005

I want to change the font spec for certain controls on a command bar I've created but can't find out how, or even if, it can be done.

Does anyone know how to do this? There might be an API call somewhere but I don't have any details.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved