We help IT Professionals succeed at work.

Checking to see if a record is currently OPEN, if so not letting anyone else open it.

TechGuise asked
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.
Watch Question

Applications Developer
Hi  TechGuise

have you had a look into pessimistic record locking, it can be a bit pedantic to set up, but once set up it will certainly stop other network users from modifying a record being edited on another machine. The application throws a 'Write conflict error' dialog, which can be handled/trapped using code:

There lots of info out there in the ether pertaining to pessimistic record locking, this one seemed on the mark:

Alan ";0)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.