master detail subforms in ms access 2010

Question

I have a customers table in an MS Access 2010 database. I want to create a form with two subforms which together allow the users to select a customer and see more detail about the customer.

The customers table looks like:

CustomerID  
FullName  
Address  
City  
StateProvince  
Other fields

The mainForm I created in design view by dragging CustomerListForm and CustomerDetailForm onto MainForm.

CustomerListForm is on left side and lets users filter customers from long list  
  Contains the following controls:  
    txtFilter textbox with embedded macro where = [LastName] Like [Forms]![CustomerListForm]![txtFilter] & "*"  
    FullName textbox and hidden CustomerID textbox which are bound to CustomersTable  
CustomerDeatilForm located on right, with contents changing based on selection from CustomerListForm  
    If no customer selected from CustomerListForm, show default message  
    Else:  
        Show FullName, address, city, and stateprovince of customer selected from CustomerListForm  
        txtFullName set =[CustomersTable]![FullName], and so on for other fields

When I run my CustomerListForm separately, it does successfully allow a user to filter customer records by typing in a name. But that filtering ability goes away when I embed CustomerListForm in mainForm. Also, my filtered results in CustomerListForm do not contain any sort of links, which means that CustomerDetailForm does not seem to be able to identify which customer it should be outputting data about.

Can anyone show me how to set this up? I think if I get this much running, I will be able to fill in the blanks from other research that I am doing.

EDIT:

I see that the FullName textbox on CustomerListForm has an OnClick method. FullName is not editable in CustomerListForm. CustomerID is a hidden field alongside each record in CustomerListForm. I also see that there are multiple hyperlink settings in the format tab of the property sheet for the FullName textbox. Is there some way that the OnClick method of the FullName textbox can be used to send the CustomerID to CustomerDetailForm? Perhaps in a hyperlink?

John Bingham John Bingham · Accepted Answer · 2013-09-17T00:07:50

The subforms are presumably linked to the parent form by CustomerID?

Remove this value from the LinkMasterID, LinkChildID properties of the subform control (in the parent form - as opposed to on the form properties of the subform itself)

Remove the macro, and create an AfterUpdate event for txtFilter, which finds the relevant data and displays it in the CustomerListForm; In addition to whatever other code is in this procedure, put

Dim CustID as Long: CustID = [get customerID from wherever it is]

... [your other code]

Me.Parent.LockCustomer CustID

In the outer form, add this in vba:

Public Sub LockCustomer(CustID as Long)
    CustomerDetailForm.Form.RecordSource = _
        "SELECT * FROM Customer WHERE CustomerID = " & CustID
End Sub

If you've customer details of any sort on the main form in addition to the subforms, and you want to show the details of the selected customer in the listform on both the main form and the detail subform, instead of setting CustomerDetailForm.Form.RecordSource, set Me.RecordSource & ensure that the LinkMasterID, LinkChildID properties of the Detail subform control are set to CustomerID.

master detail subforms in ms access 2010

1 Answers