Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Calculate Quantity on Hand

Posted on 2014-02-21
13
551 Views
Last Modified: 2014-02-27
I have a database that I have been developing over a matter of time and am at the point where I really need to be able to track inventory (products received + products in stock - products shipped) to know my actual Qty on Hand status. I have a frontend/backend database and right now I’m trying to figure out how to show the Qty on Hand in my Products Form. The code below gives me the desired results, but I’d like to have just the Qty on Hand from that query in my Products Form. Is there a way to do that?

SELECT tblProducts.ProductID, tblProducts.ProductName, Sum([qryStockCount].[InStock]+[qryStockReceived].[QtyRec]) AS QtyOnHand
FROM qryStockReceived INNER JOIN (tblProducts INNER JOIN qryStockCount ON tblProducts.ProductID = qryStockCount.ProductID) ON qryStockReceived.ProductID = tblProducts.ProductID
GROUP BY tblProducts.ProductID, tblProducts.ProductName;

Open in new window

0
Comment
Question by:wcsjas
  • 6
  • 4
  • 3
13 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39878203
It seems that you have 3 fields being selected.
If this is the query on your form, simply ignore the other fields.
Of course, you can just remove them from the query as long as your form doesn't use them.
Maybe I'm missing how your form is using the query...
0
 

Author Comment

by:wcsjas
ID: 39878223
I'm sorry, I didn't explain myself well. The code I have listed, is for a query made strictly to get the Qty on Hand. I would like to be able to use an expression or code similar to that, placed in my UnitsInStock field in my Products form.

These are the forms in my Products Form:

ProductID, ProductName, QuantityPerUnit, Pages, Category, Language, Discontinued (this one is a checkbox), UnitsInStock, UnitsOnOrder, ReorderLevel, DateEntered, Date Discontinued, PricePerUnit, InkColor, PaperColor
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39879037
I'd just open a quick recordset to do this:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT tblProducts.ProductID, tblProducts.ProductName, Sum([qryStockCount].[InStock]+[qryStockReceived].[QtyRec]) AS QtyOnHand
FROM qryStockReceived INNER JOIN (tblProducts INNER JOIN qryStockCount ON tblProducts.ProductID = qryStockCount.ProductID) ON qryStockReceived.ProductID = tblProducts.ProductID WHERE ProductName='" & Me.ProductName & "'
GROUP BY tblProducts.ProductID, tblProducts.ProductName")

Me.UnitsInStock = rst("QtyOnHand")

Open in new window


You could perhaps run this on the Form's Current event.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 22

Expert Comment

by:rspahitz
ID: 39880974
So with those fields on the form, did you try simply adding a new field and set the ControlSource to QtyOnHand, where that field is a calculated field in the RowSource of the form?
(Does that make sense to you?  What is the form's current RowSource? Is it simply a table reference or is it the SELECT you showed?)
0
 

Author Comment

by:wcsjas
ID: 39882764
UnitsInStock is a field on a form. The form gets its data (record source) from tblProducts. That SELECT statement is from my query that I built that works, but I want to get that information on the from, so I can see any time I look at a product how much is in stock without having to open the query and search for it.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39882974
There are several solutions to this.
1) Change the form source to be a combination of tblProducts.* and your query.  However, doing this will mean that your form will probably not support updates.

2) You can add a control (e.g. Combobox) where the Source is your query.

3) You can add some VBA to populate a control (e.g. Textbox) with the value based on your query.  This is typically less maintainable but more powerful and flexible.

I would start with option 2 just because you should be able to simply paste your query directly into the RowSource.

If this doesn't quite work, would you be able to attach the DB (a copy minus data if it's proprietary) and I'll see if I can get it working?
0
 
LVL 84
ID: 39883581
Did you try the method I suggested? It would do exactly what you want, without concerns of updateability or unnecessary stored queries.
0
 

Author Comment

by:wcsjas
ID: 39886949
Scott McDaniel, that looks appealing to me. I won't even try to hide the fact that I have never worked with recordsets, so I'm trying to study up on that so I can implement it.

Our oldest daughter landed up in the hospital for a couple days with epilepsy complications aggravated by the stomach flu, so I haven't had a chance to do anything. I'm working on this now.
0
 

Author Comment

by:wcsjas
ID: 39889519
OK, Scott. I got that to work, but after a fashion. My problem now is that qryStockReceived is tied to tblTractsReceived and currently there are only 2 products that are in that table. That recordset only works on those 2 records, all other records it gives me an error message Run-time error '2113': The value you entered isn't valid for this field. because there isn't a matching record in tblTractsReceived. How do I work around that?
0
 
LVL 84
ID: 39890209
You can use the Nz function:

Me.UnitsInStock = Nz(rst("QtyOnHand"),0)
0
 

Author Comment

by:wcsjas
ID: 39890921
This is the message I get with that. Run-time error '-2147352567 (80020009)':

No current record.
I can press 'End' and keep on scrolling, but I have to press 'End' every time.

In doing a little more searching, I found this: Allen Browne Inventory . If I can adapt it to my database, maybe that'll work. I'm just not good in VBA.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39891718
You can do this:

If Not (rst.EOF and rst.BOF) Then
  Me.UnitsInStock = rst("QtyOnHand")
End If

Essentially that determines if the recordset returned any records. If so, the EOF (EndOfFile) and BOF (BeginningOfFile) are both FALSE, and your code will run.
0
 

Author Closing Comment

by:wcsjas
ID: 39892926
Scott did an excellent job of pointing me in the right way, and of explaining what the code does.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question