473,390 Members | 1,343 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

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, 151 views)
Sep 28 '15 #1
9 1622
Seth Schrock
2,965 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,965 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,965 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,965 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

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

Similar topics

6
by: skgolden | last post by:
My husband and I own a small temporary labor company and deal with 4 major clients (A,B,C & D), each of which has about 2 dozen units in our tristate area that we deal with (ie, Company A, units...
3
by: Christopher Lusardi | last post by:
Hello, Is it possible to do any of the following in the same application? (1) Have a form open up other forms (windows). (2) Have tabs with a form. Thanks, Christopher Lusardi
5
by: superjacent | last post by:
Hope someone can help. I have a saved parent form containing an unbound sub-form. I set the SourceObject (form) after the Parent Form opens/loads. The sub-form (datasheet view) basically...
4
by: marek zegarek | last post by:
Hello! I have strange and simple problem.... I am starting developer. I need to create webform, that will insert my data into SQL 2005 database. My idea is to make something like MS Access...
25
by: ali3n8 | last post by:
Hello I would like to create a search form for my database that searches by: First Last Contact Number Street City State Zip
4
by: Kelly Warden | last post by:
I have a search form with a sub data form. When i search for "john" for example I should get a bunch of records in my subform, however it is just showing the first one from the table. Here is the...
2
by: Amian | last post by:
So I'll explain to you everything that I'm doing and then let you know what I need help with b/c maybe I should be doing this a different way. I am trying to create a database for all patient...
37
ashjones86
by: ashjones86 | last post by:
Hi All, So im looking for some help with access 2007 if at all possible. I am new to access as work has asked me to create a database, so the database side to it i have sorted i.e inputting data...
1
by: fozzy79 | last post by:
Hi, I am trying to figure out how to do the following, I have a large table of parts, I want to create a form where I can filter this list (show in datasheet) view, to a point where i can see the...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.