DCount for filtered query

[ittechguy]

Can DCount be used to further filter a query after its been filtered by several search boxes?



In nearly every forum post I've found, DCount is used to count duplicates in a table, but not in a query. Further more, not in a a filter variable.

This code below works except it counts duplicates in the query BEFORE its been filtered by strWhere.

If DCount("*", "qryRecordSet", "BuildingFK = " & Me.txtBuildingID & " And RoomsPK = " & Me.txtRoomsID) > 0 Then                  MsgBox "There are duplicates!"
                  'Code to remove duplicate records here
                  End If

If DCount is not the correct tool for the job, what is?

[ittechguy]

After much testing, research, etc I've found that my code is actually correct. I needed to place it before my last End If for it to work correctly.

Here is all of the code:

Option Compare DatabaseOption Explicit  'always set this  It will point out errors with field/vaiable names




Private Sub cboSearchLastName_AfterUpdate()
    Me.cboSearchFirstName.Requery
End Sub


Private Sub cboSearchOrganization_AfterUpdate()
    Me.cboSearchShopName.Requery
End Sub


Private Sub cboSearchShopName_AfterUpdate()
    Me.cboSearchOfficeSym.Requery
End Sub


Private Sub cmdReset_Click()
    Me.cboSearchBuildingName = ""
    Me.cboSearchRoomName = ""
    Me.cboSearchOrganization = ""
    Me.cboSearchShopName = ""
    Me.cboSearchOfficeSym = ""
    Me.cboSearchLastName = ""
    Me.cboSearchFirstName = ""
    Me.FilterOn = False
End Sub
Private Sub Form_Current()
    Me.lstFacilityMgr.Requery
    Me.lstRoomsPOC.Requery
End Sub


Private Sub cmdSearch_Click()
    Dim strWhere As String
    Dim startStr As String
    If Not IsNullOrEmpty(Me.cboSearchLastName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[LastName] ='" & Me.cboSearchLastName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[FirstName] ='" & Me.cboSearchFirstName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[OrganizationFK] =" & Me.cboSearchOrganization
    End If
    If Not IsNullOrEmpty(Me.cboSearchShopName) Then
       startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[ShopNameFK] =" & Me.cboSearchShopName
    End If
    If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[OfficeSymFK] =" & Me.cboSearchOfficeSym
    End If
    If Not IsNullOrEmpty(Me.cboSearchBuildingName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[BuildingFK] =" & Me.cboSearchBuildingName
    End If
    If Not IsNullOrEmpty(Me.cboSearchRoomName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[RoomsPK] =" & Me.cboSearchRoomName
    End If
    If Not IsNullOrEmpty(Me.cboSearchEquipmentName) Then
        startStr = IIf(strWhere = "", "", " AND ")
       strWhere = strWhere & startStr & "[EquipmentNameFK] =" & Me.cboSearchEquipmentName
    End If
    If Not IsNullOrEmpty(Me.cboSearchEquipmentSerialNum) Then
       startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[SerialNum] ='" & Me.cboSearchEquipmentSerialNum & "'"
   End If
        If Not IsNullOrEmpty(Me.cboSearchEquipmentIP) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[EquipmentIP] ='" & Me.cboSearchEquipmentIP & "'"
    End If
    Call MsgBox(strWhere, vbOKOnly, "Debug")
        MsgBox strWhere
         If DCount("*", "qryRecordSet", strWhere) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                Me.FilterOn = False
                Me.cboSearchBuildingName = ""
                Me.cboSearchRoomName = ""
                Me.cboSearchOrganization = ""
                Me.cboSearchShopName = ""
                Me.cboSearchOfficeSym = ""
                Me.cboSearchLastName = ""
                Me.cboSearchFirstName = ""
         Else
            Me.Filter = strWhere
            Me.FilterOn = True
        If DCount("*", "qryRecordSet", "BuildingFK = " & Me.txtBuildingID & " And RoomsPK = " & Me.txtRoomsID > 1) Then
                  MsgBox "There are duplicates!" & vbCrLf & vbCrLf
                  'Code to remove duplicate records here
                  End If
                  End If
    End Sub






Function IsNullOrEmpty(val As Variant) As Boolean
   'First conditional validates for Nothing
   'Second condition validates for an Empty String situation "" or "     "
   Dim ret As Boolean: ret = False
   If IsMissing(val) Then
      ret = True
   ElseIf (val Is Nothing) Then
      ret = True
   ElseIf (val & vbNullString = vbNullString) Then
      ret = True
   ElseIf (Len(Trim(val)) <= 0) Then
      ret = True
   End If
 
   IsNullOrEmpty = ret
End Function

I've tested the DCount code by searching for a search which returns 2 records (one unique, and one duplicate). I set it to "> 1" and MsgBox said "There are duplicates!" If I set it to "> 2", I did not get a MsgBox. Thats as it should be.

Now, the tricky part. The MsgBox is just for troubleshooting. I need to filter out records which are not unique. How can I do that?