Problems linking a subform to a combobox on the main form

Question

On a form in an access ADP there is an unbound combobox that displays a list of companies (the name is shown, the id is the bound field). When a company is chosen, I want to display the subscription information in a subform (the datasource for which is companySubscription view) for that company. I set the link Master Fields and links child Fields property of the subform to the companyId. Basically, I set it up like this.

In theory, I would think this would mean that when I change the value in the combobox the subform should show the subscription information for that company. It isn't working though- the subform always shows all the data in the companySubscription view, no matter what the combobox is set to.

Help!

Do you know how to switch the parent form's current record based on what is chosen in the combobox? — dmr
@Hansup The link master field does not have to be a field, it can be a control, and the control can even hold straight text. I have used this often. — Fionnuala

dmr dmr · Accepted Answer · 2010-09-21T16:57:35

Found the answer- had some code from another project that helped:

Private Sub cmbSub_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[subID] = " & str(Nz(Me![cmbSub], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

And had to modify it for an ADP (thanks to this post!)

Private Sub ChooseCo_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As ADODB.Recordset

    Set rs = Me.Recordset.Clone
    rs.Find "[companyId] = " & Str(Nz(Me![ChooseCo], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Problems linking a subform to a combobox on the main form

1 Answers