Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Three-strikes warning

    This question is related to the same database as thread, Check if control is empty. In the same for where an employee is selected for work, is a tick box, which is ticked if the employee doesn't turn up (No show). What I want to do is, if the same employee has 'No Show' ticked 3 times or more, an alert comes up. I know how to do a count on the tick box control, but not how to relate it to that employee.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,240
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Rob,

    On the AfterUpdate event of the checkbox (tick box) use the DCount function to count the True values for the employee.
    Code:
      iNoShows = DCount("[Field Name for No Show]", "TableName", "[EmployeeID] = '" & Me!EmployeeId & "'")
     If iNoShows >=3 then
      MsgBox "appropriate message here", vbokonly+vbcritical, "Persistent No-Show Warning"
    End if
    Please note the above is untested AIR code! HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    This part of the code

    Code:
    iNoShows = DCount("[NoShow]", "tblEvent Assignment", "[EmployeeID] = '" & Me!EmployeeID & "'")
    is giving me a data type mismatch. I've checked all controls are spelled right. I've even tried changing NoShow from True/False to Yes/No. Still no joy.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,240
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Rob,

    Ok, here's a tested version.
    Table:
    Rob2.JPG
    Form:
    Rob1.JPG
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub EmplName_BeforeUpdate(Cancel As Integer)
    
      Dim iNoShows As Integer
      
      iNoShows = DCount("[Noshow]", "Events", "[EmplName] = '" & Me!EmplName & "'")
    '*** Note: Noshow is a Yes/No field! ***
    
      If iNoShows >= 3 Then
        MsgBox Me!EmplName & " is unreliable!" & vbCrLf _
               & vbCrLf & "Please Schedule another Employee.", _
               vbOKOnly + vbCritical, "Persistent No-Show Warning"
        Cancel = True  '*** Return to form to enter new employee"
      End If
      
    End Sub  'Noshow_BeforeUpdate
    Please note that I placed the check on the Employee name field and used the BeforeUpdate event so that if the message is displayed it returns to the form w/o saving the record so you can enter a new name. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks RG. That looks exactly what I want, but there is still a problem. Because I set the form up with combo's to select the name, and the way I have the tables linked (other peoples suggestions, as I not sure what the best way is), only the EmployeeID, is stored in the Events table, and not the actual name. Is this why it's throwing up a mismatch. I replaced EmplName with EmployeeID.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorted it. Used your code, but created a query to give me the name, instead of the table. Thanks a lot for your help.

Posting Permissions

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