Search subform from mainform

[ittechguy]

After a closer look, I noticed RoomsPOC was hidden under relationships in the database I uploaded. I can see how that could confuse you. Here is what it looks like with nothing hidden:

[orange]

I looked at your form and did some "testing". I noticed that all of the buildings had rooms 100 and 200. And I also noticed that when you selecting a building, and then a room there was nothing to restrict the rooms list to only those in the building (already selected).
It was confusing because the rooms were all named 100 or 200. So I changed the data such that in Bldg A the rooms became 1100 and 1200; in Bldg b 2100 and 2200; Bldg C 3100 and 3200; bldg D 4100 and 4200. This highlighted the non connected values in cboSearchBuildingName and cboSearchRoomName. I did not change RoomsID.
I modified that, then realized that it wasn't rooms within the bldg -it should be Rooms within the selected Building that have an identified POC or (POCs) -since we're searching for POCs.
Since in the sample data Bldg B room 100 does not have a POC, it should not be included in the list of rooms for Bldg B when you are searching for POC.
I'm attaching a jpg showing the frmSearchTest with Bldg C Room 3200 selected. After 3200 is selected, the lstbox is populated with matching POC records. I think that's what you want.

--jpg for searching BldgC Room 3200 --

[orange]

Following on from last post, I was trying to show Facility Mgrs. Instead o f making a form or subform for each search, I thought of a form with different Pages.
So using your frmTestSearch, I created a new form Form1. Same header info (I'm not using last name --yet).
I thought about a single form with the search criteria in the header, and a number of pages, each with a listbox to display results.
When you choose a Bldg in the cboSeachBuildingName, in the after update event, it searches and identifies Facility Mgr info for the Bldg and places if in a listbox lstFMgrs on the page labelled FcyMgr for Bldg (behind the scenes); then still in the after update event of the cboSearchBuildingName it sets up the rowsource of the
cboSearchRoomName to only Rooms in the bldg selected that have POCs. (This is the cascadiing effect --only show rooms related to the bldg selected in cboSearchBldgName.)
Then, if you select a Room, in the after update of the cboSearchRoomName, it populates the listbox for POCs for that Bldg, that Room. I placed the lstRoomsPOC listbox on the page labelled POC for Bldg/Room.

I'm attaching 3 jpgs (ignore the pages labelled Page 7, Page 8, Page 16 etc they are not used but were added in the design)

1- Form1 showing the selections in the header form1HeaderSelections
2 -Form1 Facility Mgrs for selected Bldg
3- Form1 POC for Bldg C Room 3200

You could have search results in different pages, then just move page to page for specific result.

Anyway, post your comments.

[ittechguy]

Thank you! I feel we're getting somewhere.

Tabs would work, likely less clutter and easier to navigate.

Do you have a copy of the database w/ changes? I'd like to look at it and see if I can learn a thing or two.

[orange]

Hi,

Yes I'm going to attach what I have as a zip. I did make a change. I changed txtSearchLastName to a combo in the header of Form1. This lets you select a Customer (LastName). I also added a page with a tab Cust is POC/FMgr. After selecting the Customer name in the combo, this populates the lstCustInfo with customer info Bldg, Room and whether the person Is A POC or FMgr . Try it -you'll see some data. I didn't set Show ColumnHeads to Yes for this, but you could, as was done in the listbox for FMgrs and listbox for POCs.

I'm attaching a jpg of Form1 and the CustInfo page

Let me know if you get the zip file and how it works.
Good luck

[ittechguy]

Now we're getting somewhere!

I took your database and added tables onto customers and equipment as to remove duplicate data. I also changed all of the table names so that they begin with "tbl" and changed Primary Keys to PK and Foreign keys to FK. After working with this database for a while, I've decided that was the naming scheme I want to stick with.



Now I'm trying to make it so that I see names instead of numbers (IDs) in my list box.



I can go into the lookup tab of the actual table and set it to combo box and it will get the values based on a query. However, those values do not show up in the list box. I'm thinking I need to go into the row source of the list box and open up the query builder and click on the lookup tab and then select a query for it to get its values. I must be doing something wrong, again.

CleanedupDatabase_JED00.zip

[orange]

Is this the database you are now working with?
I see an extra table in the relationships ---tblSecOptions_1 again???

When you say you want to see names not numbers you're talking OrganizationName, ShopName etc in the form--right?

I would NOT use lookups at the table field level. They hide what is really going on. Save a copy of the table. I will save a copy and do some additional work re Names not numbers and get back a little later.

[ittechguy]

Yes, I forgot to delete tblSecOptions_1. Sorry about that.

Yes, I'm talking about OrganizationName, ShopName, etc. in the form.

I kinda figured I shouldn't use lookups at table field level, I just didn't know what else to do.

I really appreciate the help here! You've helped me learn so much about access.

[orange]

Here is another cut at it. I removed some SQL and used named queries for names not numbers.
Database name has new number.

What do you really want to display for Customer --most of the left side is all duplicate?????????

Let me know what you think or what things are an issue.

Here is view of form1 with POC and names

[orange]

Here's another page

[ittechguy]

I feel stupid, I was over thinking this again. All I needed to do was add the other tables to the query for the row source.

[ittechguy]

Is there any way I could change the form so that instead of "Cust Is POC/FMgr" displaying when searching for "Last Name," it would requery both "FcyMgr for Bldg" and "POC for Bldg/Room"?

That is, I would like to be able to see both tabs by searching for building name, room name, or customer's name. And then if I searched for "Jones" and there was more than one room which Jones owned, it would simply include multiple records which could be scrolled through using the record selector on the bottom.

[orange]

???Isn't that what is happening now???

If you select a Value in cboSearchBuildingName
--you get all FMgrs for that Bldg and
--you get all cabinets in all rooms in that Bldg

And if you now select a Room (cboSearchRoomName) in the already selected Bldg (above)
--you get the POCs for the Room

If you select a Customer in the lastname box
--you get all the Bldgs for which he/she is FMgr and
--you get all of the Rooms for which he/she is a POC


NOTE: By you get it means that the page on the tab has been populated with the data. Just click on the various tabs, the data is there.

I haven't changed the LastName result to give Names and no numbers. That was a question in post 54.

[ittechguy]

CleanedupDatabase_JED02.zip

I changed the pseudo data so that it would not be as confusion. Each customer is now only a facility manager of only one building. 1260 is Bldg A, 1261 is Bldg B, etc.

Now, if you open up Form 1 and select "DerpyDerp" under LastName, it will populate "Cust is POC/FMgr" however it will not populate "FcyMgr for Bldg" or "POC for Bldg/Room." If I select building "A" then I see "Snuffy" as the facility manager for building A.

I'm trying to create a more centralized way to search all different criteria. I would like if the facility manager and the room poc tabs populated based on either building search, room search, or last name.

An idea I had was to have two text boxes display building ID and room ID based on a search, and then populate list boxes based on what those text boxes displayed. Like this:

This is easily done with building name and room name. However I do not know how to also populate building ID and room ID by customer ID. Also I run into a problem if running a search brings back multiple building IDs and/or room IDs. An example, if I search "snuffy" and he is the POC for multiple rooms. I would like it to display multiple building and room IDs, each on their own separate page and allow me to scroll through them with the record selector on the bottom of the form. So if searching "snuffy" brought back 10 records, it would display "record 1 of 10."

[ittechguy]

Please let me know if you think what I'm trying to do is not possible, or just not worth the effort.

I'm working on coding a search button using this code:

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

I'm having a hard time getting txtBuildingID to display the value of "tblfacilityMgr.BuildingFK"