Count Previous WorkUnits

[aldeb]

Below is the code that I have put into a Text box on my form.
What I am trying to accomplish is as follows:

When a Work Unit number already exists I want this text box to show how many times it is in the database.

If it does not exist I want it to say it is in the database 0 times. Below is the code I have. The code just keeps counting every Work Unit and I need it to compare Work Unit numbers. If Work Unit 123456 is entered into the Work Unit Form Field and it is in there 6 times already I need it to say that. Right now it is just counting Work Unit totals and not Work Unit specific. Also I need the WorkUnitCount Text box to clear for each new form.

Private Sub WorkUnit_AfterUpdate()

   Dim count As Integer
   Dim SL As String, DL As String
   
   SL = vbNewLine
   DL = SL & SL
      
   count = DCount("[WorkUnit]", "WorkUnitsFaultsMainTBL")
   
   If count = 0 Then
   Me.WorkUnitCount = "This Work Unit currently has 0 Total Fault(s)"
   
   Else
                             
   If count > 0 Then
   Me.WorkUnitCount = "This Work Unit currently has " & count & " Total Fault(s)"
      
   End If
   End If
  
End Sub

 

[Fear Naught]

You need to add a criteria in the DCount() function of the actual work unit you are trying to count. Your existing code just counts all records in the table.

HTH