Iterative code needed

[Crusado]

Hi

I think this should be straight forward, but I'm new to VBA in Access and can't figure it out Here's what I'm trying to do:

I'm trying to create an order of several products from a supplier with different costs associated with each product. This bit is fine, I can work out how many of each product I need. However, each supplier has a minimum cost of order for them to deliver. I therefore need to take the order that I definately need, and if it's less than the minimum cost to deliver, I need to keep adding cases of products that will run out soon until the minimum cost is achieved.

I've attached a word doc showing example tables.

From these, the "cold drinks" supplier has a minimum order cost of £10, but the current order only comes to £8.50. I need the code to look at the "Current Order" table, see that "Water" will run out first (minimum of "Days to Zero Stock" column for "Cold Drinks"), add the cost of an extra water to the delivery cost (£8.50 + £0.30 = £8.80), which is still too little, then move to the next product to run out - "Lemonade" and do the same procedure. Then keep doing it until the £10.00 has been reached, then fire out final order quantities to another table (or update the original).

Any help / suggestions / example code would be great!

Thanks in advance

 

[The_Doc_Man]

Try thinking backwards.

Synthesize a table as a work-in-progress order (WIPO) in which you order the minimum or standard order or whatever for every possible product from every active supplier. Add a yes/no field to this list that says "CONFIRMED." Include fields for your supplier, product, order size, cash value - plus the confirmation flag. Compute the amount of each product on-hand. Include that in the record.

This is a TEMPORARY table, purists... I use a stored computed value only to simplify the process. Bear with me.

Write a query that orders the unconfirmed WIPO records in ascending order of product on hand. This might take a tricky query, but once you have it figured out, it is just another recordset.

Open the recordset of unconfirmed orders.

Here's your loop.

Requery the recordset. Check for being at EOF. If it is EOF, you are done.

Otherwise, for the current (unconfirmed) WIPO record,

If qty-on-hand is 0, confirm the record as-is by setting the CONFIRMED flag and updating the record. Jump to the bottom of the loop.

use DSum with explicit criteria limiting it to the current supplier to compute the cash value of the WIPO order for all currently confirmed records. (Remember, the open recordset is only unconfirmed records!)

Use DLookup on the supplier to find the minimum order size or cash value.

OK, you have all you need to make a decision. You will have two possible results at this point.

A: Supplier's order minimum is still not met. Confirm the current order record.
B: Supplier's order minimum is met. DELETE the order record. (Erase it.)

Bottom of the loop: Now return to the top of the loop.

When you are done, in one pass plus the DSum and DLookup as needed, the WIPO table lists supplier, product, amount, cash value, and some things you can now discard. This should be a fairly simple loop.

Here is the key: The WIPO table as a temporary supplies you with everything you need to determine whether to keep or eliminate each record. In plain language, you examine each possible order to see if (a) you need it because you have none or (b) you need it to build up the order size. You throw away the temporary order records that don't fit (a) or (b) above. And the ORDER BY qty-on-hand optimizes this to bring in only those things that you need now or WILL need soon.

 

[Crusado]

Thanks! Sounds like a useable process - but you've made me think of more complicated parts I should try and add in! Realistically, I need to recalculate predicted Zero Stock dates after increasing the order for an item, and each iteration should evaluate the item with the minimum date (as an extra case for 1 item might be 1 days stock, whereas for another, it could be a week's, so I should order 2 cases of the first item, not 1 of each).

Just got to figure out the VBA code now.... :-(

 

[The_Doc_Man]

Ah, well, what price automation? (You're about to find out...) Good luck.

Just a hint. Get the base process working first. Then go back and see where there is room for other tests. Second hint. Any time you confirm an order in my loop as written earlier, you are done. If you want to add another test for this projection, do it in such a way that when you confirm the order, you are done, but you don't erase the WIPO record until NONE of your fancy projections, target optimizations, and stock files has confirmed the record. In other words, give yourself every chance to confirm the record, and if you can't, then dump it at the bottom of the loop.