How to Lock a table in VBA

[robsant]

Hi,

I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.

Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.

Thanks,

Rob

 

[Galaxiom]

You probably have this requirement because you are storing the stock quantity. Avoid this and you avoid the problem. Stock quantity should be calculated from the transactions, not stored.

The stocktake, receiving and dispatch records should include times that allow the current stock to be calculated for any moment.

Quarantining the physical movement to match the transactions is the real issue.

 

[Galaxiom]

To quarantine stock movements:

A stocktake time is designated.

A dispatcher removing stock after this time leaves a note in the bin indicating how many they took. The stocktaker adds this to their count.

When the count of the bin is completed the stocktaker leaves a note as a flag. When entire counting is complete a stocktake close time is designated.

If stock is added to the bin before the close time the storeman knows that anything he receives after the stocktake start time and before the close time needs to be added to the count but only if the stocktaker's flag is present when they physically add the stock.