Anything Access

Running a club lotto has always been a great way to raise funds, but managing it effectively can be challenging. To streamline this process, we recently introduced Clubforce, a dedicated software solution that allows players to participate in our club lotto online. With Clubforce, players can conveniently choose their numbers, pay through the platform, and take part in weekly draws without requiring any direct interaction with administrators. This modern approach has significantly reduced the administrative burden for our club volunteers while providing players with a hassle-free way to support the club. The same process would work for Lottoraiser.

The Manual Sales Dilemma

While online ticket sales via Clubforce or Lottoraiser are simple and efficient, we recognized that not all our members or visitors are comfortable with online transactions. Some still prefer the traditional method of purchasing tickets in person. Incorporating manual ticket sales into the weekly draw, however, comes with its challenges. For example, a user would need to transcribe these tickets into a CSV file and upload them into the system at the time of the draw. This process introduces a lot of manual effort and the potential for errors, which made us reconsider its feasibility.

A Custom Solution to Bridge the Gap

To address these challenges and expand the reach of our lotto without adding undue burden on our volunteers, we developed a custom application using Microsoft Access. This add-on complements the Clubforce platform by automating key aspects of manual ticket sales:

1. Quick Pick Number Generation: For manual ticket purchases, the system generates quick-pick numbers automatically, saving time and ensuring fairness.
2. Member Details Capture: By integrating a card-swiping feature, we can record a member’s username and email address instantly. This ensures accurate records and simplifies communication with participants.
3. Seamless Financial Integration: Ticket sale proceeds are processed directly through the club’s till system, such as the one in our bar. This not only simplifies accounting but also eliminates the need for handling separate cash transactions.
4. Customizable Parameters: The application is designed to be flexible. Clubs can configure their own parameters, including:
   • Club name and branding.
   • Ticket pricing and draw options.
   • Receipt formats.
5. Ticket Printing on Bluetooth Printers: Using printers that support CPCL commands, the application prints receipts for ticket purchases. This ensures participants have a physical copy of their ticket, maintaining transparency and trust. Sample ticket formats are displayed below for reference.
   

Expanding Participation Without the Hassle

This hybrid approach enables us to offer both online and manual ticket sales without creating additional administrative work. Visitors and members who may not be familiar with online systems now have an easy way to participate. By swiping a card or entering details manually, their entries are logged, and the proceeds flow seamlessly into our existing financial system. The integration with Clubforce ensures that all tickets, whether purchased online or manually, are included in the weekly draw.

A Scalable Solution for Other Clubs

We understand that every club faces similar challenges when managing fundraising initiatives. To help others overcome these hurdles, we’re making this custom Access application available for download. You can tailor it to your club’s needs, setting parameters such as pricing and ticket options. It’s a straightforward way to modernize your lotto system without significant investment in additional tools.

Getting Started

Below, we’ve included screenshots and detailed instructions for configuring the system:

• Setting up your club name and ticket options.
• Configuring Bluetooth ticket printers.
• Generating quick-pick numbers.
• Processing sales through the till system.

When you start the program for the first time a registration screen will be shown to register your clubs name. This will allow us to send you a notification of any program updates.

For other configuration click on Preferences and enter your club name , the com port for bluetooth printing and the number of numbers in your draw. In this example we have 31.

Export Configuration

Depending on the version of the software you are using the export format will vary. This screen will allow you to customise the selection and the available fields.

Financial Reports

This will list any sales for a given date range in detail or summarised by ticket type. You can also customise the financial report for your year by specifying the starting month.

Sample Code for printing Tickets using CPCL

Function PrintLottoTicket(tmpID, tmpLine1, tmpLine2, tmpLine3, tmpContact, tmpName, tmpTicketName, tmpTotal, tmpPrice, tmpLines, tmpNotes)
Dim comPort As String
Dim fileNum As Integer
Dim command As String
' Configure the COM port
comPort = GetPref("Com Port")

fileNum = FreeFile
Open comPort For Binary Access Write As #fileNum

If Len(tmpNotes & "") > 0 Then
    'cancelled print confirmation
    command = ""
    command = "! 0 200 200 270 1" & vbNewLine
    command = command & "TEXT 9 0 10 40  " & UCase(GetPref("Club Name")) & vbNewLine
    command = command & "TEXT 9 0 70 85 LOTTO DRAW " & vbNewLine
    command = command & "TEXT 12 0 10 135 Printed Date & Time :" & Format(Now(), "dd-mmm-yy hh:nn") & vbNewLine
    command = command & "TEXT 9 0 10 185 #: " & tmpID & " Cancelled " & vbNewLine
    command = command & "TEXT 9 0 10 220 ________________________" & vbNewLine
Else
    If tmpName <> "" And tmpLines = 3 Then
        '3 lines with a name entered
        command = ""
        command = command & "! 0 200 200 680 1" & vbNewLine
        command = command & "TEXT 9 0 10 40  " & UCase(GetPref("Club Name")) & vbNewLine
        command = command & "TEXT 9 0 70 85 LOTTO DRAW " & vbNewLine
        command = command & "TEXT 12 0 10 135 Printed Date & Time :" & Format(Now(), "dd-mmm-yy hh:nn") & vbNewLine
        command = command & "TEXT 4 0 10 155 ________________________" & vbNewLine
        command = command & "TEXT 9 0 10 190 Line 1: " & tmpLine1 & vbNewLine
        command = command & "TEXT 9 0 10 235 Line 2: " & tmpLine2 & vbNewLine
        command = command & "TEXT 9 0 10 280 Line 3: " & tmpLine3 & vbNewLine
        command = command & "TEXT 9 0 10 325 ________________________" & vbNewLine
        command = command & "TEXT 9 0 10 370 " & tmpTicketName & vbNewLine
        command = command & "TEXT 9 0 10 415 #: " & tmpID & " Price Paid : €" & tmpPrice & vbNewLine
        command = command & "TEXT 9 0 10 460 Contact : " & tmpContact & vbNewLine
        command = command & "TEXT 9 0 10 505 Name #: " & tmpName & vbNewLine
        command = command & "TEXT 9 0 10 550  " & vbNewLine
        command = command & "TEXT 9 0 10 595 Thanks for Playing " & vbNewLine
    ElseIf tmpName = "" And tmpLines = 3 Then
        'no name
        command = ""
        command = command & "! 0 200 200 630 1" & vbNewLine
        command = command & "TEXT 9 0 10 40 " & UCase(GetPref("Club Name")) & vbNewLine
        command = command & "TEXT 9 0 70 85 LOTTO DRAW " & vbNewLine
        command = command & "TEXT 12 0 10 135 Printed Date & Time :" & Format(Now(), "dd-mmm-yy hh:nn") & vbNewLine
        command = command & "TEXT 4 0 10 155 ________________________" & vbNewLine
        command = command & "TEXT 9 0 10 190 Line 1: " & tmpLine1 & vbNewLine
        command = command & "TEXT 9 0 10 235 Line 2: " & tmpLine2 & vbNewLine
        command = command & "TEXT 9 0 10 280 Line 3: " & tmpLine3 & vbNewLine
        command = command & "TEXT 9 0 10 325 ________________________" & vbNewLine
        command = command & "TEXT 9 0 10 370 " & tmpTicketName & vbNewLine
        command = command & "TEXT 9 0 10 415 #: " & tmpID & " Price Paid : €" & tmpPrice & vbNewLine
        command = command & "TEXT 9 0 10 460 Contact : " & tmpContact & vbNewLine
        command = command & "TEXT 9 0 10 505  " & vbNewLine
        command = command & "TEXT 9 0 10 550 Thanks for Playing " & vbNewLine
    Else
        If tmpLines = 1 And tmpName <> "" Then
            'single line ticket type
            command = ""
            command = command & "! 0 200 200 605 1" & vbNewLine
            command = command & "TEXT 9 0 10 40 " & UCase(GetPref("Club Name")) & vbNewLine
            command = command & "TEXT 9 0 70 85 LOTTO DRAW " & vbNewLine
            command = command & "TEXT 12 0 10 135 Printed Date & Time :" & Format(Now(), "dd-mmm-yy hh:nn") & vbNewLine
            command = command & "TEXT 4 0 10 155 ________________________" & vbNewLine
            command = command & "TEXT 9 0 10 190 Line 1: " & tmpLine1 & vbNewLine
            command = command & "TEXT 9 0 10 235 ________________________" & vbNewLine
            command = command & "TEXT 9 0 10 280 " & tmpTicketName & vbNewLine
            command = command & "TEXT 9 0 10 325 #: " & tmpID & " Price Paid : €" & tmpPrice & vbNewLine
            command = command & "TEXT 9 0 10 370 Contact : " & tmpContact & vbNewLine
            command = command & "TEXT 9 0 10 415 Name #: " & tmpName & vbNewLine
            command = command & "TEXT 9 0 10 460  " & vbNewLine
            command = command & "TEXT 9 0 10 505 Thanks for Playing " & vbNewLine
        Else
            command = ""
            command = command & "! 0 200 200 550 1" & vbNewLine
            command = command & "TEXT 9 0 10 40 " & UCase(GetPref("Club Name")) & vbNewLine
            command = command & "TEXT 9 0 70 85 LOTTO DRAW " & vbNewLine
            command = command & "TEXT 12 0 10 135 Printed Date & Time :" & Format(Now(), "dd-mmm-yy hh:nn") & vbNewLine
            command = command & "TEXT 4 0 10 155 ________________________" & vbNewLine
            command = command & "TEXT 9 0 10 190 Line 1: " & tmpLine1 & vbNewLine
            command = command & "TEXT 9 0 10 235 ________________________" & vbNewLine
            command = command & "TEXT 9 0 10 280 " & tmpTicketName & vbNewLine
            command = command & "TEXT 9 0 10 325 #: " & tmpID & " Price Paid : €" & tmpPrice & vbNewLine
            command = command & "TEXT 9 0 10 370 Contact : " & tmpContact & vbNewLine
            command = command & "TEXT 9 0 10 415  " & vbNewLine
            command = command & "TEXT 9 0 10 460 Thanks for Playing " & vbNewLine
        End If

    End If
End If
command = command & "FORM" & vbNewLine & "PRINT" & vbNewLine
Put #fileNum, , command
  
' Close the COM port
Close #fileNum
End Function

Function for generating the quick pick lotto numbers. Each set of 4 numbers will be unique from the given range defined in your parameters.

Function SelectUniqueRandomNumbers()
    Dim numbers As Collection
    Dim randomNumbers(1 To 4) As Integer
    Dim i As Integer, randIndex As Integer
    Dim result As String

    ' Initialize a collection with numbers 1 to 31
    Set numbers = New Collection
    For i = 1 To GetPref("No of Numbers")
        numbers.Add i
    Next i

    ' Randomize the seed for the Rnd function
    Randomize

    ' Pick 4 unique random numbers
    For i = 1 To 4
        ' Select a random index from the collection
        randIndex = Int((numbers.Count) * Rnd) + 1
        randomNumbers(i) = numbers(randIndex)
        
        ' Remove the selected number to avoid duplication
        numbers.Remove randIndex
    Next i

    ' Build a result string for display
    result = ""
    For i = 1 To 4
        result = result & randomNumbers(i)
        If i < 4 Then result = result & ", "
    Next i

    ' Output the results in a message box
    SelectUniqueRandomNumbers = result
End Function

Feel free to customize and adapt this solution for your own club. Together, we can make fundraising initiatives like club lotto more accessible, efficient, and enjoyable for everyone involved. If you need any support email support@anythingaccess.com.

You can download the sample application from our sample page.

Version History