Automatically allocate stock?

[edster]

I have a database for taking orders for books, leaflets etc, what i want to do is this:

When I receive a delivery of stock I enter how many of each ‘Product’ I have received, I then want Access to allocate these to the various orders to make then ready for collection.

For example, if five people have all ordered 3 of ‘Product Guide’ and I only receive 15 then the first three to order are allocated three each.

Not got a clue how to do this, guessing I’ll need to code it?

Hope it makes sense

 

[statsman]

Generally, I think you would go about it the other way. That is, you have so much in stock and then you assign it to those who have ordered it.

I believe the Northwind database (the sample that comes with Access) has a section on "back-orders". You may want to consult this.

 

[ScottGem]

I have a database for taking orders for books, leaflets etc, what i want to do is this:

When I receive a delivery of stock I enter how many of each ‘Product’ I have received, I then want Access to allocate these to the various orders to make then ready for collection.

For example, if five people have all ordered 3 of ‘Product Guide’ and I only receive 15 then the first three to order are allocated three each.

Not got a clue how to do this, guessing I’ll need to code it?

Hope it makes sense

Your example doesn't make sense, 5*3 = 15. so you just allocate the 15 you received to the pending orders.

If you received only 9, then you would want to allocate an a FIFO basis. If you want to do this via automation, you could use a DO While that loops thru the outstanding orders.

 

[edster]

sorry my fault, confused myself!!

it sounds like you have managed to understand what i want to do, how would i code the Do While so that it stops allocating when it runs out of stock?

many thanks

 

[ScottGem]

Something like this:

Dim intInStock As Integer

intInStock = your received stock #

Do While intInStock > 0
Open query of back orderes sorted by orderdate
Do Until EOF
Select first back ordered record
Update Backordered quantity
intInStock = intInStock - backordered quantity
Loop
Loop