Why can't you just specify an auto-enter serial number field in Tickets for this? What's different about the number from Jobs? I think I can recommend a fairly straight forward fix here but need to understand the link between Jobs and Tickets.
We do multiple "jobs" each year. The company wanted invoice numbers that were based on each job. So, for instance if the job name is Avery 2012 the invoice numbers would be Avery - 12 - 0001 and so on. The number starts with 1 for each jub and incriments only for that job. The field that holds the current number is located in the job record for that reason. The record locking occurs when someone is in the Job record while an invoice is being created.
I was afraid of that. This is a classic example of why I recommend that you don't use such a numbering scheme in a multi-user database.
Try this first:
Create a new table for managing the latest job number for each Job. That way edits on the Job table won't create a record lock against another user editing the field. Then I'd put a unique values validation rule on the Ticketnumb field to catch any instances where you still end up with a duplicate value in the field. On rare instances when this might happen, the validation error can pop up, the user can revert and try again and should then be able to get a unique value entered.
Your JobNumbers table could have two fields: JobID, a number field to link to the jobs and tickets tables and LatestTicketNumb, a number field to increment with each new ticket record for that job.
Once you have that working, you can try updating the script to check for duplication and try again if the value returned is a duplicate.
Thanks Phil. I was thinking I would have to move that field to a table of it's own. I'll put it together on mMonday.
Got it, Thanks Phil :)