Page 5 of 5 FirstFirst 12345
Results 61 to 71 of 71

Search subform from mainform

  1. #61
    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,287
    I guess it's back to the issue of what exactly do you want.


    You were quite clear that a customer could be both a FMgr and a POC. I questioned this because early on you said an FMgr was for an entire Bldg; and a POC was for a specific Room (within a Bldg). -- and then that a Customer could be both a FMgr and a POC. And there could be multiple FMgrs and POCs for Bldgs and Rooms.

    So I don't understand this which is a major change in business rules
    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.
    You can not determine FMgr without knowing Bldg. So once a Bldg is chosen, in the after update, I get all info to fill the list of FMgrs for that Bldg.
    Similarly you can not identify POCs until you identify Room, and as soon as you select a Room, the POC listbox is filled with all POCs for that Bldg/Room combination.
    As far as LastName box is concerned, if only the LastName is selected, the set up finds all info for that Customer and identifies where he is FMgr and POC.

    That is how the listboxes on the Pages on the tabs are populated.

    You can change how things are populated. But single textboxes for Bldg and Room will not handle situations where Customer has >1 assignment. DerpyDerp in your example is FMGR in Bldg B (Rooms 2100 and 2200), and is POC for
    Bldg A Room 1100, Bldg C (Rooms 3100 and 3200)--how would that fit into 1 text box Bldg and 1 text box Room?

    I don't think you populate BldgID and RoomId by CustomerID, that's the whole point of relationships. You gather the required info from a query against the related tables.

    I don't understand how different forms or multiple pages would make finding/dissecting the information more efficient. Your initial objective was a form and multiple subforms.

    You can have many pages on tabs in addition to what is currently there, and you can populate most in the after update of the combos and/or the textbox. This gives you rapid access to info by just moving tab to tab.

    Anyway, you can change the way things are calculated and displayed. There is nothing sacred about what has been done so far except to show some proof of concept.

    I still think there is an issue with the way you have modeled Customers and Rooms. To me, a Room only exists in the context of a Bldg, and shows up in tblRoomsPOC. It is resolved in tblRooms. It may be exactly how things work, but just seems a little strange to me.

    You seem to be much more confident in what you are trying to do. It also seems have some ideas for options for identifying and displaying information which you understand better than others. You have definitely become more familiar with Access over the last while. I think you're in good shape to test and prototype some ideas. It will be helpful if you bounce some options off the people who will be using the database. It will help you with design, and it will get others involved.

    Good luck with the project.

  2. #62
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Quote Originally Posted by orange View Post
    But single textboxes for Bldg and Room will not handle situations where Customer has >1 assignment. DerpyDerp in your example is FMGR in Bldg B (Rooms 2100 and 2200), and is POC for
    Bldg A Room 1100, Bldg C (Rooms 3100 and 3200)--how would that fit into 1 text box Bldg and 1 text box Room?
    The only way it would work is if it would display multiple records for txtBuildingID and txtRoomID. That is, if searching for "DerpyDerp" brought back 2 building IDs (Building ID 1 and Building ID 2 aka building "A" and Building "B"), it would display "1" in the txtBuildingID text box and down at the bottom of the form it will show multiple records. I.E. "Record 1 of 2."

    I don't think this is possible on an unbound form. I'm experimenting with a bound form and filtering the bound form right now. I have txtBuildingFK bound to tblBuilding.BuildingPK and Form 1 is created based on a query. Then I'm trying to use the code below to filter the form. It doesn't like my SELECT syntax. Ugg.....

    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
    Me.Filter = sqlSearch
    Me.FilterOn = True
    
    
                
    End Sub

  3. #63
    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,287
    Sometimes it is easier to select only the record(s) you want, rather than selecting all records and filtering. It won't be an issue with only a few records.

    I think the only way to know for sure if something is practical or even feasible is to try it. You can do a lot of learning by trying some options.

    Tabs and pages are a convenient way of displaying a lot of info while reusing the screen real estate.

  4. #64
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I agree that the only way to know for sure is to try it. Thats why I'm trying to do as much research as I can, and fiddle with things. I could find someone to do all this for me, but then I wouldn't learn anything.

    I definitely still consider myself to be in the learning phase of this project.

    I agree tabs and pages are a convenient way of displaying this info.

    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As StringsqlSearch = "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.Filter = sqlSearch
    Me.FilterOn = True
    
    
                 End Sub
    Do you know why I'm getting run time error 3075? It says invalid syntax. I've tried removing tblCustomer.LastName and just using LastName =. Same result. I've also tried putting everything on one line, still doesn't work. I also removed tblCustomer.FirstName (as I'm not working with that, yet). I just noticed I have an extra space after "FROM". I removed that, Still get same error.

    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As StringsqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName FROM " _
               & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
               & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
               & " WHERE LastName ='" & Me.cboSearchLastName & "'"
    
    
    Me.Filter = sqlSearch
    Me.FilterOn = True
    
    
                 End Sub
    Last edited by ittechguy; 10-03-2015 at 03:12 PM. Reason: Fix code

  5. #65
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I've done more troubleshooting. I still get runtime error 3075, invalid syntax. So likely I still have a problem with my sql syntax.

    But also, I think I might have a problem with how I'm applying the filter.

    I need txtBuildingID to be filtered based on the sql string. I haven't applied the filter to txtbuildingID, only to the form itself. I think sqlSearch needs to be something like

    Code:
    Me.txtBuildingID = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName FROM" _
               & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
               & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
               & " WHERE LastName ='" & Me.cboSearchLastName & "'"

  6. #66
    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,287
    I used your previous database and
    -added a button (Command22)
    -added a textbox called text20

    -added code for button click see attached jpg


    Intent is to put lastname into text20

    Have attached a jpg of the result
    Attached Thumbnails Attached Thumbnails ButtonOnForm1AndTextBox_Text20.jpg   CodeBehindButtonClickToFillText20.jpg  

  7. #67
    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,287
    Just saw that you had posted again before I posted.

    I have reopened the previous database (I didn't save the changes)

    Anyway I added a textbox text20 (again)

    The intent is to put the building ID of the Bldg name selected in cboSearchBuildingName into text20.

    I didn't use a button, but added some code to the after update event of cboSearchBuildingName. You could put this in the click of a button, same code should work.

    Here is the revised after update code.

    Code:
    Private Sub cboSearchBuildingName_AfterUpdate()
        Dim sqlRS As String        'rowsource SQL for Rooms in this Bldg that have a POC
        Dim SQLFcyMgr As String    'rowsource SQL for Facility Mgrs for this Building
        Me.cboSearchRoomName = ""
        Me.lstRoomsPOC.RowSource = ""
        Me.lstCustInfo.RowSource = ""
        
       '''new code here '''''''
        Me.Text20 = " Building  " & Me.cboSearchBuildingName & "  Has BldgID " & _
                             DLookup("BuildingPK", "tblBuilding", "BuildingName = '" & Me.cboSearchBuildingName & "'")
        Debug.Print Me.Text20
        ''''' to here'''''''''''''''
    I didn't copy the rest of the code. It's in the database you have.

    I have attached a jpg showing the result in Text20
    Attached Thumbnails Attached Thumbnails ResultShowingBuildingIDInText20.jpg  

  8. #68
    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,287
    Just tried it with a button.

    Button was called btnFind
    here is the code behind the click event {exactly the same code as in the after update event in last post}
    Code:
    Private Sub BtnFind_Click()
    '''new code here '''''''
        Me.Text20 = " Building  " & Me.cboSearchBuildingName & "  Has BldgID " & _
                    DLookup("BuildingPK", "tblBuilding", "BuildingName = '" & Me.cboSearchBuildingName & "'")
        Debug.Print Me.Text20
        ''''' to here'''''''''''''''
    End Sub
    attached are 2 jpgs of the result of button click
    Attached Thumbnails Attached Thumbnails BuildingID intoText20UsingButtonClick.jpg   AnotherExampleDifferentBldg.jpg  

  9. #69
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I'm making some progress. I needed to set the recordsource based on the SQL statement, not apply filter.

    Here is my code:

    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As String
    If Not IsNull(Me.cboSearchLastName) Then
    sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
            & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
            & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
            & " WHERE LastName ='" & Me.cboSearchLastName & "'"
            End If
    Me.RecordSource = sqlSearch
    End Sub
    I have two text boxes, txtBuildingID and txtRoomID. txtBuildingID is bound to BuildingFK. txtRoomID is bound to RoomsFK. If I search for a last name, I see all the records for rooms owned by that last name. At the bottom of the form, I can scroll through the records. Record 1 of 10, as an example. It works great!

    But life is not without problems, it seems. I also need to filter by facility manager. I want to be able to filter both by RoomsPOC and by Facility Manager.

    I've tried using two search criteria using using AND between them. But that requires two separate SQL statements. Which doesn't allow me to bind txtRoomsID to RoomsFK because RoomsFK doesn't exist in the second SQL statement. How can I fix this?

    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As String
    If Not IsNull(Me.cboSearchLastName) Then
    sqlSearch = "SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRoomsPOC.RoomsFK, tblRooms.BuildingFK FROM" _
            & " tblCustomer INNER JOIN (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) ON" _
            & " tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK" _
            & " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
            End If
    If Not IsNull(Me.cboSearchLastName) Then
    sqlSearch = "SELECT tblFacilityMgr.BuildingFK, tblFacilityMgr.CustomerFK, tblCustomer.LastName FROM" _
               & " (tblCustomer INNER JOIN tblFacilityMgr ON" _
               & " tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)" _
               & " WHERE LastName ='" & Me.cboSearchLastName & "'"
               End If
    Me.RecordSource = sqlSearch
    End Sub

  10. #70
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I'm getting closer.

    Seems the reason it wasn't working is because I cannot have two separate SQL statements. If I'm assigning the recordsource to sqlSearch, it will use only one query. So, it seems I need a union query.

    I'm not good with union queries. I wrote this:

    Code:
    Private Sub cmdSearch_Click()    Dim sqlSearch As String
        If Not IsNull(Me.cboSearchLastName) Then
            sqlSearch = "SELECT tblCustomer.LastName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.BuildingFK" _
    & " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
    & " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
    & " WHERE LastName ='" & Me.cboSearchLastName & "'""" _
    & " UNION ALL SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRooms.RoomsPK, tblRooms.BuildingFK" _
    & " FROM (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) INNER JOIN" _
    & " (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK) ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
    & " WHERE LastName ='" & Me.cboSearchLastName & "'"
        End If
        Me.RecordSource = sqlSearch
    End Sub
    It says invalid syntax. I'm not sure what I did wrong. I copied the SQL statement down and created a query for it. If I remove ALL so that its just "UNION SELECT" then the query works without a problem. However if I use "UNION ALL" in vba, it says unsupported join expression. So I'm guessing UNION ALL doesn't work in vba. Regardless, do I have a problem with the " _ & " between each line?

    Edit: I fixed my syntax error, now it says "Join expression not supported." I googled this, and found nothing good. It looks like vba doesn't support "complex queries"??? Is there a work-around?

    Fixed it! I had to build the queries separately to find out what I was doing wrong.

    Code:
    Private Sub cmdSearch_Click()Dim sqlSearch As String
        If Not IsNull(Me.cboSearchLastName) Then
            sqlSearch = "SELECT tblCustomer.LastName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK" _
    & " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
    & " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON" _
    & " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
    & " WHERE LastName ='" & Me.cboSearchLastName & "'" _
    & " UNION SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
    & " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
    & " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
    & " WHERE LastName ='" & Me.cboSearchLastName & "'AND"
       End If
       If Not IsNull(Me.cboSearchLastName) Then
            sqlSearch = "SELECT tblCustomer.FirstName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK" _
    & " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
    & " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON" _
    & " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
    & " WHERE LastName ='" & Me.cboSearchFirstName & "'" _
    & " UNION SELECT tblCustomer.FirstName, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
    & " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
    & " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
    & " WHERE LastName ='" & Me.cboSearchFirstName & "'"
       End If
        Me.RecordSource = sqlSearch
    End Sub

  11. #71
    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,287
    It would help readers if you have a clear statement of what you are trying to accomplish.

    Union ALL will work in vba. But it includes duplicate values from each/any components of the UNION.
    UNION by itself rejects duplicates. So if you are using a union query for anything, it is rare to want duplicates.
    Union queries require that the number of fields in each Select is the same.

    Below is the SQL I used to get the FMgr and POC info given a value in the cboSearchLastName. This is the rowsource of lstCustInfo.

    Code:
    SELECT tblCustomer.OrganizationFK, tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK,
     tblCustomer.RankFK, tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.PhoneNo, tblCustomer.Email,tblBuilding.BuildingName, tblRooms.RoomName, "FMGR" AS Is_A
     FROM (tblBuilding INNER JOIN tblRooms ON 
     tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN
     (tblCustomer INNER JOIN tblFacilityMgr ON 
     tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK
    WHERE (((tblCustomer.LastName)=[Forms]![Form1]![cboSearchLastName]))
    
    UNION 
    SELECT tblCustomer.OrganizationFK, tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK,
     tblCustomer.RankFK, tblCustomer.LastName, tblCustomer.FirstName, PhoneNo, Email,tblBuilding.BuildingName, tblRooms.RoomName, "POC" AS Is_A
    FROM (tblBuilding INNER JOIN tblRooms ON
     tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK) ON
     tblRooms.RoomsPK = tblRoomsPOC.RoomsFK
    WHERE (((tblCustomer.LastName)=[Forms]![Form1]![cboSearchLastName]))
    ORDER BY Is_A;
    You can add or remove tables and/or fields depending on your needs.

    One technique is to create queries to get the info you want --that is make sure the query syntax is good and the result meets the need. Then, to get your WHERE clause to handle a form control, use the build option in the query designer. Identify the form (from loaded forms) and the control from the list of controls. Then apply that to your SQL or Rowsource.

    As you can see from some of the examples, there are different ways to achieve things.

Page 5 of 5 FirstFirst 12345
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