record locking

I have the following table structures:
Cartons:  CartonID (autoID), TrackingNumber, RANumber, DateReceived, etc...
CartonContents: ContentID (autoID), CartonID, etc...

RANumber, and sometimes even TrackingNumbers can be found on several cartons. In other words, records in table Cartons can look identical.

When it comes time to enter the contents, staff pull the boxes, several staff at the same time working the same batch of cartons.  I was planning on having them select a carton record and start entering the content data.  Then I thought it may be safer to have an unbound form that when clicking 'Add' it grabs an available CartonID.  The thing I need to guarantee about is that two staff don't try and use the same CartonID at the same time but either method seems it could be possible for users to 'clash'

Any suggestions?
LVL 7
UniqueDataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In other words, records in table Cartons can look identical.
This is somewhat troubling, since a single Record in a table really should be unique. If it's not, then either (a) you haven't really built the system correctly or (b) the Carton doesn't really need a unique table. Either way, a review of your process and business rules would be in order.

That said: If the records are identical, then why would you care if the users choose the same CartonID? It really wouldn't matter, unless you have to track specific Cartons (at which point my earlier comment would be very relevant).

Can you tell us more about your business, and what the real-world process is?
0
UniqueDataAuthor Commented:
So a shipment comes in with printed rma labels. If it comes in truck freight, the shipping company assigns one tracking number to all the cartons. And if they are from the same customer, all the cartons will have the same rma number. So as far as labels, they will all be identical. So say 20 cartons come in with the same indentfiers. When it comes time to pull them and see what is inside (days later) each inspector would just grab a few of the twenty and enter details into the contents table. The contents would be tied to a carton but it doesnt matter which, as long as it hasnt been used by someone else already.

However, shipments that come in ups, fedex, and usps will usually have unique shipper tracking numbers. So when those get inspected there will be no worries about grabbing a carton id.
0
PatHartmanCommented:
Doesn't the truck freight company or the original shipper put a "1 of n" number on the individual cartons along with the rma number?  How do they keep track of the pieces?  How do you fill out an insurance claim?

If push comes to shove and you really don't have anything unique on the specific carton, you can update a record to "lock" it.  Then, your form would check for the "lock" indicator and not let anyone else update the record.  When the update is complete, you have to unset the "lock".  You will also need a query to find and clear locks because invariably, locks get left on when you use this type of process.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The contents would be tied to a carton but it doesnt matter which, as long as it hasnt been used by someone else already.
So if you receive a skid with 20 cartons on it, you'd have twenty identical records in your Cartons table. All you care is that you grab a record for the Carton+RMANumber+TrackingNumber that is not yet being used in the CartonContents table?

I agree with Pat - it would seem odd that a specific package could not be identified. I deal with a LOT of material movement, and I've yet to see that sort of setup.
0
UniqueDataAuthor Commented:
I guess an alternative would be to have them go to a receivings table first and add an "end of receiving posting" that takes all the cartons that contain the same identifiers and makes them one record, with a qty of 20 in the Cartons table. However, the contents table would have things like 5 widgets, XL, 4 Blue thingys.  I would need to change that somehow so I know those two came in one carton so I can track how many cartons have been inspected and how many are outstanding.  But that could be a "group posting" process too so they enter all the contents of one box and post it so they all get an identifier indicating they were all one carton.

hmmm.. that may be the safest method.  Because updating a "lock" field still has the slight possibility that two users could grab the same carton id number and lock at the same exact moment, right?
0
PatHartmanCommented:
No.  Using the lock process will prevent that.  If two users read the same record and both try to lock it by updating the record, one will succeed and the second will fail with an error message from the database engine that someone has changed the record since you read it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's always possible, depending on the level of usage and such. Pat's suggestion to create a "Lock" field is something I've done in the past, and it works well enough for the most part.

If an RMANumber or TrackingNumber are unique, it might be worthwhile to consider something like this:

tRMA
-----------
ID
RMANumber
DateReceived
NumCartons
etc etc

Then associate CartonContents with tRMA, and have a field where the user could indicate which "carton" they're working on (i.e. 1 of 20, or 10 of 20, etc).
0
UniqueDataAuthor Commented:
I will go ahead and go this route.  Thanks for your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.