Results 1 to 3 of 3
  1. #1
    StuW is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    2

    Apply Filter to Subform on Main Unbound Form

    I have a Unbound form with a Subform on the Unbound form. (frmProduct with fsubProduct) I display all my records on the subform using qryProduct.
    I have a command button on the form that opens a form with several multi-list boxes. I can select data row(s) from the list boxes and from a command button, run code to retrieve a subset of records. These records are stored in a temp table and the main table tblProduct has a field that is updated with a Yes/No type field. When the form with List boxes closes, I want the subform to get updated with the "Yes" records ONLY displayed on the frmProduct when it gets the focus. How to apply a filter showing the "Yes" records only? I can always select the column and filter after the fact, but I wanted filtered with my subset On Focus. Tried many/many ways with no success. Any ideas appreciated.

    [Code]
    Private Sub OK_Click()
    On Error GoTo OK_Click_Err
    Dim varItem As Variant
    Dim strWhere As String
    Dim strWhere1 As String
    Dim strWhere2 As String
    Dim strWhere3 As String
    Dim strWhere4 As String
    Dim lngLen As Long


    Dim strDelim As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String

    With Me!lstGroup
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    strWhere1 = strWhere1 & "'" & strDelim & .ItemData(varItem) & strDelim & "',"
    End If
    Next varItem
    End With
    lngLen = Len(strWhere1) - 1
    If lngLen > 0 Then
    strWhere1 = "[Description] IN (" & Left$(strWhere1, lngLen) & ") "
    End If

    With Me!lstClass
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) & strDelim & "',"
    End If
    Next varItem
    End With
    lngLen = Len(strWhere2) - 1
    If lngLen > 0 Then
    strWhere2 = "[POP] IN (" & Left$(strWhere2, lngLen) & ") "
    End If

    With Me!lstBrand
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    strWhere3 = strWhere3 & "'" & strDelim & .ItemData(varItem) & strDelim & "' ,"
    End If
    Next varItem
    End With
    lngLen = Len(strWhere3) - 1
    If lngLen > 0 Then
    strWhere3 = "[Mfg Name] IN (" & Left$(strWhere3, lngLen) & ") "
    End If

    With Me!lstCode
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    strWhere4 = strWhere4 & "'" & strDelim & .ItemData(varItem) & strDelim & "' ,"
    End If
    Next varItem
    End With
    lngLen = Len(strWhere4) - 1
    If lngLen > 0 Then
    strWhere4 = "[Make] IN (" & Left$(strWhere4, lngLen) & ") "
    End If

    strWhere = strWhere1

    If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
    strWhere = strWhere & " AND " & strWhere2
    Else
    strWhere = strWhere & strWhere2
    End If
    If Len(strWhere) > 0 And Len(strWhere3) > 0 Then
    strWhere = strWhere & " AND " & strWhere3
    Else
    strWhere = strWhere & strWhere3
    End If
    If Len(strWhere) > 0 And Len(strWhere4) > 0 Then
    strWhere = strWhere & " AND " & strWhere4
    Else
    strWhere = strWhere & strWhere4
    End If
    Set db = CurrentDb
    '*** create the query based on the information on the form
    strSQL = "SELECT qryProduct.* FROM qryProduct "
    strSQL = strSQL & " WHERE " & strWhere
    Set qdf = db.QueryDefs("qryProduct1")
    qdf.SQL = strSQL
    '*** open the query
    strSQL1 = "SELECT qryProduct1.[Part Number], qryProduct1.Make, qryProduct1.[Mfg Name], qryProduct1.Description, qryProduct1.POP INTO tblTempMake " & vbCrLf & _
    "FROM qryProduct1;"
    strSQL2 = "UPDATE tblProduct SET tblProduct.Select = 0;"
    strSQL3 = "UPDATE tblProduct INNER JOIN tblTempMake ON tblProduct.[Part Number] = tblTempMake.[Part Number] SET tblProduct.Select = -1;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmProductCriteria"
    'DoCmd.OpenForm "frmProduct", acNormal, "qryProduct1", , , acWindowNormal
    DoCmd.ApplyFilter "qryProduct1", , "Forms!frmProduct.fsubProduct"
    OK_Click_Exit:
    Exit Sub

    OK_Click_Err:
    MsgBox Err.Description
    Resume OK_Click_Exit

    End Sub
    /[code]

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	11 
Size:	71.5 KB 
ID:	26162

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	11 
Size:	29.6 KB 
ID:	26163

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think I've always use the Form.Filter approach so I'm guessing here.
    First, I think if you're going to specify a query as a filter via DoCmd, I don't recognize the use of your "Forms!frmProduct.fsubProduct" reference, nor could I find an example like it. I'd say you should specify the query as the filter, then set the FilterOn property to True and that's all. If the filter is not a query, the filter is specified as '[SomeField] = SomeValue, then FilterOn = True.

    In addition, if you're going to use the [SomeField] = SomeValue approach, you must have the correct subform reference syntax if the control supplying the filter is on a subform or if you're setting a subform property from a main form. I use:
    referencing CONTROLS on subform:
    Code:
    [Forms]![Main form name]![subform control name].[Form]![control name on subform]
    referencing subform PROPERTY (e.g. recordset property such as Recordcount):
    Code:
    Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount
    Last edited by Micron; 10-19-2016 at 09:22 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    StuW is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    2
    Thank you Micron; I was able to use the Filter On Load set to Yes and it worked just fine.

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

Similar Threads

  1. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  2. Replies: 5
    Last Post: 02-12-2014, 11:52 PM
  3. Replies: 2
    Last Post: 02-25-2013, 10:47 AM
  4. Apply Filter based on unbound date boxes
    By anoob in forum Access
    Replies: 3
    Last Post: 01-21-2011, 05:26 PM
  5. Replies: 5
    Last Post: 10-06-2010, 07:28 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