Search form to remove duplicates from union query

[ittechguy]

I'm not sure if I have a SQL problem or a vba problem so if I've posted in the wrong section, I'm sorry.

I have a search form which uses a union query as its RecordSource. I need to use a union query because I need to search through both Building Facility Managers and points of contact for rooms. I need to be able to search by Last Name, First Name, Organization, Building Name, Room Name, etc.

The way I've set the search form up, is I have search boxes which are all combo boxes. The user selects one or more combo box value and then hits search. At which point the union query is filtered. I have two text boxes which are bound to BuildingFK and RoomsFK. So whatever the user searches by, they should see in those two text boxes.

The problem I'm having is that those two text boxes sometimes contains duplicate data. For instance, suppose I search for BuildingName "A" RoomName "1100". Because there are three different people who are listed as the POC for that room and/or the facility manager for building "A" I see three records all of BuildingID 1, RoomsID 10. As you can see in the attachment, record 1 of 3.

I don't care if there are duplicates in the union query (and in turn the record source for the form). I just need to only see a unique building ID and a unique room ID in txtBuildingID and txtRoomsID. Its fine if there are more than 1 record, as long as the values in both text boxes are unique. For an example, you could search for LastName "Smith" and see that he owns BuildingID 1, RoomsID 10, 11, and 12.

Which brings out an important point. The combination of txtBuildingID and txtRoomsID must be unique. But there can be multiple records of txtBuildingID by itself. This is because as I said, a search of "Smith" could return Building ID 1, Room ID 10, Building ID 1, Room ID 11, building ID 1, Room ID 12, etc.

How can I do that?

Here is my code:

Private Sub cmdSearch_Click()
Me.RecordSource = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK" _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON" _
& " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " UNION SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK"
Dim strWhere As String
Dim lngLen As Long
     If Not IsNull(Me.cboSearchLastName) Then
          strWhere = strWhere & "[LastName] ='" & Me.cboSearchLastName & "' AND "
             End If
             If Not IsNull(Me.cboSearchFirstName) Then
          strWhere = strWhere & "[FirstName] ='" & Me.cboSearchFirstName & "' AND "
             End If
             If Not IsNull(Me.cboSearchOrganization) Then
          strWhere = strWhere & "[OrganizationFK] =" & Me.cboSearchOrganization & " AND "
             End If
             If Not IsNull(Me.cboSearchShopName) Then
          strWhere = strWhere & "[ShopNameFK] =" & Me.cboSearchShopName & " AND "
             End If
             If Not IsNull(Me.cboSearchOfficeSym) Then
          strWhere = strWhere & "[OfficeSymFK] =" & Me.cboSearchOfficeSym & " AND "
             End If
             If Not IsNull(Me.cboSearchBuildingName) Then
          strWhere = strWhere & "[BuildingFK] =" & Me.cboSearchBuildingName & " AND "
             End If
             If Not IsNull(Me.cboSearchRoomName) Then
          strWhere = strWhere & "[RoomsPK] =" & Me.cboSearchRoomName & " AND "
             End If
        Call MsgBox(strWhere, vbOKOnly, "Debug")
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        Call MsgBox(strWhere, vbOKOnly, "Debug")
      '  MsgBox "No Records Found."
      Dim Msg As VbMsgBoxResult
 
'If DCount("*", "strWhere") = 0 Then
' Msg = MsgBox("No corresponding records to your search criteria." & vbCrLf & vbCrLf & _
 '   "Do you want to try again?", vbCritical + vbYesNo)
 ' If Msg = vbYes Then
  '  'Code to open a form
'  Else
'    'Code to open other form
'  End If
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Private Sub txtBuildingID_AfterUpdate()
Me.lstFacilityMgr.Requery
End Sub
Private Sub txtRoomsID_AfterUpdate()
Me.lstRoomsPOC.Requery
End Sub
 
'Private Sub Form_Load()
'set the controls to empty on load
'Me.cboSearchBuildingName = ""
'Me.cboSearchRoomName = ""
'Me.cboSearchLastName = ""
'End Sub

[ittechguy]

I'm guessing I could use DCOUNT to remove duplicates, somehow. I would need to specify what I consider a duplicate, which would be txtBuildingID and txtRoomsId combined.

[ittechguy]

Or can this be done w/ SQL?

[ittechguy]

I'm not sure if it matters, but I've changed my code as I was having problems before.

Option Compare Database
Option 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 lngLen As Long
    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")
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        'strWhere = Left$(strWhere, lngLen)
        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
        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
 
 

[ittechguy]

I've spent way too much time researching this.

Best I figure, I need to further filter results AFTER the recordsource has been filtered by the search boxes. Seems this is either done w/ DCount or with RecordCount. The important thing is that the combination of txtBuildingID and txtRoomsID is unique. Or tblFacilityMgr.BuildingFK and tblRooms.RoomsPK, whichever is easier.

I don't think recordcount can look in two fields. So I think I need DCount. Seems if my life depended on it, I can't get the syntax right.

[ittechguy]

I'm making progress. Below is the DCount code which counts the records of txtBuildingID and txtRoomsID. I've tested it and confirmed it works.

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

Now, I need to remove the records which are duplicates. Not from the union query, but from the form recordsource. How do I do that?

It has to have something to do with my filter variable, strWhere. An idea I had was to remove one record from strWhere until there are no more duplicates. Not sure if thats even possible.

[NeoPa]

FYI:
I'm not ignoring this thread because I don't like you. I've looked at it, but frankly it's far too involved and complex a question to post on a forum. I would be surprised if this were not true of many other experts.

It's your responsibility (albeit an onerous one sometimes as simplifying complicated problems is often very tricky) to present something which is straightforward enough to work on without taking hours out of our already busy days. It's also true that you need to provide enough detail to work with and getting a balance isn't easy, so I'm not criticising. Nevertheless, in the real world we need to have the time in order to help you, and if a question takes this long just to grasp what it is you're asking for, many (most) will avoid it.

[hvsummer]

It not like I want to avoid like everyone usually do, but the fact, too complex situation that make me can't understand any piece of your picture ==
if you need help, breakdown it into basic, simple model, and we find the way to solve it, then you can apply that model into your complex DB. I alway do this when I ask everyone here in this forum.
hope you could find the way soon. sometime we can only rely on ourself if everything become uncontrolable (mean you can't simply the problem) situation.