Inventory Problem Beginner Database

[joe.ise]

Hello
Another new person trying to get started. I did a project for my senior design class in college. The project was to help a company keep track of their parts inventory. My team member did the access data base portion of the project and now it is not working and I work for the company we built the database for. Class is over, team members graduated and the database is full of code, and not even one relational connection between tables.


Anyway, the only way for me to save face is to build a new, simple one from scratch, this way I can manage / maintain it. I wanted to start with just a few simple SKU's and at most 2 parts for each SKU. My goal is to keep our parts department stocked with inventory. I know how to calculate the re order points based on the previous demand I have and can worry about a fast way to do that later. For now I just need to keep record of the parts being sent to customers and the parts coming in to replace those from the vendor, which I think I have in place, see attached beginnings of my database. Now I need to figure out how to create a query, and hence a report that shows current inventory levels and another query / report that shows which items have gone below the re order point. I have time to work this out step by step if someone else has the time to help me. Please help if you can.
joe.ise

[joe.ise]

So I have read the example http://allenbrowne.com/AppInventory.html
from the posts. I think this is where I need to be but I do not understand how to put the function that returns the quantity on had into the database. I assume in needs to be put into a query. Is that correct ? It looks like it might should be a macro but I do not know much about macros. I have only taken one class in Access and it was only a brief part of the semester. Any help is appreciated.
Thanks

[RuralGuy]

Allen provided the Function to return the Quantity on Hand if your db was set up as he described. You can run that function from a form in an event if you wanted to.

[joe.ise]

Allen provided the Function to return the Quantity on Hand if your db was set up as he described. You can run that function from a form in an event if you wanted to.

Well I tried to create a form called CurrentInventory and created a button (Text 7) and tried to put the code from the allen example in and I am getting an error. Any advice ?
My database is attached.
Thanks

[RuralGuy]

Try this one. To start, the code from Allen's site should have been in a Standard Module, which I did for you. You then only need one copy of it. Second, you cannot put a Function inside of a Sub, period. Look at what I did in the Form that automatically loads and you will see how easy it is to use.

[joe.ise]

Try this one. To start, the code from Allen's site should have been in a Standard Module, which I did for you. You then only need on copy of it. Second, you cannot put a Function inside of a Sub, period. Look at what I did in the Form that automatically loads and you will see how easy it is to use.

Thank you very much for working with me. I am truly grateful. I am going to look at what you did, see if I can polish up my forms and buttons and get it running. I will get back to you soon.

[joe.ise]

RuralGuy
So I have been trying to figure out how you put the code to show current inventory from the allen browne example into the module that you did for me. I understand that you put it into a standard module, and I tried to replicate what you did in a copied version of my previous data base before you fixed it for me but it does not show up in my objects. I then think you linked that module to the button in the current inventory form. I clicked on the button in the current inventory form and selected properties but I can not see where your basInventory shows up. I really want to learn how to do what you did so I can do the next step on my own which will be to show current inventory that is less than the reorder point but I am just having trouble. I will keep looking at it and see if I can figure it out. I guess, if you have time, give me a hint if you think I need one without giving me too much and hindering the learning process.
Thanks
joe.ise

[joe.ise]

RuralGuy
Attached are some screen shots to help explain.
Thanks
joe.ise

[RuralGuy]

The Standard Module will not show up until you save it. I invoked the OnHand() function from the Current Event of the form.

[joe.ise]

RuralGuy
Wow ! I think I got it to work. See attached screen shots. Screen shot #3 shows me replicating your code that I found nested in the text box on the current inventory form and it seemed as if it was "calling up" (sorry about my bad terminology") your module. Screen shot #4 shows my results, seems to be working correctly. Now I want to add the feature showing my person in the warehouse that she needs to reorder because her current inventory has reached it's re order point and tell her how many to order. I think I need to add some fields to tblProduct as shown in screen shot 5. Figure out how to populate those fields, then find code to make it all come together. Can you stay with me ?
Thanks
joe.ise

[RuralGuy]

Remember, QuantityOnHand is a calculated value and should not be a field in a table. I'm not going anywhere.

[joe.ise]

Ooops ! That's right, that was the big no no that was mentioned and why we put the code into the data base in the first place. So, I am thinking I eliminate QuantityOnHand and OrderThisManyNow fields. Keep ReOrderQuantity and OrderUpToQuantity and then figure out code and implementation steps to do something like this:
If Inventory Level from CurrentInventory form is less than or equal to ReOrderQuantity
Then tell user order enough parts to total OrderUpToQuantity
If Inventory Level from CurrenInventory form is greater than ReOrderQuantity
Then nothing

[RuralGuy]

That sounds like a plan.

[joe.ise]

Well I have been thinking I need to start with a Macro or a Module. Since I was able to use the code from the Allen Browne example last time to put into the module and I have no code example this time I thought I should try to write it in a Macro and then maybe make the CurrentInventory form run that Macro after it is opened. Attached is my first attempt at writing the Macro. If a Macro is the way to go and I am headed in the right direction can you give me a hint where to go next ? It seems as if I have the "If" part right in my Macro, not sure, but I can not figure out how to tell the data base to display the OrderUpToQuantity if my condition is true.
Thanks

[RuralGuy]

I don't not use Macros and know very little about them. I only use code. If you want help with code, I can help usually but not with Macros. Sorry.