Page 1 of 5 12345 LastLast
Results 1 to 15 of 71
  1. #1
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218

    Search subform from mainform

    I have a form which contains several subforms. My main form displays building name and room name and then everything else is in subforms. There's a RoomPOC subform, a facilitymgr subform, and a cabinets subform. I need to be able to search from the main form by either building/room name or by a customer's last name, or by a cabinet name, or by a switch name (inside my cabinets subform). Basically, I want to be able to search through a subform and pull up that record in my main form. So if I search for building 1 room 1, it will show building 1, room 1 and then the room POC, facility manager, cabinets, switches, etc. If I search for Joe Smith and Joe Smith is the facility Manager for building 1, 2, and 3, I should see 3 records... for building 1, 2, and 3 and I should see all of the subforms within those 3 records.

    I've created a simple search form using this code here:



    Code:
    1. Private Sub cmdSearch_Click()
    2. Dim strWhere As String
    3. If Not IsNull(Me.cboRoomName) Then
    4. strWhere = strWhere & "([RoomName] Like ""*" & Me.cboRoomName & "*"") "
    5. End If
    6. Me.Filter = strWhere
    7. Me.FilterOn = True
    8. End Sub
    This works great for room name, however I cannot figure out how to search for everything else I need. The problem I have is only building name and room name is in the recordsource for my main form. Everything else (last name, first name, cabinet, switch, etc) is located in my subforms. I've tried adding AND and then
    Code:
    If Not IsNull(Me.txtSearchLastName) Then
            strWhere = strWhere & "([Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName] Like ""*" & Me.txtSearchLastName & "*"")"
        End If
    However, that returns a blank form. I should add, I shouldn't even be doing it like that because if I specify the full path for my control name, it will not work when I open the form under my navigation form. I have a navigation form and I need this to work both inside navigation form as well as from frmMainRooms.

    What am I doing wrong?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I know we have discussed this in other thread. Cannot reference full path to control and still be able to use the form as standalone and within navigation form - which you aware of.

    What you can do is declare some global variables in a general module or use TempVars. These variables can be referenced by any procedure anywhere in the db. So the variables can be set in one procedure and then another procedure can reference them to construct filter criteria.

    So how to set the global variables?

    What do you mean by 'search through a subform', exactly how do you do that - click on record in subform?

    The syntax for the posted criteria is wrong. Don't put variables within quotes, reference to control is a variable.

    strWhere = strWhere & [Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName] & " Like '*" & Me.txtSearchLastName & "*'"

    What is the rest of the code that builds the criteria? Need AND and/or OR operators as part of the concatenation of multiple parameters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I need to be able to search for records in my subforms and go to those records in my main form.

    For instance, if Joe smith is the facility manager for building 1, 2, and 3 I should be able to search "Joe smith" and see building 1, 2, and 3. I need to see all of the subforms within my main form for those records. For instance, Bob smith might also be the facility manager for building 1. If so, when I search the subform for Joe smith and it pulls up that record, I should also see bob smith.

    As for my other criteria (building, room, switches), these can be searched separately or along with lastname. So, I think that's AND (?).

    I don't have any other code, yet.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Makes no sense to me. How can one building have multiple facility managers?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I have a many to many relationship between my customers table and my buildings table. There is a junction table between the two. One building can have multiple facility managers because there are several facility managers per building. So if one isn't available, we can call the other person.

    I'll try to explain it again.

    I have a main form which contains building name, room name, and security method (key, pin, etc). These are all text boxes.

    I have a rooms POC (point of contact) subform. This displays customer information (last name, first name, organization, phone, email, etc) for every customer who is a POC for the room which is displayed in my main form. So, in my main form, if I'm viewing building 1, room 100 and Bob Smith is the POC for that room, I should see all of Bob Smith's information. If Jane Doe is also the POC for that room, I should see her information as well.

    I have a facilitymgr subform. This displays customer information (same as rooms subform) for every customer who is a facility manager for that building which is displayed in my main form. If bob smith is the facility manager for building 1, then if I am viewing building 1 in my mainform, I should see bob smith and whoever is also a facility manager. The way I have it setup now, is it pulls this from my facilitymgr junction table. So, if I see building 1 in my main form, it will also show the rooms within building 1 in my roomname text box. Regardless of what room name I'm viewing, it will show all the building managers for that room. This is because we need to be able to search for building 1, room 100 and see both the POC for that room as well as the facility managers for the entire building.

    I have a cabinets subform. This form is tabular and not datasheet view. This form displays the cabinets which are located inside of the room which is selected. If the room does not have any cabinets, it will show no records. There is a subform inside this subform which displays switch information (switch name, ip address, serial number, etc.). This sub subform is in datasheet view because one cabinet can have several switches.



    Now, for my search bar in the header of my mainform.

    I need to be able to filter by building name and room name. This needs to be cascading combo boxes. If I select building 1 from my combo box and hit the search button, I should see all the records for building 1. Each record that is displayed would have a different room number, likely different room POCs (though it could be the same as one customer could be the POC for multiple rooms), and definitely different cabinets/switches.

    AND/OR

    I need to be able to filter by customer last name as well as customer first name. These can be text boxes. So, if in the last name text box I type in Smith and in the first name text box I type in Bob, I should see all of the records in my main form which bob smith a POC for. I need this to search through both my rooms subform as well as my facilitymgr subform. That is, if bob smith is the POC for building 1 room 100 but he's also the facility manager for building 2, if I do a search for bob smith, I should see both records.

    AND/OR

    I need to be able to filter by cabinet name. This will have to be a combo box because multiple rooms have the same cabinet names just like how multiple buildings have the same room name although they're completely different rooms. "Black Wallmount cabinet" for an example, is in 100s of rooms, has the same name, but they're all very different cabinets. There is no point to search for a cabinet name by itself so this should only work if someone first selects a building, then selects a room, then selects a cabinet.

    AND/OR

    I need to be able to filter by switch name. This can be combo box or text. I need it setup so that if I search for Switch123 it will show 1 and only one record in my mainform. That mainform will still display all the data inside of the subforms. Room POC, facility manager, cabinets, switches, etc. So if there are several switches in that building, I should see several switches in the subform.

    AND/OR

    I need to be able to filter by switch IP address. Nearly identical to my switch name search. If I search for IP address 192.167.0.1, it should show one record in my main form and display all related data in my subform.

    AND/OR

    I need to be able to filter by switch serial number. Same as above two searches.


    Now, all these search text boxes and combo boxes, I believe we would be using a AND operator. But I could be wrong. There shuld be a blank form and someone could enter one search criteria, or several.


    Thank you for your help.

  6. #6
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I'm starting to doubt if what I want to do is even possible. If not, how else would you do this?

    It is complicated by the fact that I have so many subforms. I cannot put all of this information into one form.

    In any case,
    Code:
     If Not IsNull(Me.txtSearchLastName) Then
            strWhere = strWhere & [Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName] & " Like '*" & Me.txtSearchLastName & "*'"
        End If
    Does not work. I get a syntax error.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If I understand correctly, the main form has only building info so to return all records associated with Bob Smith selected in a combobox, code would have to build criteria like:

    BuildingID IN (1, 2, 4, 7)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Hmm. What are the numbers in parentheses for?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    They would be a set of the building ID numbers. It's only an example. I don't know your db structure well enough to give details specific to your db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Hmm. It would not be practical to include the building ID and the room ID in my vba code. There is just way too many.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about posting a copy of your latest database and some instructions for navigating to the form/control in question(s)?
    Also, it seems to me (IIRC) that the rules are changing--- 1 Facility Mgr for whole Bldg -- but I haven't looked for a while.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Unfortunately, I am not seeing any method that would be practical for your requirements.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Please see attachment. If you open up Navigation Form, click on the Rooms tab.

    If the main form/ subform setup I have is too complicated, and you know of another hopefully easier way I would be more than willing to hear it. I understand my requirements are complicated.

    I need a way to display all of this information though, and search for all of it as well.

    BuildingsSwitches3 (1).zip

  14. #14
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Also rules haven't changed much. One facility manager for entire building. Also a customer could be the facility manager for multiple buildings, and one building can have multiple facility managers.

    If I setup queries, everything works the way I need it to. But I cannot get the syntax right on my search form if my life depended on it.

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If I setup queries, everything works the way I need it to. But I cannot get the syntax right on my search form if my life depended on it.
    Well,since you can get queries to work, you could have buttons or text/labels with the various query descriptions on a form, then let the user click a button or label to invoke the query. Maybe you don't need a complex search form?? Or some combination is needed??

    You could have a form (menu for searches) with buttons or labels (some controls) and when you click you either go to a more detailed form for more info/details, or (at some point) execute the query and display necessary info.

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-07-2012, 05:31 PM
  2. Replies: 4
    Last Post: 03-30-2012, 01:47 AM
  3. Replies: 1
    Last Post: 11-29-2011, 01:37 AM
  4. Link between subform and mainform
    By crowegreg in forum Forms
    Replies: 2
    Last Post: 08-24-2011, 07:05 AM
  5. Replies: 0
    Last Post: 03-14-2009, 12:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums