MacroScope
Programmer
I have written a live queue for an insurance company. When a user inputs info on a web form it is immediately placed in a staging table in Access 2007 housed on SQL Server 2008. The count of unassigned records in the queue is displayed on all screens and updated every 5 seconds. A double-click on one field of the queue form causes an append query to run to append the chosen record of web info to our main data table and it then opens the already fully populated record on screen for attention.
The overall system works perfectly, but the problem I'm having is that it's not infrequent that two people try to double-click the same record at approximately the same time, causing duplicates and confusion. Using record locks I can get the standard clipboard error, "Another user has changed the data..." to occur, which accomplishes the task of halting the second record writing process, but I am not satisfied to leave it that way. Is there a particular property that evaluates to true that I can use to detect the potential for the error before it happens, so that I can stop the actions before writing to the record and causing the actual error?
I thought that the Dirty property might work, but it seems to be only for the form you're writing on at the time, and not for the database state. DataChange evaluates to a string, and while I can use a Len() operation to detect if the string is greater than zero length and get a True-False result, that doesn't seem to work for the underlying table, but instead only for the form on screen.
The very first operation is to change the text in [QuoteStatus] from "Unassigned" to "Assigned".
I essentially would like to determine in advance that if I proceed with the write operation the error will occur and be able to use that to stop all activity and just pop a message saying the record is already assigned to someone else.
I hope that's clear enough.
As always, all help is greatly appreciated.
The overall system works perfectly, but the problem I'm having is that it's not infrequent that two people try to double-click the same record at approximately the same time, causing duplicates and confusion. Using record locks I can get the standard clipboard error, "Another user has changed the data..." to occur, which accomplishes the task of halting the second record writing process, but I am not satisfied to leave it that way. Is there a particular property that evaluates to true that I can use to detect the potential for the error before it happens, so that I can stop the actions before writing to the record and causing the actual error?
I thought that the Dirty property might work, but it seems to be only for the form you're writing on at the time, and not for the database state. DataChange evaluates to a string, and while I can use a Len() operation to detect if the string is greater than zero length and get a True-False result, that doesn't seem to work for the underlying table, but instead only for the form on screen.
The very first operation is to change the text in [QuoteStatus] from "Unassigned" to "Assigned".
I essentially would like to determine in advance that if I proceed with the write operation the error will occur and be able to use that to stop all activity and just pop a message saying the record is already assigned to someone else.
I hope that's clear enough.
As always, all help is greatly appreciated.
