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

Posted on 2012-08-15
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.
Question by:TechGuise
    1 Comment
    LVL 26

    Accepted Solution

    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)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now