Solved

record locking

Posted on 2014-10-27
8
129 Views
Last Modified: 2014-11-05
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?
0
Comment
Question by:UniqueData
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 85
ID: 40408115
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
 
LVL 7

Author Comment

by:UniqueData
ID: 40408489
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 40408519
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
ID: 40408602
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
 
LVL 7

Author Comment

by:UniqueData
ID: 40408620
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
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40408667
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
 
LVL 85
ID: 40408670
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
 
LVL 7

Author Closing Comment

by:UniqueData
ID: 40424807
I will go ahead and go this route.  Thanks for your time.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question