Return Quantity on Hand for all products at the same time (1 Viewer)

ChrisMore

Member
Local time
Today, 10:13
Joined
Jan 28, 2020
Messages
174
Hi everyone, :giggle:

I am new to this forum and hoping for some assistance.

I have very little knowledge of VBA in Access and am currently developing a stock database. I plan to have a report which summarizes all products showing key information, such as the current quantity on hand levels. I have everything ready to go but the function I am using only returns one product's on hand level at a time using a parameter.

I used Allen Browne's Inventory Control: Quantity on Hand article and adjusted the function to suit my table names. It works perfectly and returns the on hand quantity as it should. However, what I really need is for the function to return the quantity on hand for all the products at the same time, instead of just one. I have structured my database in the same way as the example in Allen Browne's article.

My table names are as follows:
Products
Customer_Orders
Customer_Orders_Items
Purchase_Orders
Purchase_Orders_Items
Stocktake

I would really appreciate some help to get this working.

Thank you very much in advance :)

Chris
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Jan 23, 2006
Messages
15,361
It sounds like you need a loop construct to Call the function for each product.
Review For Next loop, While Wend for ideas/examples.
Good luck.
 

vba_php

Forum Troll
Local time
Today, 05:13
Joined
Oct 6, 2019
Messages
2,884
However, what I really need is for the function to return the quantity on hand for all the products at the same time, instead of just one.
Chris,

If you're doing this right, Allen Browne's custom function, if called from an actual query, should do just that. You might be going about it the right way, or might not. Have you taken a look at the inventory example that MS has in their ever-so-popular Northwind database? You may want to look at that. I have an example on github of calculating periodicals with regard to inventory stocking and accounting methods used (FIFO / LIFO specifically). You might be able to get something from it. It is here: https://github.com/ajetrumpet/MS-ACCESS-EXAMPLES/raw/master/ACCESS Inventory Level Calculations (FIFO - LIFO).zip

Also, on one of my github repo pages, there are many examples of using custom-defined functions in VBA, called from queries, to display financial data of various kinds in a single field. You can find those samples on this page: https://github.com/ajetrumpet/MS-ACCESS-VBA-CODE

The other thing here is that these tables of yours:
Code:
Customer_Orders
Customer_Orders_Items
Purchase_Orders
Purchase_Orders_Items
seem to be redundant. Isn't a customer order and a purchase order the same thing? Or if your ''POs'' are referring to purchases *your* organization is making from your vendors, I would say they should probably be in a another table. Customer data is totally unrelated to internal data, like vendor purchases.
 

ChrisMore

Member
Local time
Today, 10:13
Joined
Jan 28, 2020
Messages
174
Hi Adam,
Thanks for your reply.

If you're doing this right, Allen Browne's custom function, if called from an actual query, should do just that. You might be going about it the right way, or might not.
I used Allen Browne's code as a custom function within my query. It requires me to enter a parameter value (the product code) for which to run the query on. However, I want the function to run for all products and don't want the parameter box to appear. I'm unsure if I am going about this in the right way if I'm being honest. Obviously I am open to suggestions if there is a better way.

seem to be redundant. Isn't a customer order and a purchase order the same thing? Or if your ''POs'' are referring to purchases *your* organization is making from your vendors, I would say they should probably be in a another table. Customer data is totally unrelated to internal data, like vendor purchases.

My table structure is the same as the example in the Allen Browne article. My Purchase_Orders tables are the acquisitions tables and my Customer_Orders tables are the invoices tables. The function uses specific fields from these tables and the Stocktake table to return the quantity on hand value.

I'll take a look at the Northwind database and see how that works compared to what I am trying to achieve.

Thanks,
Chris
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:13
Joined
Feb 28, 2001
Messages
26,996
Sounds to me like you over-categorized something, or missed something, I can't tell which. vba_php also noticed this apparent discrepancy.

Normally, the correct (and easiest) way to do this is a table of inventory transactions. You have the item number (could be your SKU if that is what you use) or a specific part number. You don't have descriptive data because you define the item elsewhere in a Parts table. But you need at least a couple of more items. You might include date of transaction, type of transaction, amount of transaction, and EFFECTIVE amount of transaction. (I.e. if you put something IN inventory, effective transactions are positive numbers. If you take them OUT of inventory then the effective transactions are negative numbers. Things like "stock correction" as a transaction could be positive OR negative, depending. They would stem from sight inventories that return a result different than the computed inventory levels.

If you have a master inventory transaction table, you can then do a summation query that uses a GROUP BY on the item number to determine current stock levels. If you do so, that query WOULD give you simultaneous counts on every item. Once you have the summation query, you can JOIN it to your parts table for more descriptive info for your reports.

This isn't necessarily a violation of normalization because what would be in the xxxx_Orders_Items table would quantify when and how items were transferred. However, IF you want to keep those tables as-is, then you would need a UNION query to represent your transactions and would need a separate xxxx_Items table to represent adjustments.

There are many other wrinkles on this. Search the forum for topics on "Inventory" because that is a VERY common question.
 

vba_php

Forum Troll
Local time
Today, 05:13
Joined
Oct 6, 2019
Messages
2,884
yeah, look at the NW sample and see what you can do with it in terms of learning. Most of the pros around here swear by Allen Browne, however my personal opinion of the guy is that, while he has a lot of genius articles on his website, I disagree with some of it. I've even downloaded some of his examples, modified them to be more purposeful, and implemented my own solutions sourced from his work. But bottom line, there are very few sites on the internet that are as good as AB's.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Jan 23, 2006
Messages
15,361
Chris,
Maybe it's time to post a copy of your database (zip format). Remove anything personal/confidential and we don't need volumes of data. Just enough to highlight the issue/concern.

As Doc says if you are working with transactions, then records in the transaction table should represent Products and Quantities purchased or Sold since the last StockTaking.
Good luck.
 

vba_php

Forum Troll
Local time
Today, 05:13
Joined
Oct 6, 2019
Messages
2,884
Maybe it's time to post a copy of your database (zip format).
oh come on you guys, let us give the poor guy a chance to do it himself! He seems like a pretty smart dude! I'm sure he'll be fine. Let us wait to see what he has to say....no?
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Jan 23, 2006
Messages
15,361
No one suggested doing it for him. You and Doc have identified some "apparent discrepancy".
So do you want to keep guessing or look at the actual code and streamline communication?
It was a suggested next step, but the OP can ignore it or act on it.
Agree he seems "pretty smart", but he's also the one with the problem, and seeking help.
 
Last edited:

ChrisMore

Member
Local time
Today, 10:13
Joined
Jan 28, 2020
Messages
174
Hi all!

Thanks for all the input so far, and thanks for having confidence in my ability ;)

I have had a look at the Northwind database (I can't believe I haven't done so before!) and I am inspired to learn to create something like this in the future. However, I will say it is a lot more complex than the needs of the company I work for. We have separate accounting software which covers much of the NW functionality. The inventory database I require is purely for stock level tracking based on monthly stock take records minus customer orders (disposals) plus purchase orders (acquisitions). That is why, in theory, Allen Browne's article fitted the brief perfectly. It only falls down because I am currently unable to view the on hand quantities for all products at the same time.

I like the sound of The_Doc_Man's table of inventory transactions idea. The only potential issue with this is if all negative transactions need to be actually entered as a negative number. I can see my colleagues in the office forgetting to add a minus to make the number negative! If this isn't necessary, please let me know as I might be misunderstanding the whole concept.

I have attached my inventory database in it's current state. I am in the process of making the queries before I move onto the forms/reports. I plan to have a front sheet form with buttons to bring up different forms/reports, the summary (dashboard) report being one of them.

Once you open the database, click on the Dashboard_Query which will require you to enter a product code (1 to 236) and an optional as of date.

I hope this helps you to help me solve my problem :)

Cheers,
Chris
 

Attachments

  • Inventory Database.zip
    137.9 KB · Views: 253

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:13
Joined
Jul 9, 2003
Messages
16,243
I have had a look at the Northwind database (I can't believe I haven't done so before!) and I am inspired to learn to create something like this in the future.

Be careful with the Northwind database, it's a Microsoft sample database and it's not built for the purposes of business, its built for the purpose of demonstrating the features of MS Access, with a bias towards the simpler features of Microsoft Access which a beginner might be tempted to use, and which in many cases are avoided by professionals. I know for a fact that the early version of the northwind database could actually destroy information if used in a working environment... See my post here:- Northwind Problem and Solution
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:13
Joined
Feb 28, 2001
Messages
26,996
if all negative transactions need to be actually entered as a negative number. I can see my colleagues in the office forgetting to add a minus to make the number negative!

Yep, that's a problem. Which means you apply the full form of Murphy's law. "If it CAN go wrong, it WILL go wrong." (That's the first part, but nobody ever remembers the second part "So build it so that it can't go wrong.") Because of Ed Murphy, molded plugs were invented, the kind that can only be inserted one way.

I mentioned that you COULD keep your tables and at most modify them for transaction orientation, but that you might add a UNION query. IF you do that, then when you pick up the kind of transactions that draw down the stock, you pick up the quantity as SELECT PartID, -Qty, etc. FROM ..., whereas if you are adding stock in the other branch of the UNION, you have SELECT PartID, Qty, etc. FROM ...

At that point, you don't worry about what was put in because you compensate for it when you form the UNION.
 

vba_php

Forum Troll
Local time
Today, 05:13
Joined
Oct 6, 2019
Messages
2,884
Chris,

Since no one else here has said something about your file, here's what I saw in terms of *why* you're getting only 1 return value for every record in your query:

The function *onHand()* gets its value from the addition of 3 variables:
Code:
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
and those variables get their values by pulling data from single fields in recordsets that run from these sql statements:



when I broke the code after each statement and checked on what value from those single fields you were assigning to the variables, it showed this:



the value *1413* shows up in every single loop the function runs through. another piece of code that you should change is the following, because you're giving a *long* type variable a *variant* type value (probably doesn't matter, but still....):



does this help you pinpoint the problem? have you debugged your code by way of a process like this?
 

Attachments

  • sql_statements_after_breaking.jpg
    sql_statements_after_breaking.jpg
    111.6 KB · Views: 431
  • variables_getting_values_assigned.jpg
    variables_getting_values_assigned.jpg
    103.3 KB · Views: 419
  • long_assigned_a_varriant.jpg
    long_assigned_a_varriant.jpg
    27.3 KB · Views: 436

jdraw

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Jan 23, 2006
Messages
15,361
Chris,
I mentioned a loop construct early on. I haven't been following the details of the responses, but didn't see any Loop
attempts/comments.
Here's an example using your February_Orders_query to illustrate the point I was trying to make. This is for the concept of listing QtyOnHand for all products in a set. You know your setup better than readers, so adjust the recordset(s) as necessary to get the QtyOnHand for each Product involved.

Code:
Sub InvtyAllProds()
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("february_Orders_query")
    Debug.Print "Product_code", "Qty on Hand  "
    'Loop through all products ordered in February to get QtyOnHand
    Do While Not rs.EOF
        Debug.Print rs!product_code, OnHand(rs!product_code)
        rs.MoveNext
    Loop
End Sub

Which shows this result
Code:
Product_code  Qty on Hand

16            755
138           756
182           1386
198           1132
 

ChrisMore

Member
Local time
Today, 10:13
Joined
Jan 28, 2020
Messages
174
does this help you pinpoint the problem? have you debugged your code by way of a process like this?

Before I posted my problem to this forum I had been playing with the code to try and identify the reason for it only producing one result. My conclusion was because the product code is a variant, but as I have limited Access VBA knowledge I didn't know how to amend this.

I think jdraw is almost there with the solution. I adjusted the code so the recordset is a query for all product codes and when I run the code in VBA it produces the result I am looking for. However, when I use this code as a custom function in a query it isn't showing the result like it does in VBA.

How do I go about getting this code to work within the query? Maybe I'm missing something really simple!

Thanks again for your help with this, I feel like we are getting close to making this work. :)
Chris
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:13
Joined
Feb 28, 2001
Messages
26,996
I had been playing with the code to try and identify the reason for it only producing one result. My conclusion was because the product code is a variant, but as I have limited Access VBA knowledge I didn't know how to amend this.

A "Variant" is merely an ambiguous data type that can be modified from one type to another at run time. It is a "generic" variable that is truly (and silently) amorphous. A LONG is clearly an integer. A DOUBLE is clearly a scientific number. A STRING is clearly text. A VARIANT could be any of those things at different times in the same long code segment. It cannot, however, be the cause of something being a constant. If anything, a VARIANT is at the far end of the spectrum from constants since it is the ultimate "plaything." Its only danger is that variants can never be the cause of a warning for "type mismatch" since anything you assign as the value of a Variant will work just fine. No error, no muss, no fuss.

If you have a fixed value inside a loop, it is because nothing is updating the fixed value. It has nothing to do with VBA. That same behavior would apply to FORTRAN, C#, JAVA, COBOL, or PL/1. Variables act like constants because you don't update them. Therefore, look into the loop to see where that variable would be updated. If there is no update, then (for the purposes of that loop) it IS a constant.
 

ChrisMore

Member
Local time
Today, 10:13
Joined
Jan 28, 2020
Messages
174
The_Doc_Man, thank you for your explanation.

Which shows this result
Code:
Product_code  Qty on Hand

16            755
138           756
182           1386
198           1132

jdraw, I have created a query for all product codes (called 'Products_Query) and changed your code so 'Products_Query' is the name of the recordset. I then created a query with a Product_Code field and a custom function with the code as the Expression in the next field. The code is working perfectly in the Immediate Window, but not for my query. Do you have a suggestion on how to modify the code so it works within the query?

Thanks,
Chris
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Jan 23, 2006
Messages
15,361
Chris,
Can you post your latest database with the revisions you have made?
 

Users who are viewing this thread

Top Bottom