How to make automatic Search Form?

[ittechguy]

I've created a search form which works quite well. A user selects a value from a combo box and then hits the search button (cmdSearch) and the form is filtered using the Click event.





I would like to remove the search button and make it so that the form is automatically filtered as soon as a user selects a value from one of the combo boxes. I believe this would be in the AfterUpdate event of each combo box. If that is the case, I am not sure how to break up the code into several different combo boxes. I appreciate any help I can get.

   Dim startStr As StringDim strFilter As String
If Not IsNullOrEmpty(Me.cboSearchLastName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "'))"
End If
If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.FirstName ='" & Me.cboSearchFirstName & "') OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.FirstName ='" & Me.cboSearchFirstName & "'))"
End If
If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & ") OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.OrganizationFK =" & Me.cboSearchOrganization & "))"
End If
If Not IsNullOrEmpty(Me.cboSearchShopName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.ShopNameFK =" & Me.cboSearchShopName & ") OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.ShopNameFK =" & Me.cboSearchShopName & "))"
End If
If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "(tblBuilding.BuildingPK IN (" _
    & " SELECT tblFacilityMgr.BuildingFK " _
    & " FROM tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK " _
    & " WHERE tblCustomer.OfficeSymFK =" & Me.cboSearchOfficeSym & ") OR "
    strFilter = strFilter & "tblRooms.RoomsPK IN (" _
    & " SELECT tblRoomsPOC.RoomsFK " _
    & " FROM tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK " _
    & " WHERE tblCustomer.OfficeSymFK =" & Me.cboSearchOfficeSym & "))"
End If
If Not IsNullOrEmpty(Me.cboSearchBuildingName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "[BuildingPK] =" & Me.cboSearchBuildingName
End If
If Not IsNullOrEmpty(Me.cboSearchRoomName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "[RoomsPK] =" & Me.cboSearchRoomName
End If
If Not IsNullOrEmpty(Me.cboSearchEquipmentName) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "tblRooms.RoomsPK IN (" _
    & " SELECT tblCabinet.RoomsFK " _
    & " FROM tblCabinet INNER JOIN tblEquipment ON tblCabinet.CabinetPK = tblEquipment.CabinetFK " _
    & " WHERE tblEquipment.EquipmentName ='" & Me.cboSearchEquipmentName & "') "
End If
If Not IsNullOrEmpty(Me.cboSearchSerialNum) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "tblRooms.RoomsPK IN (" _
    & " SELECT tblCabinet.RoomsFK " _
    & " FROM tblCabinet INNER JOIN tblEquipment ON tblCabinet.CabinetPK = tblEquipment.CabinetFK " _
    & " WHERE tblEquipment.SerialNum ='" & Me.cboSearchSerialNum & "') "
End If
If Not IsNullOrEmpty(Me.cboSearchEquipmentIP) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & "tblRooms.RoomsPK IN (" _
    & " SELECT tblCabinet.RoomsFK " _
    & " FROM tblCabinet INNER JOIN tblEquipment ON tblCabinet.CabinetPK = tblEquipment.CabinetFK " _
    & " WHERE tblEquipment.EquipmentIP ='" & Me.cboSearchEquipmentIP & "') "
End If
Call MsgBox(strFilter, vbOKOnly, "Debug")
If DCount("*", "qryFrmMainRooms", strFilter) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
Call cmdReset_Click
End If
Me.Filter = strFilter
Me.FilterOn = True

[ssanfu]

I would like to remove the search button and make it so that the form is automatically filtered as soon as a user selects a value from one of the combo boxes.

I think this is a bad idea unless 99.9% of the time only one search term is selected. If a user has 5 terms to select/search on, you will have to wait through 5 searches to get the final results.




Having said that, lets say the button is named "btnSearch" and the event is "btnSearch_Click".

In the after update event of each of the combo boxes add the line

Call btnSearch_Click

And set the button's visible property to FALSE. You will have everything set up to return to the button functionality - just comment out/delete the after update events for the combo boxes.

[ittechguy]

Thanks for your help ssanfu! I had no idea it was just that easy.

You do make a good point though. I don't imagine it will be an issue because usually no more than 2 combo boxes are used at the same time. But I've set it up as you said and if I don't like it after a day or two of testing, I'll comment it out.