Error: The object doesn't contain the Automation Object..

[bryan0]

Hello All,


I have an access project with a single main form (HEADER_FORM) with has several subforms, (including CHILD_SUBFORM). I created each form using the form designer. On the first tab of the main form, I'm using a listbox that the user can use to select which record they'd like to open. After clicking on the record, they click my "View/Edit" command button to open the data behind that record.

Unfortunately, the subforms are not being populated. For some reason, the record that is opened in the main form is not sent to my subforms. I also get an error when I try to enter information into the subforms. It says: "The object doesn't contain the Automation object 'CHILD_TABLE.' You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations. Check the component's documentation for information on the properties and methods it makes available for Automation operation."

Here is the VBA I used to open the record (my listbox is called "SearchResults"):

Private Sub cmdViewEdit_Click()    
    Dim rs As DAO.Recordset
    If Not IsNull(Me.SearchResults) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
        Set rs = Me.RecordsetClone
        rs.FindFirst "[HEADER_ID] = '" & Me.SearchResults.Column(0) & "'"
        If rs.NoMatch Then
            MsgBox ("Not found")
        Else
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub

Here's what I've checked:

• In the relationship window, the PK of the main table is Left Joined to the FK of my other tables.
• In the properties window of the subform on the main form, on the data tab, "Link Master Fields" = HEADER_ID and "Link Child Fields" = CHILD_SUBFORM.HEADER_ID_FK
• The "Record Source" on my subform is: SELECT CHILD_TABLE.*, HEADER_TABLE.* FROM HEADER_TABLE LEFT JOIN CHILD_TABLE ON HEADER.HEADER_ID = CHILD_TABLE.HEADER_ID_FK;

I suspect that my problem could be due to the VBA that I'm using to select a record? I've had this working in the past when using the form wizard, but this form is much more complex, and I could not use the wizard to create it all at once. Any help would be greatly appreciated!

[ItsMe]

Maybe something like this will help to isolate where the error is occurring

Private Sub cmdViewEdit_Click()    
    Dim rs As DAO.Recordset
    If Not IsNull(Me.SearchResults) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
        Set rs = Me.RecordsetClone
        rs.FindFirst "[HEADER_ID] = '" & Me.SearchResults.Column(0) & "'"
        If rs.NoMatch Then
            MsgBox ("Not found")
        Else

              if rs.bookmarkable = true then
              msgbox "Can't bookmark this record
              else
              Me.Bookmark = rs.Bookmark
              end if

        End If
        Set rs = Nothing
    End If
End Sub