Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2021
    Posts
    23

    Filter Subform that contains Multiple Subforms with a Combobox Selection in Navigation Form Header

    Hello guys,



    I'd be really grateful for help. Here is my problem:

    I have a Navigation Form with a Navigation Control and multiple Navigation Buttons and a Subform Control for all the Tabs of the Navigation form.
    The Filtering with the Tabs with only one Form works fine so far.

    Now I have a problem: The combobox called cboUser contains a UserID which I want to use to filter a Form which I want to embed into the Tab called "Information". Now the Form I want to embed is a Form that has 2 Subform Controls in it called subUser and subLanguage.

    How do I solve this?

    For the other Tabs, I have a procedure written that has Cases based on the selected Tab which sets the Recordsource of the central Navigation Sub Form Control for all the Tabs and requeries accordingly.

    But now I have this probem that I do not know how to reference the subform controls within the Subform I want to embed in the Navigation Form.

    Would be really grateful for help! Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    filter the master form on the ID in the afterupdate event.

    Code:
    sub cboBox_afterupdate()
    if isNull(cboBox) then
       me.filterOn = false
    else
       me.filter = "[clientID]=" & cboBox 
       me.filterOn = true
    endif
    end sub

    I would suggest to only have 1 subform. Then when user clicks the tab ,swap out the source of the subform.
    This saves LOTS of memory. I've had memory errors with > 1 subform.

    Private Sub TabCtl_Change()

    Select Case TabCtl.Value
    Case 0
    subFrm.RowSource = "fPaymentsSub"
    Case 1
    subFrm.RowSource = "fPurchasesSub"
    Case 2
    subFrm.RowSource = "fInventorySub"
    End Select
    End Sub

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not really understanding what you actually require - would help if you provide a screenshot of the form. But best guess - you can set the linkchild/master properties to more than one control e.g.

    linkMaster - cboUser; cboLanguage
    linkChild - userID; languageID

  4. #4
    Join Date
    Jan 2021
    Posts
    23
    Quote Originally Posted by ranman256 View Post
    filter the master form on the ID in the afterupdate event.

    Code:
    sub cboBox_afterupdate()
    if isNull(cboBox) then
       me.filterOn = false
    else
       me.filter = "[clientID]=" & cboBox 
       me.filterOn = true
    endif
    end sub

    I would suggest to only have 1 subform. Then when user clicks the tab ,swap out the source of the subform.
    This saves LOTS of memory. I've had memory errors with > 1 subform.

    Private Sub TabCtl_Change()

    Select Case TabCtl.Value
    Case 0
    subFrm.RowSource = "fPaymentsSub"
    Case 1
    subFrm.RowSource = "fPurchasesSub"
    Case 2
    subFrm.RowSource = "fInventorySub"
    End Select
    End Sub

    Hey, thank you for your reply. I use Navigation Controls though so it's a little different

  5. #5
    Join Date
    Jan 2021
    Posts
    23
    1.
    Click image for larger version. 

Name:	NavApp.jpg 
Views:	11 
Size:	75.2 KB 
ID:	44969
    2.
    Click image for larger version. 

Name:	NavSubForm1.jpg 
Views:	11 
Size:	60.5 KB 
ID:	44970
    3.
    Click image for larger version. 

Name:	NavSubForm2.png 
Views:	11 
Size:	28.3 KB 
ID:	44971


    Okay. I want to explain it a little more.
    I am using a Navigation Form called NavApp. In this Navigation Form Header, I have a combobox with Users.
    In the Tab Information, I embedded the Form called "TabInformation". It's an unbound Form which contains two Subform Controls called 1) subUser AND 2) subLanguage. I tied the Form called "NavSubform_tbl_User" to 1) subUser and I tied the Form called "NavigationForm_tbl_UserLanguage" to 2) subLanguage.

    So there are several users and each users I assigned some languages.
    The filtering as proposed in the first answer works well when I do it separately but my central problem and my goal is, that I want to use the Combobox in the Navigation Form Header only and don't want to embed comboboxes within the SubForms.

    I wrote an On_Click() Procedure similar as proposed already which invoked a LoadNav() function depending on the selected tab. I removed all other tabs. Currently, I have no procedure written for the Tab "Information". I already tried many things and also Link Master/Child seems to be not working because there is something different in here.

    So I also better submit the Database which I already prepared and now gonna upload.

    So I hope my problem is kind of clear now and I would be really grateful if anyone could help

    Thank you and have a nice day everyone!

  6. #6
    Join Date
    Jan 2021
    Posts
    23
    Here is my very easy held DB

    The other forms are for demonstration purposes that I managed to filter them separately already with

    Me.Form.Filter.

    Form.Form.FilterOn = True..

    but my problem is how do I filter from Navigation Form Header to SubForm which contains 2 subform controls which contain the sub forms...
    Attached Files Attached Files

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    try this for cbo AfterUpdate
    Code:
    Private Sub cboUser_AfterUpdate()
    With Me.subNavigationForm.Form.subuser.Form
      .Filter = "UserID = " & Me.cboUser
      .FilterOn = True
    End With
    End Sub
    If that's working as it should, you just need to add the second subform to that code. However, I don't agree with your design. One subform based on a query that returns all languages spoken by a user is what you should be doing. The combo could still provide for filtering that recordset if desired.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-05-2013, 10:09 PM
  2. Replies: 1
    Last Post: 09-06-2011, 01:47 PM
  3. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

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