473,326 Members | 2,173 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,326 software developers and data experts.

Need help Filtering subform from main form

70 64KB
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. 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:

Expand|Select|Wrap|Line Numbers
  1.  If Not IsNull(Me.txtSearchLastName) Then
  2.         strWhere = strWhere & "([Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName] Like ""*" & Me.txtSearchLastName & "*"")"
  3.     End If
It just gives me a blank form, it doesn't work.

Is what I want to do even possible? What am I doing wrong?
Sep 24 '15 #1
10 2842
NeoPa
32,556 Expert Mod 16PB
Regardless of whether the item of information you want to filter by is on your main form, one of your sub-forms, not included in your data at all, the filter should be done with an unbound ComboBox. There are a number of articles which cover different scenarios that can be found starting at Example Filtering on a Form.

If you can't find what you need there then please come back here and explain why so that we can give you further, directly helpful, guidance.
Sep 24 '15 #2
ittechguy
70 64KB
Thanks for the reply.

My comboboxes are unbound. I'm attaching a copy of my database. if you open up Navigation Form, click on the rooms tab.
Attached Files
File Type: zip BuildingsSwitches3 (1).zip (76.7 KB, 126 views)
Sep 25 '15 #3
NeoPa
32,556 Expert Mod 16PB
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.
Sep 25 '15 #4
ittechguy
70 64KB
I've read over that link well. I still don't get it. Its not working.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.   Dim strFilter As String
  3.      If Not IsNull(Me!txtSearchLastName) Then
  4.          strFilter = strFilter & _
  5.                      "([Forms]![frmMainRooms]![frmSubFacilityMgr].[form]![LastName] Like '" & _
  6.                      Me!txtSearchLastName & "*')"
  7.          Dim res As Integer
  8.          res = MsgBox(strFilter, vbOKOnly, "Debug")
  9.          Me.Filter = strFilter
  10.          Me.FilterOn = True
  11.      End If
  12.  End Sub
  13.  
  14.  
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.
Sep 25 '15 #5
ittechguy
70 64KB
I did some troubleshooting. I found if I removed all the [forms]![frmMainRooms]... stuff and simply used a control from my main form (RoomsName), it worked fine.

It seems the problem is with how I'm referencing LastName from my subform. I know my path is correct. I created a text box on my main form and under the control source I typed in
Expand|Select|Wrap|Line Numbers
  1. =[Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName
That displays the last name of the person who is listed in my facilitymgr subform as being the facility manager.

I did notice though, it only displayed one last name. There is always more than one facility manager per building, sometimes several. I have my subform in datasheet view.
Sep 25 '15 #6
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim strFilter As String
  3.  
  4.     If IsNull(Me.txtSearchLastName) Then Exit Sub
  5.     strFilter = Me.txtSearchLastName
  6.     With Me.frmSubFacilityMgr.Form
  7.         strFilter = Replace("([LastName] Like '%F*')", "%F", strFilter)
  8. Call MsgBox(strFilter, vbOKOnly, "Debug")
  9.         .Filter = strFilter
  10.         .FilterOn = True
  11.     End If
  12. End Sub
Sep 25 '15 #7
ittechguy
70 64KB
Thanks. I did some more research before you replied, and I discovered that issue. I wasn't applying the filter for my subform.

Your code doesn't like the End If. It says End If without block If.
Sep 25 '15 #8
ittechguy
70 64KB
Okay, I've sorta figured it out. Not really.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.      Dim strFilter As String
  3.      If Not IsNull(Me.txtSearchLastName) Then
  4.      strFilter = strFilter & _
  5.                       "([LastName] Like '" & _
  6.                       Me.txtSearchLastName & "*')"
  7.  Call MsgBox(strFilter, vbOKOnly, "Debug")
  8.          Me.frmSubFacilityMgr.Form.Filter = strFilter
  9.          Me.frmSubFacilityMgr.Form.FilterOn = True
  10.      End If
  11. End Sub
  12.  
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. Like if Mr. 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.
Sep 25 '15 #9
NeoPa
32,556 Expert Mod 16PB
ITTechGuy:
Your code doesn't like the End If. It says End If without block If.
Indeed. My bad. Unfortunately, without your actual project to play around within I must develop air-code. I'm very careful to check it before posting, but there is definitely scope for me to make errors. In this case I allowed the line End If instead of End With.
Sep 27 '15 #10
NeoPa
32,556 Expert Mod 16PB
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.

Expand|Select|Wrap|Line Numbers
  1. [tblBuilding]
  2. {[BuildingID]} ==> {1}
  3.                    {2}
  4.                    {3}
Expand|Select|Wrap|Line Numbers
  1. [tblRoom]
  2. {[BuildingID] | [RoomID] | [TeacherName]} ==> {1 | 'MAT001' | 'Smith, Aaron'}
  3.                                               {1 | 'ENG001' | 'White, Glynis'}
  4.                                               {2 | 'MAT002' | 'Smithson, Bertrand'}
  5.                                               {2 | 'ENG002' | 'Brown, Howard'}
  6.                                               {3 | 'MAT003' | 'Topol, Christopher'}
  7.                                               {3 | 'ENG003' | 'White, Glynis'}
If we wanted to show, on the main form, those buildings which had rooms where the [TeacherName] started with 'Smith', then we'd set the filter up as :
Expand|Select|Wrap|Line Numbers
  1. ([BuildingID] In(SELECT [BuildingID] FROM [tblRoom] WHERE ([TeacherName] Like 'Smith*'))
The buildings this would return would be 1 & 2 only.
Sep 27 '15 #11

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

Similar topics

9
by: chris vettese | last post by:
On my subform I have a field in the footer that totals the value of a field. On the main form I have referenced this field. I'm using this field in a calculation on my main form. The problem...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
2
by: origin197511 | last post by:
Hello all... I'm having an issue with MSAccess 2000. I have a Form that holds records of my cartridge loads for a rifle and a subform that lists all groups that have been fired with that load. ...
7
by: Danielb | last post by:
I want my application to run most of the time as just an notify icon visible in the system tray, with some dialogs windows that open if the user selects an option from the context menu on the tray...
3
by: gsb58 | last post by:
Hi! A mainform is being used to show records from a table in an sql database. A button on the main form will load a new form that allows the user to add, delete, update and search certain...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
5
Scott Price
by: Scott Price | last post by:
Hello, I'm running Access 2003 trying to filter a subform with approximately 15 records per year per the main record contained on the parent form. The filter works fine if the subform is opened...
6
by: OllyJ | last post by:
Hope you can help guys. I have a scheduling database, the main schedule table contains the following fields: AddedDate Day/Night Machine Arm/Head StockCode ToolNumber
2
by: Constantine AI | last post by:
Hi can anybody help? I have a main form with customer details on that links to a sales order head subform through the customer ID. The sales order head subform is then linked to the sales order...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.