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"):
Code:
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!