Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    11

    Unanswered: Filtering a subform within a main form from a dialog box

    The title may sound convoluted but it's true. LOL. I've been struggling with this for days, and feel like I'm so close, but no cigar.

    I have an unbound main form that has command buttons on it as well as an embedded subform in datasheet format with detail shipment information blah blah. One of the commands is a filter button that opens up a dialog box where the user can enter parameters (ie. company, division, facility, shipdate) and the theory is when they click on the "Apply" command button, the subform that is embedded in the main form should show the filtered records. However, I get the error message that "Microsoft Access can't find the form Sfrmmanifestlog in the Macro or visual basic code. If I have the subform open as an independent screen the filter works fine, but not while it's embedded in the main form. I've played with the code but can't seem to make the connection. Any help or suggestions would be greatly appreciated. Here is the code I'm using:

    Private Sub CmdApply_Click()
    Dim StrCompany As String
    Dim StrDivision As String
    Dim StrFacility As String
    Dim StrFilter As String

    If IsNull(Me.CboCompany.Value) Then
    StrCompany = "Like '*'"
    Else
    StrCompany = "='" & Me.CboCompany.Value & "'"
    End If

    If IsNull(Me.CboDivision.Value) Then
    StrDivision = "Like '*'"
    Else
    StrDivision = "='" & Me.CboDivision.Value & "'"
    End If

    If IsNull(Me.CboFacility.Value) Then
    StrFacility = "Like '*'"
    Else
    StrFacility = "='" & Me.CboFacility.Value & "'"
    End If

    StrFilter = "[Company] " & StrCompany & " AND [Division] " & StrDivision & " AND [Facility] " & StrFacility

    With Forms![SfrmManifestLog] <-----I believe this is the source of my issue.
    .Filter = StrFilter
    .FilterOn = True
    End With
    End Sub

    PLEASE HELP!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When a form is open as a subform, it is not present in the Forms collection. Provided that the SubForm/SubReport control that contains the subform is named "SfrmManifestLog" too, try:
    Code:
    With Me.SfrmManifestLog.Form
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    11
    Thx Sindho, unfortunately the subform is named SfrmManifestLog the main form is name FrmManifestLog. I've tried this code already to no avail.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the name of the SubForm/SubReport control containing the subform (it can be different from the name of the subform itself)?
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    11
    Thx, I understand the control name is the same as the actual subform name - SfrmManifestLog.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you create a valid reference to the subform? What happens if you try:
    Code:
    Dim frm as Form
    Set frm = Me.SfrmManifestLog.Form
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Posts
    11
    Interesting, I've tried that too but it still does not recognize the subform because it is not physically open in its own window.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This makes no sense. If there is a control of type subform/subreport in the Controls collection of the main form and if this controls contains the name of another Form in its SourceObject property (which means that this other Form is open as a subform in the main form), then you are able to retrieve a reference to the Form object for this subform:
    Code:
    Private Sub EnumSubForm_Click()
    
        Dim frm As Form
        Dim ctl As Control
        Dim i As Long
        
        For Each ctl In Me.Controls
            If ctl.ControlType = acSubform Then
                Debug.Print ctl.Name
                Debug.Print , ctl.SourceObject
                Set frm = ctl.Form
                For i = 0 To frm.Controls.Count - 1
                    Debug.Print , , i, frm.Controls(i).Name
                Next i
                Debug.Print
            End If
        Next ctl
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Posts
    11
    I don't have the filter controls embedded in the main form. They come up in a dialogue box that pops up when you click the filter command button on the main form. I have the filter options in a dialogue box because I want to be able to add additional filtering options, ie. manifest type, date ranges, invoices, COD, etc. I'm thinking it's because of the dialogue box that the subform is not being called into the available control options. Initially I thought I could get around the problem by what you suggested 2nd as setting a valid form reference, but that didn't bring it into the queue of available forms. I may just have to figure out a way to integrate all the filtering controls into the actual main form...

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dbk412 View Post
    I don't have the filter controls embedded in the main form. ... I may just have to figure out a way to integrate all the filtering controls into the actual main form...
    Not necessarily: You can send the filter string assembled into the filter dialog form back to the main form where it can be processed.

    Main form: Frm_Main
    Filter form: Frm_Filter
    Subform of Frm_Main: SfrmManifestLog
    Command button of Frm_Main to open Frm_Filter: CmdFilter

    In the class module of Frm_Main:
    Code:
    Private Sub CmdFilter_Click()
    
        DoCmd.OpenForm "Frm_Filter", , , , , acDialog, Me.Name
        
    End Sub
    
    Public Sub ApplyFilter(ByVal strFilter As String)
    
        With Me.SfrmManifestLog.Form
            If Len(Filter) > 0 Then
                .Filter = strFilter
                .FilterOn = True
            Else
                .FilterOn = False
            End If
        End With
            
    End Sub
    In the class module of Frm_Filter:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strCaller As String
    
    Private Sub CmdApply_Click()
    
        Dim StrCompany As String
        Dim StrDivision As String
        Dim StrFacility As String
        Dim strFilter As String
        
        If IsNull(Me.CboCompany.Value) Then
            StrCompany = "Like '*'"
        Else
            StrCompany = "='" & Me.CboCompany.Value & "'"
        End If
        If IsNull(Me.CboDivision.Value) Then
            StrDivision = "Like '*'"
        Else
            StrDivision = "='" & Me.CboDivision.Value & "'"
        End If
        If IsNull(Me.CboFacility.Value) Then
            StrFacility = "Like '*'"
        Else
            StrFacility = "='" & Me.CboFacility.Value & "'"
        End If
        strFilter = "[Company] " & StrCompany & " AND [Division] " & StrDivision & " AND [Facility] " & StrFacility
        DoCmd.Close acForm, Me.Name
        If Len(m_strCaller) > 0 Then Application.Forms(m_strCaller).ApplyFilter strFilter
        
    End Sub
    
    Private Function IsOpen(FormName As String) As Boolean
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = FormName Then
                IsOpen = True
                Exit For
            End If
        Next
    
    End Function
    
    Private Sub Form_Open(Cancel As Integer)
    
        If Not IsNull(Me.OpenArgs) Then
            If IsOpen(Me.OpenArgs) Then m_strCaller = Me.OpenArgs
        End If
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •