Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: Listbox Query Doesn't Match Criteria on Load

    I have subform2 with a listbox that displays records based on a value in subform 1. When the form loads, the listbox displays all the records when it is only suppose to filter by the criteria (Audit_ID)

    If I have a button that tells the listbox to requery, the listbox displays the correct records. It seems that my listbox is running the query before the Audit_ID is loaded into subform1. Anyone know how to fix this?

    Here's the RowSource of my listbox

    Code:
    SELECT *
    FROM dbo_tbl_Attachments
    WHERE (((dbo_tbl_Attachments.Audit_ID)=[Forms]![frmCreateReport]![subfrmPlanAudit].[Form]![Audit_ID]))
    ORDER BY dbo_tbl_Attachments.Attach_ID;

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Access is an events-driven program. When you open a form that contains subforms, nothing can guarantee that all forms (the parent form and its subforms) will be ready (loaded) simultaneously: each "form loading" process is performed asynchroneously.

    If you need to be sure that several objects (here: the form and its subform) are in the same state (here: loaded and ready), you can implement an event-driven mechanism in your application.

    Apart from the event that are defined in Access for the different objects (forms, controls, etc.), you can declare and use your own events and use them in the code of your application. Here's an example of such a mechanism.

    Frm_Parent is a form with 2 subforms: SF_Child_1 and SF_Child_2.

    SF_Child_1 is bound to a table (Tbl_CF_DATA) and has a TextBox: Text_Order_Number bound to the column: Order_Number (ControlSource property).

    SF_Child_2 has a ListBox: List0 with 3 columns. Its RowSource property is:
    Code:
    SELECT Tbl_CF_DATA.Order_Number, Tbl_CF_DATA.Engineer_Fist_Name, Tbl_CF_DATA.Engineer_Last_Name 
    FROM Tbl_CF_DATA 
    ORDER BY Tbl_CF_DATA.Engineer_Fist_Name;
    I want to synchronize the contents of List0 from SF_Child_2 with the contents of Text_Order_Number from SF_Child_1, like this (don't pay attention to the syntax: I know that Text_Order_Number is not properly referenced):
    Code:
    SELECT Tbl_CF_DATA.Order_Number, Tbl_CF_DATA.Engineer_Fist_Name, Tbl_CF_DATA.Engineer_Last_Name 
    FROM Tbl_CF_DATA 
    WHERE Tbl_CF_DATA.Order_Number >= [SF_Child_1].[Text_Order_Number] 
    ORDER BY Tbl_CF_DATA.Engineer_Fist_Name;
    I'll create a class: Cls_Child in which I'll declare a public event: Ready

    Both subforms (SF_Child_1 and SF_Child_2 ) will create an instance of this class and use it to raise the Ready event on their Current event to signal that they are loaded and ready.

    These events (one for each instance of the class Cls_Child) will be received by the parent form Frm_Parent that will process the events by adjusting the contents of List0 according to the contents of Text_Order_Number, only if both subforms have initialized their instance of Cls_Child (i.e. if both subforms are loaded and ready).

    There are several advantages to using an independent class instead of declaring the necessary events in the class module of each form. One of them is that the code is centralized in one place (the independent class module) and can be re-used by several subforms, as needed.

    Here is the code of the different modules, everything is explained in the comments. In the attached sample database I also use this mechanism in a different way for the command buttons of the form.

    Code:
    '
    ' This is the code of the class Cls_Child
    '
    Option Compare Database
    Option Explicit
    
    ' Declare the event that will be raised by this class.
    '
    Public Event Ready(ByVal ObjectName As String)
    
    ' Declare the member variable that will contain the instance of the associated form.
    '
    Private WithEvents Form As Form
    
    Public Function DEEP_Attach(ByRef frm As Form)
    
    ' This function is called on the Open event of the corresponding subform.
    ' It "attaches" the calling subform to this class and calls the Hook function
    ' of the subform's parent to link this class to it. This is only possible
    ' if the subform has a parent (i.e. if it's not open independently), hence the test.
    '
        Set Form = frm
        Form.OnCurrent = "[Event Procedure]"
        If HaveParent = True Then Form.Parent.Hook Me
    
    End Function
    
    Private Function HaveParent() As Boolean
    
    ' A subform (i.e. a form open as a child in the subform control of another form)
    ' is not present in the Forms collection (the collection of all open forms).
    ' We use this particularity to determine whether the form associated to this class
    ' is open as a subform or not. If it's not member of the Forms collection, then it's
    ' open as a subform and we know that it has a parent form.
    '
        Dim frm As Form
        
        HaveParent = True
        For Each frm In Application.Forms
            If frm.Name = Form.Name Then
                HaveParent = False
                Exit For
            End If
        Next
    
    End Function
    
    Public Property Get FormName() As String
    
    ' This will allow to identify the associated form by returning its name.
    '
        FormName = Form.Name
        
    End Property
    
    Private Sub Form_Current()
    
    ' Raise the Ready event on the Current event of the associated form.
    '
    ' Note:  Events of the associated form are now processed here because the member variable Form is
    ' ----   declared With Events (Private WithEvents Form As Form) in the Declaration section of this module.
    '
        RaiseEvent Ready(Form.Name)
        
    End Sub
    Code:
    ' This is the code of the class module for SF_Child_1 and SF_Child_2
    ' (both forms receive a copy of the same code).
    '
    Option Compare Database
    Option Explicit
    
    Private m_clsChild As Cls_Child
    
    Private Sub Form_Open(Cancel As Integer)
      
    ' Create an instance of the class Cls_Child
    ' and "attach" this subform to it.
    '
        Set m_clsChild = New Cls_Child
        m_clsChild.DEEP_Attach Form
        
    End Sub
    Code:
    ' This is the code for the class module of the form Frm_Parent.
    '
    Option Compare Database
    Option Explicit
    
    ' Declare the member variables for the instances of the classes
    ' associated with the child objects (subforms).
    '
    Private WithEvents m_clsChild1 As Cls_Child
    Private WithEvents m_clsChild2 As Cls_Child
    Private WithEvents m_ClsStd_Buttons As Cls_Std_ChildButtons
    
    
    Public Function Hook(Child As Object)
    
    ' This function is called by both instances of the class Cls_Child
    ' and also by the instance of the class Cls_Std_ChildButtons
    ' when they are initialized, i.e. when their DEEP_Attach function is
    ' called by their corresponding form on the Form_Open event.
    '
        Select Case Child.FormName
            Case "SF_Child_1":          Set m_clsChild1 = Child
            Case "SF_Child_2":          Set m_clsChild2 = Child
            Case "SF_Child_Buttons":    Set m_ClsStd_Buttons = Child
        End Select
        
    End Function
    
    Public Sub m_clsChild1_Ready(ByVal Sender As String)
    
    ' This is the event handler for the Ready event raised by the class instance m_clsChild1
    ' The Ready event is raised by the subform SF_Child_1 on its Current event.
    '
        AdjustList0
    
    End Sub
    
    Public Sub m_clsChild2_Ready(ByVal Sender As String)
    
    ' This is the event handler for the Ready event raised by the class instance m_clsChild2
    ' The Ready event is raised by the subform SF_Child_2 on its Current event.
    '
        AdjustList0
    
    End Sub
    
    Public Sub m_ClsStd_Buttons_CallBack(ByVal Sender As String, ByVal Message As String, ByVal Extra As Variant)
    
    ' This is the event handler for the CallBack event raised by the class instance m_ClsStd_Buttons
    ' The CallBack event is raised by the subform SF_Child_Buttons when a command button is clicked.
    '
        Select Case Message
            Case "Close":   DoCmd.Close acForm, Me.Name
            Case "Quit":    Application.Quit
            Case Else:      MsgBox "The command: " & Message & " is unknown and cannot be executed.", vbExclamation, "Unknown command"
        End Select
        
    End Sub
    
    Private Function AdjustList0()
    
        Dim strOrderNumber As String
        
        ' For being able to adjust the contents of the ListBox List0 in the subform SF_Child_2
        ' Both subforms SF_Child_1 and SF_Child_2 must be loaded and ready. When they are ready
        ' their corresponding class variables m_clsChild1 and m_clsChild2 are initialized.
        '
        If Not m_clsChild1 Is Nothing And Not m_clsChild2 Is Nothing Then
            strOrderNumber = Nz(Me.Child1.Form.Text_Order_Number.Value, "")
            Me.Child2.Form.List0.RowSource = "SELECT Tbl_CF_DATA.Order_Number, " & _
                                                    "Tbl_CF_DATA.Engineer_Fist_Name, " & _
                                                    "Tbl_CF_DATA.Engineer_Last_Name " & _
                                             "FROM Tbl_CF_DATA " & _
                                             "WHERE Tbl_CF_DATA.Order_Number >= '" & strOrderNumber & "' " & _
                                             "ORDER BY Tbl_CF_DATA.Engineer_Fist_Name;"
            Me.Child2.Form.List0.Requery
        End If
    
    End Function
    Attached Files Attached Files
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    How does:

    Code:
    Public Event Ready(ByVal ObjectName As String)
    Know when its completed?

    I can't see any
    Code:
    End Event
    anywhere... Does it just end itself when it reaches the end of the script?


    Nice code by the way. Did you write that all now? Or have it laying around somewhere?
    Looking for the perfect beer...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Thanks for your appreciation!

    As stated in the comment just above it, this line:
    Code:
    ' Declare the event that will be raised by this class.
    '
    Public Event Ready(ByVal ObjectName As String)
    is just the declaration of the event. You declare an event as you would declare a member variable, in the Declarations section of the class. It is not a procedure that would necessitate a End Event line. see: Events in Visual Basic

    As for the code, yes I wrote it in answer to the question about the lack of synchronisation between a form and its subform(s) when the form is open.

    It's a problem I had to solve several times in the past and programming with events was the best solution I could find. I now use this technique very often in the applications I write. I have developed several "standard" classes that can be linked to forms and subforms to solve various problems (synchronize the current record of two unrelated forms, translate the captions of the forms and their controls to different languages, use a standard interface for all command buttons in all forms, create a standard filtering method for almost every bound form, etc.). You can see an example of this in the attached database if you examine the way the subform SF_Child_Buttons communicates with its parent form.
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    63
    Wow, thanks Sinndho! It will take me a while to work that code into my project but I will let you know if I run into problems. Thanks!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    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
  •