Results 1 to 4 of 4
  1. #1
    whamilton is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6

    Filter Subform by Combo Box

    I've built a form with an embedded Subform. There are three combo boxes allowing the user to filter the results of the subform. Everything works until I embed the form onto the Navigation Form.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Combo9_Change()
      Combo11.Value = Null
      Combo11.Requery
      Combo13.Value = Null
      Dim SQL As String
      
      SQL = "SELECT qry_Customers.[Major Command_ID], qry_Customers.[Major Command], qry_Customers.ComponentCommand_ID, qry_Customers.[Component Command], " _
      & "qry_Customers.Commands_ID, qry_Customers.Command, tbl_TaskNames.[Task Name], tbl_TaskNames.[Task_ID], tbl_ChargeCodes.[Charge Code],tbl_ChargeCodes.[Contract Type], " _
      & "tbl_ChargeCodes.[PoP Start], tbl_ChargeCodes.[PoP End], tbl_ChargeCodes.[Term Length], tbl_ChargeCodes.[Total Value], tbl_ChargeCodes.[Funded Amount], " _
      & "tbl_ChargeCodes.[Cumulative Invoiced], tbl_ChargeCodes.[Funded Remaining], tbl_ChargeCodes.[Total Remaining], tbl_ChargeCodes.[Burn Rate] " _
      & "FROM (tbl_ChargeCodes RIGHT JOIN tbl_TaskNames ON tbl_ChargeCodes.ChargeCode_ID = tbl_TaskNames.[Charge Code]) LEFT JOIN qry_Customers ON tbl_TaskNames.Customer = qry_Customers.Customer " _
      & " WHERE ((tbl_ChargeCodes.[PoP End]) > Date()) AND qry_Customers.[Major Command_ID]=" & Me.Combo9 & " " _
      & "ORDER BY tbl_ChargeCodes.[Charge Code];"
    
    
    Me.frm_TaskNames_and_Customer1.Form.RecordSource = SQL
    Me.frm_TaskNames_and_Customer1.Form.Requery
            
    End Sub
    As I stated this works when I open the form by itself but it doesn't work when the form is embedded in [frm_Navigation]

    The location of the form in question on [frm_Navigation] is:

    Tab: Search
    Tab: Customer

    The Customer tab contains [frm_ Sort Tasks by Customer]

    [frm_Sort Tasks by Customer] contains the embedded Form [frm_TaskNames and Customer1]

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Instead of assigning the sql string as the recordsource at runtime, just make the form's recordsource that Select statement in design mode.

  3. #3
    whamilton is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6
    Quote Originally Posted by davegri View Post
    Instead of assigning the sql string as the recordsource at runtime, just make the form's recordsource that Select statement in design mode.
    I need to be able to refine the filter by the three comboboxes:

    1) [Major Command]
    2) [Component Command]
    3) [Command]

    I have the three Combo boxes filtering down as they go and the form works perfectly when it's not embedded in the Navigation form. I can't seem to get it to work any other way.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Likely your reference syntax is wrong OR you are not aware that only one subform loads into a navigation form at one time. If you choose another tab, the current form closes and the next chosen one is loaded. A navigation control can only hold one form at a time - same as any subform control. Thus if you're attempting to reference one form from another and both are on a navigation form, you cannot but I can't tell from your post if that's what's going on.

    If it is a syntax issue you might need something like
    Code:
    Forms!frmNavigation!frmMain.Form!frmOvertime.Form.txtControlNameHere
    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. Filter subform with two combo boxes
    By dotcanada in forum Forms
    Replies: 4
    Last Post: 07-25-2015, 05:20 PM
  2. Combo box to filter records in a subform
    By dannybeaver in forum Access
    Replies: 9
    Last Post: 08-27-2014, 02:14 AM
  3. Filter a Subform with 4 combo box
    By Tlegendz8 in forum Forms
    Replies: 1
    Last Post: 05-20-2014, 03:06 PM
  4. Filter Subform by Combo Box Dates
    By steve042 in forum Forms
    Replies: 14
    Last Post: 06-20-2013, 07:36 AM
  5. Replies: 19
    Last Post: 07-23-2012, 10:34 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