I'm trying to create a search form which is located in my main form. I need it to filter several subforms. My main form contains building name and room name.
Jun 16, 2004 - Situation: Unbound Main form is open. It has two subforms, A and B. Both A and B are in datasheet view. Subforms A and B are based on two. Nov 1, 2017 - Forms in Access can be embedded into other forms, creating a main. The streamlined process makes for efficient data entry and quick lookups based on specific needs. Main form using CustomerID for the subform's “Link Master Fields”. The form header contains unbound combo boxes to filter and find.
I have a subform for customer information (first name, last name, phone, etc). I have a subform for cabinet information, and a subform within that subform for switch (equipment) information. If viewing a record on my main form, that main form will display the building name and the room number. Then the subform displays information about that building.
For an example, if viewing building 1, room 100, it will show Bob Smith as the POC for that room. I need to be able to search for 'Bob Smith' and see all of the rooms which he is the POC for. So, I need to search in the subform from the main form. I've been searching the web for weeks now.
I've seen every video I could possibly see. I cannot figure this out.
This is my code here. Let's take it one appropriate step at a time. The first step, as indicated in my previous post, is for you to explore that article and learn the techniques you'll need. If, when you've done that, you still have problems then we'll take the next step which will only be clear at that point.
If, at any later stage, I feel I need to see your project then you can be sure I'll make a request for it. Remember, this is your project. We can offer help, but you're the one doing the heavy lifting. You're not obliged to follow any advice we give, but should you choose not to then don't be surprised if you find yourself in uncharted territory. My belief is that once you've spent a little time playing with the example in that article you will understand well enough not to need any more help, but we can only be sure when we get to that stage.
Private Sub cmdSearchClick. Dim strFilter As String. If Not IsNull(Me!txtSearchLastName) Then. strFilter = strFilter &. '(Forms!frmMainRooms!frmSubFacilityMgr.form!LastName Like ' &. Me!txtSearchLastName & '.' )'.
Dim res As Integer. res = MsgBox(strFilter, vbOKOnly, 'Debug'). Me.Filter = strFilter. Me.FilterOn = True. End If. End Sub. My msgbox displays (Forms!frmMainRooms!frmSubfacilityMgr.form!LastName Like 'Smith.'
) When I hit okay, it shows me a blank form. Something must be wrong with my syntax, but I got it right from the link you posted.
ITTechGuy: Something must be wrong with my syntax, but I got it right from the link you posted. Not the problem part you didn't. The rest is fine, but nowhere do (or would) I refer to a control or field in the filter from a Forms reference. The main problem of course, is that you're trying to set the.Filter for the main form instead of that of the subform. Excuse me if I get some details wrong.
I'm trying to work with very little information as to what fits where in your setup, but I'm working on the basis that LastName is a field used on the subform frmSubFacilityMgr. In such a scenario, and with txtSearchLastName on the main form, I think you need some code like the following. Private Sub cmdSearchClick. Dim strFilter As String. If Not IsNull(Me.txtSearchLastName) Then.
strFilter = strFilter &. '(LastName Like ' &. Me.txtSearchLastName & '.'
)'. Call MsgBox(strFilter, vbOKOnly, 'Debug'). Me.frmSubFacilityMgr.Form.Filter = strFilter. Me.frmSubFacilityMgr.Form.FilterOn = True. End If. End Sub. That effectively filters my frmSubFacilityMgr subform.
However, it does not go to that record from the main form. If I am viewing Building 1, room 100 in my main form and two facility managers are showing for that building (Bob Jones and Mike Smith). I need to be able to search for 'jones' and see building all of building 1 records. Or I can search for 'Smith' and see all building 1 records.
In other words, if I search 'Smith' I should see all records from my main form which contain 'Smith' in frmSubFacilityMgr subform. Now, suppose Mr. Smith also owned building 2.
I should then see all of building 1 and also building 2. Right now, this code only filters my subform. Smith and Mr. Jones are facility managers for building 1, if I were to search for Jones only the record for Jones shows up in my sub form, and Smith does not. ITTechGuy: In other words, if I search 'Smith' I should see all records from my main form which contain 'Smith' in frmSubFacilityMgr subform.
This intention wasn't clear to me before. It may well have been in the question somewhere but certainly not in a way I understood. This is a game-changer, of course. First and foremost, it means that we need to understand the relationship between the main form and the subform very clearly. Second, it means that we are no longer interested in filtering the subform after all, but only the main form. Unfortunately the filtering could be much more complicated depending on the relationship between the information.
At this point I cannot proceed in any detailed manner without information pertaining to the data in the recordset of the main form, the data in the recordset of the relevant subform, and how exactly they are linked. However, I can explain in more general terms what we're looking for. Typically, the subform recordset will be linked to the main form recordset by a foreign key. That is to say a value stored in the subform recordset that matches the PK of the main form recordset. Sometimes, less often, the main form recordset will also contain the actual value you are planning to filter on. If that is the case in your situation then this is the easiest to handle. You simply set the filter on the main form to match the value typed in, very much as we have already illustrated for the subform.
On the other hand, and in the more common scenario, you are going to need to filter by matching the PK of the main form to a subquery. The subquery would reflect filtering the same recordset as the subform using the same Like 'XXX.' approach, and it would return a list of valid PK values that would be used in the filter.
Let me see if I can outline this for you with an example. I'll make it similar to what you have, at least as far as I know from what you've included. For simplicity, though, it'll be a school with rooms for Maths and English across various buildings.
There are two tables, tblBuilding and tblRoom. tblBuilding has a PK of BuildingID that is numeric. tblRoom has a PK of RoomID which is alpha-numeric and consists of three alpha to reflect usage and three numeric as a sequence.
Each room also has BuildingID as an FK as well as a separate field TeacherName to indicate which teacher. There are three buildings (1, 2 & 3) and each building has a Maths and an English room within it.