We help IT Professionals succeed at work.
Get Started

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

TechGuise asked
Last Modified: 2013-01-19
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
Practice Manager
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE