469,337 Members | 6,592 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,337 developers. It's quick & easy.

Create SQL Search form to populate form

70 64KB
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.
Attached Images
File Type: jpg database.jpg (33.8 KB, 132 views)
Sep 28 '15 #1
9 1327
Seth Schrock
2,962 Expert 2GB
You just have to put an And or Or between each criteria expression in your WHERE clause. For example,
Expand|Select|Wrap|Line Numbers
  1. WHERE BuildingID = Forms!YourForm!txtBuildingID And CustomerName = "'" & Forms!YourForm!txtCustomer & "'"
Sep 28 '15 #2
ittechguy
70 64KB
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.
Sep 29 '15 #3
Seth Schrock
2,962 Expert 2GB
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.
Sep 29 '15 #4
ittechguy
70 64KB
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.
Sep 29 '15 #5
Seth Schrock
2,962 Expert 2GB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerID, FirstName, LastName, roomsID, buildingID 
  2. FROM Customers 
  3. 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.
Sep 29 '15 #6
ittechguy
70 64KB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim sqlSearch As String
  3. sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName, tblCustomer.FirstName FROM " _
  4.            & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
  5.            & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
  6.            & " WHERE tblCustomer.LastName ='" & Me.cboSearchLastName & "'"
  7.  
  8. Me.txtBuildingID.ControlSource = sqlSearch
  9.  
  10. End Sub
That is not working correctly. I need txtBuildingID to be bound to facilitymgr.BuildingFK from the query above.
Oct 3 '15 #7
ittechguy
70 64KB
Also something is wrong with my select statement.
Oct 3 '15 #8
ittechguy
70 64KB
I'm making some progress. I needed to set the recordsource based on the SQL statement, not apply filter.

Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim sqlSearch As String
  3. If Not IsNull(Me.cboSearchLastName) Then
  4. sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
  5.         & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
  6.         & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
  7.         & " WHERE LastName ='" & Me.cboSearchLastName & "'"
  8.         End If
  9. Me.RecordSource = sqlSearch
  10. 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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim sqlSearch As String
  3. If Not IsNull(Me.cboSearchLastName) Then
  4. sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
  5.         & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
  6.         & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
  7.         & " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
  8.         End If
  9. If Not IsNull(Me.cboSearchLastName) Then
  10. sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName FROM" _
  11.            & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
  12.            & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
  13.            & " WHERE LastName ='" & Me.cboSearchLastName & "'"
  14.            End If
  15. Me.RecordSource = sqlSearch
  16. End Sub
Oct 4 '15 #9
Seth Schrock
2,962 Expert 2GB
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.
Oct 5 '15 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by skgolden | last post: by
3 posts views Thread by Christopher Lusardi | last post: by
4 posts views Thread by marek zegarek | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.