How to Lock a table in VBA (1 Viewer)

[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

 

[Uncle Gizmo]

I'm assuming you have a typical front end, back end arrangement with tables on a server which everyone accesses from their own copy of the front end. You could add a table (on the server "backend") in which you would store the current system status. Basically you would have the check box type field which you could set to true when you needed exclusive access for a particular process.

You wouldn't do this by locking down at the table level, you do this by altering the access the users have to the data in the tables through the forms they use. The controls on the form (usually command buttons) will inspect the flag you set on the server and dictate whether or not to allow a particular operation to take place.

 

[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.

 

[Uncle Gizmo]

Further to Galaxioms insight, you may find this from Allen Browne Helpful.