Checking to see if a record is currently OPEN, if so not letting anyone else open it.
We have a FORM that users use to create TICKETS for purchase items. {frmPurchaseTickets}The main part of this FORM has things like CUSTOMER, DATE, CLERK, etc... The FORM also has a SUB-FORM that is used to list items that make up the TICKET (Item, QTY, Cost Ea, etc..)
It is very common, that a clerk will open a new TICKET, Enter some data, then place the TICKET "on hold" (this is done by clicking a button that simply closes the FORM without marking it paid).
Another FORM shows a list of all "On Hold" TICKETS. {frmTicketSelector}. This form is based on a query that filters out all Tickets that are not showing PAID. When the Clerk gets ready to finish a TICKET, they click a button on frmTicketSelector which opens the cooresponding record in the frmPurchaseTickets FORM.
HERE IS THE PROBLEM...................
There are usually 4 or 5 workstations opening, holding, re-opening, and finishing TICKETS.
Despite all that I have tried, I have been unsuccessful in keeping two people from opening the same ticket.
HERE IS WHAT I HAVE TRIED.................
(1) Created a field in the underlying Ticket table and called it "CurrentlyOpen". Set the value of that field to TRUE when a user opens the TICKET. Have tried doing this in OnLoad, OnCurrent, OnOpen..... (all of them). This method works probably 90% of the time, but occassionaly is not able to perform action. I'm assumming that it is because either the form was opening too slowly or perhaps another user tried opening the same record a second after the first person. This method also seems to hang (not very often) when putting the FORM on hold so that it shows back up on the frmTicketSelector FORM.
(2) Created a new TABLE and called it tblOpenTickets. When a TICKET is put on hold, I run and SQL APPEND statement that adds that TICKET# to the tblOpenTickets. Then I tied the frmTicketSelector FORM to the tblOpenTickets TABLE. When someone clicks on a TICKET from the frmTicketSelector FORM, I run and SQL DELETE statement that removes that TICKET# from the tblOpenTickets TABLE. Again, it works most of the time, but sometimes just seems like it can't update the table quick enough.
in both instances, I have tried requery-ing the frmTicketSelector by using OnTimer all the way down to ½ a second, and also requery-ing when they move the mouse over the list. With both methods, it is still possible to open a TICKET on one workstation, and then immediately open the same ticket on another workstation.
Hoping that someone knows of a better method of checking to see if a record as been opened or perhaps there is something to do with Record Locking that I don't understand.
It is very common, that a clerk will open a new TICKET, Enter some data, then place the TICKET "on hold" (this is done by clicking a button that simply closes the FORM without marking it paid).
Another FORM shows a list of all "On Hold" TICKETS. {frmTicketSelector}. This form is based on a query that filters out all Tickets that are not showing PAID. When the Clerk gets ready to finish a TICKET, they click a button on frmTicketSelector which opens the cooresponding record in the frmPurchaseTickets FORM.
HERE IS THE PROBLEM...................
There are usually 4 or 5 workstations opening, holding, re-opening, and finishing TICKETS.
Despite all that I have tried, I have been unsuccessful in keeping two people from opening the same ticket.
HERE IS WHAT I HAVE TRIED.................
(1) Created a field in the underlying Ticket table and called it "CurrentlyOpen". Set the value of that field to TRUE when a user opens the TICKET. Have tried doing this in OnLoad, OnCurrent, OnOpen..... (all of them). This method works probably 90% of the time, but occassionaly is not able to perform action. I'm assumming that it is because either the form was opening too slowly or perhaps another user tried opening the same record a second after the first person. This method also seems to hang (not very often) when putting the FORM on hold so that it shows back up on the frmTicketSelector FORM.
(2) Created a new TABLE and called it tblOpenTickets. When a TICKET is put on hold, I run and SQL APPEND statement that adds that TICKET# to the tblOpenTickets. Then I tied the frmTicketSelector FORM to the tblOpenTickets TABLE. When someone clicks on a TICKET from the frmTicketSelector FORM, I run and SQL DELETE statement that removes that TICKET# from the tblOpenTickets TABLE. Again, it works most of the time, but sometimes just seems like it can't update the table quick enough.
in both instances, I have tried requery-ing the frmTicketSelector by using OnTimer all the way down to ½ a second, and also requery-ing when they move the mouse over the list. With both methods, it is still possible to open a TICKET on one workstation, and then immediately open the same ticket on another workstation.
Hoping that someone knows of a better method of checking to see if a record as been opened or perhaps there is something to do with Record Locking that I don't understand.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.