Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 71

Search subform from mainform

  1. #16
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I suppose I could use queries and buttons. I really want a search form.



    I've done a ton of research and also some troubleshooting. I changed my code to this:
    Code:
    Private Sub cmdSearch_Click()
         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
    I discovered, I was not applying the filter to my subform. I was applying it to my main form and expecting it to filter my subform.

    So now, it works. Sort of. It effectively filters my frmsubfacilitymgr subform based on what I put in txtSearchLastName. However, it doesn't go to that record on my main form. Instead, if I have two facility managers showing for a particular building (Mr. Smith and Mr. Jones), if I search for "Jones," it will filter the subform and only display Jones.

    Going back to what June7 said,
    Quote Originally Posted by June7 View Post
    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)
    I do not really understand how to implement that. If you open my sample database, you'll see its not only building info in my main form. My main form has building name, room name, and security method (pin, key, etc).

    The frmSubFacilityMgr subform only cares about the building name (or building ID?). However my rooms subform only needs the room ID (or building name/ room name combo).

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    The info from June
    Code:
    BuildingID IN (1, 2, 4, 7)
    represents an SQL operator IN() which in English says
    ...Where... BuildingID is one of these.

    An analogy:
    Code:
    Select CityName from tblStateCities where CityName Like "Rock*" and 
    StateAbbrev IN ("MI","KY","NY")
    Select city names from the StateCities table where the City name begins with "Rock" and the State is
    MIchigan or KentuckY or New York. You need quotes " around alpha/string; no quotes around numbers.

    In the attached jpg, I have the frmMainCustomer in design view, I looked at the form properties (Data) and see the filter Forms!frmRooms!BuildingName --- but I don't see a frmRooms in the database??? Perhaps I must be missing something --and it doesn't error??
    Attached Thumbnails Attached Thumbnails FormMainCustomers.jpg  

  3. #18
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Really appreciate your help here Orange.

    I'm not sure why that filter is there. frmRooms doesn't even exist anymore. frmMainCustomers is a lost cause. It was a great learning experience, I don't regret it. But its getting removed from the database. I'm focusing solely on frmMainRooms. The reason for this is because with customers and my main table, I cannot easily relate to all the other data in my database. It was far easier to relate customer to rooms, rooms to buildings, rooms to cabinets, cabinets to switches, etc. So, anything that was in that form, doesn't really matter. Sorry for the confusion.

  4. #19
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    Ok. What is the database I should be looking at; and the exact form?

  5. #20
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I've attached the most recent database. Open up navigation form. I've deleted customers tab, so the rooms tab automatically opens. If you type in "snuffy" in my last name search box and hit my search button, you can see my facilitymgr subform is filtered. This is not what I need to happen.

    A good example of what I want done here, is if you go on record 5 of my main form, you'll see Jones is the facility manager for building 1. I need to be able to search for last name "Jones" from any other record on my main form and see record 5 of my main form. Well actually, it will renumber the records and show the number of records from facility managers who have a last name "jones" But you get the drift.DatabaseforForum.zip

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    When I opened your database the mainCustomers form opens.
    What exactly is
    my main form
    Ok I closed frmMainRooms and opened it and moved to record 5 of 11--I see Jones in sub FacilityMgr and Bldg 1 in header of form.
    I see 1 Alex Jones -no other Jones???

    So I closed the form frmMainRooms, I went to record 1, put in Jones for the search lastname.
    The form stays on record 1 of 11 - no change in Bldg etc, but the sub form for Facility Mgr is now blank???

    What exactly do you want to happen when you search for Jones and you are located on record 1 of 11?
    Plain English

    Advice-- you have to be clear in your communications -- readers do not know that Main Form means frmMainRooms. You would do yourself (and readers) a big help if your test data was set up with Buildings like Bldg1 or Bldg66A and Customers like Cust_Ron Smith etc. It doesn't help debugging when looking at 1, 1036, 56 ...

    I have to admit I do not understand what your form is all about. You have a Search button, but your form is already opened with record source qryRooms.

    Can you state the purpose of the form in a couple of lines-- what exactly is this form for? (plain English)

  7. #22
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    If you search for Jones from record 1 of frmmainrooms, nothing shows up because Jones is not listed in the subform under record 1 of frmMainRooms.

    I explained all this a few posts back on page one.

    I need to be able to search for a last name and see all of the records from frmMainRooms which have that last name in my frmsubfacilitymgr subform as well as my rooms subform (I'll get to rooms later).

    So, if you were to search for jones, I need to see every record from frmMainRooms where Jones is the facility manager for.

  8. #23
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    If I were to put real data in my tables, there would be over 300 records in frmmainrooms. Currently I have no way to search through them.

    So I need a search form.

    The main form is frmMainRooms. The purpose of this form is to store building name, room name, and security method for the room. In subforms, I need to store room POC, building POC (facility manager), cabinets, and switches.

    I need to be able to search for all this.

  9. #24
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Simplest way I can describe it is if I change the code for cmdSearch just slightly so that it looks for cases where cboRoomNameSearch is in Room name (a text box on frmMainRooms), it works flawlessly. I am able to search for any building or room I want using two combo boxes.

    I need to also search by customer name. But customer name is not in frmmainrooms. It's in subforms.

  10. #25
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    If you search for Jones from record 1 of frmmainrooms, nothing shows up because Jones is not listed in the subform under record 1 of frmMainRooms.
    Well that could be, but I doubt it. Here's my thoughts but you are a lot closer to the details than I am.

    When you want to search for Jones and you are on record 1 of 11,Bldg 1038, I think you have restricted your search. That is you aren't free to find Jones in any Bldg, but that's what you say you want/need.

    So when you put Jones in the LastName and do a search, you have to search FacilityMgrs with Lastname = Jones and get back all Bldgs/Rooms that match. But how do you know Jones is a Facility Mgr and not just a POC for a Room?

    Since your form has qryRooms as its recordsource, that has to be adjusted to just the rooms for which Jones is FacilityMgr, if Jones is a FacilityMgr.

    I think you have design issues, or there is something that is having you use bound form before you should.

    If you're looking for Jones, you don't know which Bldg, nor Rooms. You have to find out if Jones if FacilityMgr, which bldg, which rooms, then adjust the recordsource for your form so only Bldg and Rooms related to Jones are included, then requery the form.
    As I see it you have the form with a recordsource and you have linked master/child fields on each of the subforms --and all that info doesn't necessarily apply to Jones.

    It's late here, so I'm closing for now.

  11. #26
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I'm thinking about setting this up with unbound forms instead of bound forms. Perhaps it would be better if nothing showed in my form until I searched for something, vs everything showing and me searching everywhere for it.

    I don't think I care whether Jones was a facility manager or not. I just need to be able to search for Jones and see building, room, room POCs, facility managers, room security method, cabinets, and switches. So if I search for jones and it shows up in frmSubFacilityMgr, then cool... he's a facility manager. Usually when we'd search for a name, we want to know a contact number for that individual as well as other contact numbers. For instance, suppose Jones is a facility manager for building 1 so we search for that and we see building 1 and we call Jones but he doesn't answer. Then we would see Smith is also a facility manager, so we'd call him.

  12. #27
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    I think the approach would be to do some testing /prototyping to get an understanding and proof of concept. Once you understand what you want, and have some proven method to get it, you can investigate/adapt to the "general case", and do the design and development.

    As I have said previously, I think you are trying to do too much at one time. On reading read your posts I can see you are getting more facts and details of requirements. It seems that your current "frustration/difficulty" would be reduced by taking smaller chunks and getting them to work.

    For someone just beginning Access and database, you are doing quite well (better than most for sure). However, it seems there is a certain amount of guessing of what should happen that is a concern. Make a plan -however small it might be- identify what you want to happen (expectation) do some prototyping/testing and see how well you have done (actual/observation). Are the expectation and observation the same? If not, you have some guideline on what has to be resolved. If they match, record it (that one is resolved), and move on to the next item. Repeat.

    You're on a learning trip to gain experience and familiarity. Don't expect everything to work first time. You'll find you are doing some analysis, some design, but all the time getting more familiar with the issue and Access and vba.

    Also, regarding earlier comments on test data. I was not suggesting you load all of the data. I was suggesting you mock up some realistic data with Bldgnames like Bldg3 or Bldg XYZ. It is difficult to follow your testing because Id can be 1, 2,4 etc; building names are numbers 1038,3.. It may be realistic to you, but not to readers.

    Here's a search by John that is often referenced.
    Good luck.

  13. #28
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Thank you.

    So, how would you recommend I break up this form (so as to not do too much at one time)?

    I'm thinking I might have to have a form for customers, subform for rooms and then a form for rooms, subform for customers. Form for room, subform for cabinets.

  14. #29
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    I just finished some painting and am looking at your database.

    I made a revised Click Event for the Search Button, just to see what I needed to populate the form -this is a test only.
    I entered Jones in Lastname, and clicked Search.

    Code:
    Private Sub cmdSearch_Click()
        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
    
            'Now need to find if that is lastname of a FacilityMgr and if so
            'which building, then adjust the recordsource of the form and requery
            Dim isFacilityMgr As String
            isFacilityMgr = "SELECT Customer.LastName, Customer.CustomerID, FacilityMgr.BuildingID," _
                            & " Building.BuildingName, Rooms.RoomName, Rooms.SecOptionID" _
                            & " FROM (Building INNER JOIN (Customer INNER JOIN FacilityMgr ON " _
                            & " Customer.CustomerID = FacilityMgr.CustomerID) ON Building.BuildingID = FacilityMgr.BuildingID)" _
                            & " INNER JOIN Rooms ON Building.BuildingID = Rooms.BuildingID " _
                            & " WHERE Customer.LastName   Like '" & Me.txtSearchLastName & "*';"
            Debug.Print isFacilityMgr
            Me.RecordSource = isfaciityMgr
            Me.Requery
        End If
    End Sub
    This gives me the output in the jpg

    Note; There are 6 records--Was there something to display for these???

    I think it's time to review POC and Facility Mgr --exactly what each is; how they are related; responsibilities...
    Attached Thumbnails Attached Thumbnails ittechguy_revisedButtonClickcode.jpg  

  15. #30
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I copied your code and tried to replicate what you did with it. When I hit search, it removed qryRooms as the source for frmMainRooms and turned that form into an unbound form and then it didn't show any data in any of my forms.

    I've done more thinking and research about this. I think I've been going about this the entirely wrong way. I have built a form with subforms and have had them both auto populated, then I've been trying to filter them.

    I think it may be better to instead, start with a form / subform which contains no data, and then populate that data based on vba code which contains sql queries. Its clear I need a query search.

    Yet again, sadly, I have no idea how to do that. But your code helps a lot. I'd appreciate any pointers, links, etc.

    If I am to start with a blank form and then require the user to search for something so that it will populate, my first source of confusion is whether any of my forms should be bound or unbound. It seems to me like my main form needs to not be based on a query because if it was, it would auto populate with data. If then, it was not based on a query or table, there would be nothing to link my subform to. (i.e. roomsID from subform matches roomsID from main form).

Page 2 of 5 FirstFirst 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
  •  
Tech Forums: Microsoft Office Forums