Solved

Calculate Quantity on Hand

Posted on 2014-02-21
13
519 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now