Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Post Unanswered: Help with form to check ticket barcodes

    Hi All, I have a somewhat simple procedure that i want to setup a database for ticket scanning (box office style) in access which i need some help with.

    The simple breakdown is;

    I have serial numbered tickets which i want to be able to scan with a barcode scanner (will fill a field with serial number) , which then checks the number to see if it exists, then checks to see if it has already been scanned. If not scanned it marks that number as checked, displays the name associated from a table of data and waits for the next scan. If scanned already shows a warning message(to avoid ticket fraud).

    I know this should be too hard, though it has been quite a few years since working with access and my memory is failing me.

    My table setup is simple: ticket # (auto and prim key) , name, phone, ticket sold(true/false), ticket entered(true/false), ticket scan(for barcode entry).

    I just need to setup the following procedure in a form;

    -ticket scanned

    -if exist check ticket sold is true, if sold check ticket entered is false,

    -if not ticket entered is false display "confirmed" and possibly the name & number associated with ticket.

    -if ticket entered is true then display "warning ticket already scanned"

    I hope my description is straight forward, Im unsure if i could get away with this just using a query and some coding in the form, or would need VB but any help would be extremely appreciated and i may end up making this publicly available for others once i finished and it is working well.

    Thanks in advance

    Troy

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're not going to get away without coding anything. I've never tried using barcode scanners with Access forms, but I daresay that that will be the simplest part of it.

    With regard to checking the ticket, I would advise that you use a recordset. This will allow you to perform one pass over the table, and from that pick up all of the required fields for analysis and updating. The following air code assumes that the scanner dumps the code into a field call txtBarcode, your table is called tblTickets and the form is unbound. The form has fields txtName and txtWarning. You will also need to set up a link to the MS ADO library (in the VBA editor, it's Tools -> References).
    Code:
    Private Sub txtBarcode_AfterUpdate()
        Dim rstTicket As ADODB.Recordset
        Dim strSQL as String
    
        Set rstTicket = New ADODB.Recordset
        strSQL = "SELECT [Name], [Ticket Sold], [Ticket Entered] FROM tblTickets WHERE [Ticket Scan] = '" & txtBarcode &  "'"
        rstTicket.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
        If rstTicket.EOF Then
    
            'This barcode was not found in the table
            txtWarning = "Invalid ticket!"
            GoTo TidyUp
    
        End If
    
        'The ticket exists.  Has it been sold?
        If Not rstTicket![Ticket Sold] Then
    
            txtWarning = "This ticket has not been sold yet!"
            GoTo TidyUp
    
        End If
    
        'Has the ticket already been used?
        If rstTicket![Ticket Entered] Then
    
            txtWarning = "This ticket has already been used!"
            GoTo TidyUp
    
        End If
    
        'So, the ticket exists, has been sold and has not been used.  Record its use and display the holder's name.
        txtName = rstTicket![Name]
        rstTicket![Ticket Entered] = True
        rstTicket.Update
    
    TidyUp:
    
        rstTicket.Close
        Set rstTicket = Nothing
    
    Exit Sub
    You might want to do more with it, but that should get you started.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •