Turn on and off Visibility of a Image by record count query

[capjlp]

Hi Folks

I have a current database that uses a form for registration at an event. We have a table with people that may register. When they come by we look them up then click a check box to register them for the event. We are having an event where we want to give away a prize to every 25th person that registers so we would like to have an image on the form become visible for every 25th registered person. all our data is pretty much in one table for this event. My thoughts were I could just use a count query for people in the registration table with a check mark on registered. Then do magic to make the image visible for every 25th person. We will give out the prize when they are registering so we want to see the image and give them their Prize. Seems like I would use an after update event on the Registered Check mark to update the state of the image.



Thank you

[ranman256]

at the SAVE button on click event

  vCount = dcount("*","table")
  if (vCount mod 25) = 0 then 
     image.visible = true
     msgbox "Congrats"
 else
          image.visible = false
  endif

[ssanfu]

In code, use a query to get the number of registered people. Then use the Mod function to determine if the 25th person.

Air Code:

Private Sub Registered_AfterUpdate()
    Dim r As DAO.Recordset
    Dim RC As Long

    'always hide image
    Me.WinnerImage.Visible = False

    'get number of people regixtered
    sSQL = "SELECT Count(IsSelected) AS CountOfIsSelected FROM Table1 WHERE IsSelected = True;"
    Set r = CurrentDb.OpenRecordset(sSQL)

    'check if records
    If Not (r.BOF And r.EOF) Then
        RC = r.RecordCount
        If RC Mod 25 = 0 Then   '<- 25 th person
            Me.WinnerImage.Visible = True  'show
        Else
            Me.WinnerImage.Visible = False  ' hide
        End If
    End If

    'clean up
    r.Close
    Set r = Nothing

End Sub

Change the red names to your table, field and image names.

[capjlp]

I'll give this a try than you!

[capjlp]

Ok I tried this and can't seem to get it to work I set it to 5 people for testing. I tested the image true and false call on a on click and it does work but not after update on the 5th peoples. I set a label up to be = r.recordset and its always 1

Private Sub chkRegistered_AfterUpdate()
'Count Registered and Turn on WinnerImage
Dim r As DAO.Recordset
Dim RC As Long

'always hide image
Me.WinnerImage.Visible = False


'get number of people regixtered
sSQL = "SELECT Count([Registered]) AS [Total Registered] FROM CustomerTable WHERE (((CustomerTable.Registered)=True));"
Set r = CurrentDb.OpenRecordset(sSQL)


'check if records
If Not (r.BOF And r.EOF) Then
RC = r.RecordCount
If RC Mod 5 = 0 Then '<- 5 th person
Me.WinnerImage.Visible = True 'show
Else
Me.WinnerImage.Visible = False ' hide
End If
End If


'clean up
r.Close
Set r = Nothing
End Sub

[ssanfu]

My bad I was doing a lot of cutting and pasting and cut the wrong lines.
Try this:

Option Compare Database   '<- should be the first line in every module
Option Explicit           '<- should be the second line in every module

Private Sub chkRegistered_AfterUpdate()
    'Count Registered and Turn on WinnerImage
    Dim r As DAO.Recordset
    Dim RC As Long
    Dim sSQL As String

    'always hide image
    Me.WinnerImage.Visible = False

    'save record
    Me.Dirty = False

    'get number of people registered
    sSQL = "SELECT Count([Registered]) AS TotalRegistered FROM CustomerTable WHERE CustomerTable.Registered = True;"
    Set r = CurrentDb.OpenRecordset(sSQL)

    'check count of records registered
    If Not (r.BOF And r.EOF) Then
        RC = r("TotalRegistered")
        If RC Mod 5 = 0 Then    '<- 5 th person
            Me.WinnerImage.Visible = True    'show
        Else
            Me.WinnerImage.Visible = False    ' hide
        End If
    End If

    'clean up
    r.Close
    Set r = Nothing
End Sub

Note: I removed the space in "TotalRegistered"