Create SQL Search form to populate form

[ittechguy]

I have a unbound form which contains no data. I need to populate the form and also 3 listboxes with data using a search form which contains several search boxes in the header of my form.

My idea is to create several search boxes. Some combo boxes, some text boxes. I need to use the search criteria to populate both a building ID in txtBuildingID and Room ID in txtRoomID. Then I have 3 list boxes which populate data based on the building ID and room ID. That is, rooms POC, facility manager, and cabinets.

My problem is I do not know how to build a search form using multiple search criteria. I need to be able to search by building name, room name, last name, first name, organization, shop name, cabinet name, switch name, switch ip address.

I can easily build a search form which searches by only building and room, but adding customers names and such complicates things.

[Seth Schrock]

You just have to put an And or Or between each criteria expression in your WHERE clause. For example,

WHERE BuildingID = Forms!YourForm!txtBuildingID And CustomerName = "'" & Forms!YourForm!txtCustomer & "'"

[ittechguy]

Thank you Seth. But I don't think you're understanding what I need to do.

"txtCustomer" is not located on my form. No data is stored on my form. I need to use sql queries to populate a building ID field and a room ID field. So I cannot search WHERe buildingID = txtCustomers. Its more like WHERE BuildingID = SQL Query for customers where building ID = txtSearchLastName.

That is, I need to use a query to search through the actual table (not form) for whatever value I input into txtSearchLastName.

And unfortunately, I need multiple search criteria, each searching tables (sql query) for a value from my search text boxes.

Hope that makes sense to you.

[Seth Schrock]

Your search boxes have to be on a form (or a report I guess, but I can't imagine that you would be doing your search on the report). In my first reading, I had gotten the idea that txtCustomer was one of your search boxes, but reading again I realize that is incorrect. However, the same principle applies. You just need to change the name of the control. This doesn't search the form, but it uses values on the form as the criteria when it searches the table. Data can only be stored permanently in tables, so that is what I was assuming that you were talking about. You had mentioned that you could get one criteria at a time to work, but not multiple. That is where the And and Or statements come in. They combine multiple search criteria into one query.

[ittechguy]

I'm sorry, I'm still not understanding you. Or you're not understanding me.

txtSearchLastName is a text box that is supposed to search the last name of my Customers table. So, suppose if Mr. Smith is the point of contact for roomsID 10 which is listed in my table as building A, room 100. If I search for "Smith," I should see "10" in txtRoomsID.

Because smith is not located anywhere on my form, I need to do this with a query. I do not know how.

[Seth Schrock]

Okay, so lets put names to everything. You have a table called Customers, a field I'll call LastName, a form I'll call frmSearch, and a textbox called txtSearchLastName. Your query would be

SELECT CustomerID, FirstName, LastName, roomsID, buildingID 
FROM Customers 
WHERE LastName = Forms!frmSearch!txtSearchLastName

This performs a search based on a single criteria. Now to get the results in txtRoomsID, then you need to have your form bound to the query and txtRooms bound to the roomsID field. From your first post, txtBuildingID would be bound to the buildingID field. txtSearchLastName would remain unbound.

[ittechguy]

Thank you for the help.

When you say txtBuildingID would be bound to the building ID field, you mean the form must be created based on a query? I'm trying to avoid using a bound form.

I currently have this code here:

Private Sub cmdSearch_Click()
Dim sqlSearch As String
sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName, tblCustomer.FirstName FROM " _
           & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
           & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
           & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "'"
 
Me.txtBuildingID.ControlSource = sqlSearch
 
End Sub

That is not working correctly. I need txtBuildingID to be bound to facilitymgr.BuildingFK from the query above.

[ittechguy]

Also something is wrong with my select statement.

[ittechguy]

I'm making some progress. I needed to set the recordsource based on the SQL statement, not apply filter.

Here is my code:

Private Sub cmdSearch_Click()
Dim sqlSearch As String
If Not IsNull(Me.cboSearchLastName) Then
sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
        & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
        & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
        & " WHERE LastName ='" & Me.cboSearchLastName & "'"
        End If
Me.RecordSource = sqlSearch
End Sub

I have two text boxes, txtBuildingID and txtRoomID. txtBuildingID is bound to BuildingFK. txtRoomID is bound to RoomsFK. If I search for a last name, I see all the records for rooms owned by that last name. At the bottom of the form, I can scroll through the records. Record 1 of 10, as an example. It works great!

But life is not without problems, it seems. I also need to filter by facility manager. I want to be able to filter both by RoomsPOC and by Facility Manager.

I've tried using two search criteria using using AND between them. But that requires two separate SQL statements. Which doesn't allow me to bind txtRoomsID to RoomsFK because RoomsFK doesn't exist in the second SQL statement. How can I fix this?

Private Sub cmdSearch_Click()
Dim sqlSearch As String
If Not IsNull(Me.cboSearchLastName) Then
sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
        & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
        & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
        & " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
        End If
If Not IsNull(Me.cboSearchLastName) Then
sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName FROM" _
           & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
           & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
           & " WHERE LastName ='" & Me.cboSearchLastName & "'"
           End If
Me.RecordSource = sqlSearch
End Sub

[Seth Schrock]

You don't create two SELECT statements, you just have two criteria in the WHERE clause when using the AND statement. However, looking at your two queries, they have different tables that they are pulling from. You would have to combine these queries so that all the fields are available in one query and then you can have both criteria in your WHERE clause.