Autofill order table based on selection in form...

[mermaidboy]

Hello,

I am creating an inventory management system for a restaurant and have a question as follows:

I have a products table, with a field called ReorderQuantity. I want to populate this on form over several days and then when the total value per supplier reaches a minimum level (MinimumOrder field in SupplierTable) it will then enable my "checkout" button, take these reorder values and create an order in the OrderTable and its child, OrderLineItemTable so that I can generate & send orders. How do I do this?



Thanks,

Nick

[aytee111]

Figure out the trigger for this process - at what point in time do you want to check all of these numbers to decide whether to create an order or not. For instance, each time you take something out of inventory. Presuming this is a form, when the on-hand value changes you can create a routine that will do all the checking and create the order.

You must first write it out in plain sentences, how exactly you see this process happening.

[mermaidboy]

OK here goes:

My approach to inventory management works like this.

I have a TxQuantityTable with InventoryID, Quantity, and another called TxValueTableValue. I have a series of append queries that record each time I sell a menu item or waste something.

For example when I sell a burger it needs to append -120g beef, -1unit buns, -20g sauce etc from each inventory item. The same for value€. This all works fine for the - transactions. The idea is that the current inventory level is the net + / - total for each inventory item, with + being the corresponding quantities & value€ from orders placed and received.

I don't want to create an actual order until there is enough to order to satisfy the minimum order level set by each supplier (eg €350), and I don't want to accept into inventory any stock until the order has arrived and has been verified on site.

My idea is something like this:

In a query, I calculate ReorderQuantity: Int([TargetVolume] - [CurrentInventoryLevel])

I only want to trigger reorder when the difference between actual and target stock level is greater than 1 order unit (eg TargetInventory for oil is 40L, MinimumOrder is 1 x 12 pack of 1L bottles of oil, therefore trigger would be when CurrentInventory reaches 28L. If CurrentInventory level is anywhere between 16L and 28L, the ReorderLevel will be 1, and between 4L and 27L it would be 2 units.

I will then have a CurrentOrders Form, where I can see for each supplier, the total value of non-zero reorder items, and when this reaches the minimum reorder level eg €350 for that supplier, I can then press a button, and it will generate 1 record in my OrderTable (with the supplier name, delivery address, date etc) and several records in the child table OrderLineItemTable for each product. I can do this for each supplier as they meet the minimum order criteria.

I guess it could be fully fully fully automated, but I would like some level of human interface before the orders are actually sent out...

Not sure how much more detail to give at this stage, but happy to send files etc if / when appropriate.

Thanks a lot,

Nick

[aytee111]

Each time you decrease the inventory (sell a menu item or waste), in addition to running those append queries call a routine to do the calc for each product and provide a message which states "some items need to be reordered".

Create a form for reordering. This can be as simple as having a listbox of items that need to be reordered, double-clicking each one to create the order, display it, and you can manually accept it and/or make changes. This form can be run in conjunction with inventory entries above or stand-alone, available to you whenever you need it.

I don't get the value minimum order field. So far it has been number of units, with the calc as you stated it - to reorder oil the minimum is 1 12-pack, so how does the €350 fit in?

Write out the calculation in plain sentences/pseudo math, where it will cover all eventualities, such as
if CurrentInventory < TargetInventory then
if the difference is > MinimumOrder then
only one needs to be ordered
otherwise
the number that needs to be ordered is the difference divided by MinimumOrder rounded up

[Bulzie]

How does it work for each item or each Supplier? If say your buns reach the minimum limit but nothing else does, do you generate an order just for buns? What if in next hour say ketchup reaches the minimum for same Supplier, does it generate another order? Or do you figure all the items to order in a day, then at end of day create and send out the orders? You will still have product on hand so not like you are totally out of something.

[mermaidboy]

The €350 is the minimum for the total order value - otherwise the supplier will charge me for transport. Each line item is ordered in its own units - where 1 is the minimum you can order for that item eg:

12 x 1L pack of olive oil = 1 item
3 x 90 eggs = 1 item
2kg pack of minced meat = 1 item

Therefore, I don't want to order any olive oil until the difference between target inventory (defined by storage space) and actual inventory reaches at least 12L. The cost of ordering 12L olive oil is eg around €100, so I need to order other stuff as well from the same supplier in order to reach the minimum €350 level.

Since I have 500 or so different inventory items, I want to automate this process. I have no problem making the calculations above - the part I don't understand is this:

I can display on a form / report the inventory items which need reordering, and the quantity to be ordered - this is just calculated as above - it is not stored anywhere. When it is time to actually create & place an order, I need the same data to be stored in the OrderTable and the OrderLineItemTable. Eg:

Tuesday morning - I see the following on the ReorderForm:

InventoryName Quantity ProductID ProductName Supplier Value
Eggs 1 111 3x90 biofresh eggs Biofresh €30
Olive oil 2 222 12x1L bottles EV olive oil Biofresh €200
Mince 4 333 2kg pack minced beef Biofresh €120
Total €350

So I decide to place the order - I want this to be a manual process. I want to press a button on the OrderForm which will open the OrderTable & create a new record, as follows:

OrderID xxx
LocationID Resto1
SupplierID yyy
Date Tuesday
Placed? False (will switch to True when I actually place the order
Checked? False (will switch to true once order received into inventory)

And I want the same procedure to create the following records in the OrderLineItemTable:

OrerLineItemID OrderID ProductID Quantity Variance Notes
aaa xxx 111 1 0 Null
bbb xxx 222 2 0 Null
ccc xxx 333 4 0 Null

When the delivery is received, lets say 2 packs of mince were poor quality and returned, I will input -2 into variance. Then I want to press a button on the OrderForm which transfers these items into actual inventory. I currently do this by using an append query to create records in two tables called TxQuantityTable and a TxValueTable. These store transaction data for each inventory item from the beginning of time, so I can take a Sum of all orders received and all sales made during a period to calculate the curent inventory level.

If there is a better way to manage inventory I am happy to be given advice, but this works so far. The only part I don't know how to code is the event procedure to create new records in OrderTable and OrderLineItemTable.

Thanks for taking the time to look at this !

[mermaidboy]

Sorry - I had spaced everything out nicely, but when I posted, it removed all the spacing! Happy deciphering...

[aytee111]

Now I'm confused!

The only part I don't know how to code is the event procedure to create new records in OrderTable and OrderLineItemTable.

That's all you are asking? Add a command button which when clicked will run two append queries.

[mermaidboy]

OK but how does the 2nd append query know the ID of the record just created in the first append query?

[aytee111]

You can use DLookUp to get the Max ID on the orders table. After adding the records to the tables you can use that ID to filter the form so that you can see what has just been added.

[mermaidboy]

Great - thanks I will try. Any chance you can give me the correct syntax for DLookup & maxID? I guess I put it straight into the append query as an expression??

[aytee111]

1 - append record to orders
2 -
Dim MaxID as Long
MaxID=DLookUp("Max(id)","Orders table")
3 - append record(s) to order details
4 - filter form
Me.Filter="Id=" & MaxID
Me.FilterOn=True

[mermaidboy]

Great - you are a star thanks.

[aytee111]

You realize that the second query can't be an actual query, unless you change the SQL. You need to do it in VBA and concatenate the MaxID variable:
DoCmd.RunSQL "INSERT .... " & MaxID & " AS OrderID & "......"