Question Inventory Project

[mjdemaris]

I could use some input/suggestions on my database here. First, I am new to database design and haven't used VBA in a few years.

Here is what I've got so far, in an attached zip file. The data is not complete, but it serves the purpose.
On open, there is a splash screen, which opens a navform for general users to search for a part. They select the search field, type, click on part in list box, verify that is the part they want, and sign out the part.

I haven't figured out how to record the transactions (including item quantity (which will be calculated), initials of the user, date and transaction type - which will determine if quantity is increased or decreased).

I also haven't figured out how to permit access to higher level functions for the Parts Manager, and a Developer login.

Thanks folks!

mjd

 

[jdraw]

Review this inventory/stock control article by Allen Browne.

 

[JLCantara]

Attached is MY version of your issue solution. Of course, you will find strange mods but take the time to examine their signification. Note the somewhat complexity of handling actual stock without keeping it in a table (read JDraw link). Any question can be sent as PM.

Good luck, JLC.

 

[mjdemaris]

Well, too bad I don't read French...

Can the same thing be accomplished using the Macro builder, that you have in the frmParts code?

And, what is =Choice("frmName","1_1",0) ?

Thanks.

 

[mjdemaris]

jDraw, I've read the article, but I am getting conflicting advice. Some say store the OnHand, StockTake and such in the Parts table, others say use a separate table, and Browne says not to store the OnHand data.

So, if I have any number of users checking parts out any number of times per day, 24/7, and 3000 parts, StockTake occurs twice a year...what is the best way to do this?

Thanks.

 

[JLCantara]

@MJdeMaris: did I use French somewhere?

The Choice function was left because I am lazy. I made a Nutrition app that can 'speak' French, English, Spanish and Portuguese. This coding is a way to localise what needs to be translated.

No macro can perform the somewhat complex tasks of the OnCurrent proc.
OK, I admit that the code is not simple but it's correct unless you want to shovel forward lots of problem: I think you should accept this level of complexity.

 

[mjdemaris]

Can you take me through how this code works? I don't see any results on the forms.

But the basic idea is to lookup the transaction history for a part, and calculate the Current Stock, correct?

Thanks.

 

[JLCantara]

But the basic idea is to lookup the transaction history for a part, and calculate the Current Stock, correct?

Yes. But notice that you need a criteria to start the calculation. For any given part, that's the last StockTake.

Can you take me through how this code works? I don't see any results on the forms.

You will have to be more specific about what you don't understand.

I just realized that I gave you a rather complex piece of code. Note that only Protective... has any value. Actual stock is left for you: you have 2 queries to do the job...

I noticed ambiguities (that's another way to say errors!!!) so now here is a new version of the BEAST.

 

[mjdemaris]

Well, I figured out the login issue and writing transactions to the Transaction table. Still having a problem summing transactions based on type: Removal, Restock, StockTake and Shrinkage.
I have queries that pull all four types, but actually adding/subtracting them has me stumped.
And, the latest mod code for Parts Update does not work. "Item not found in this collection." Referring to:

Set qdf = CurrentDb.QueryDefs("sqlLastStockTakePart_prmPart")

 

[JLCantara]

Attached is the final (!!!!) version of frmParts and frmPartsDisplay. Note that I use 2 queries in both case because I didn't yet found a way to do it with a single one (and very familiar with VBA, and quite lazy...). Multi-storage location is probably irrelevant to you project: so ignore it. Don't forget that actual stock calculation needs an initial StockTake record in the Transactions.

Good luck, JLC.

 

[mjdemaris]

Thank you! This might just answer all my asked and unasked questions.