views:

29

answers:

1

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!

A: 

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
dmr
That's the code produced by the combo box wizard when you choose to create a FIND combo box. I don't know about the ADP version, but the first one is one of the worst pieces of auto-generated code in existence. I tear it to shreds in this post: http://stackoverflow.com/questions/3401645/how-do-i-bring-up-a-record-based-on-a-combobox-in-access/3418490#3418490 -- in that article I find "an error rate of at least 2.5 problems per line of code created". My criticism is for code in an MDB/ACCDB, but 75% of the criticism apply in an ADP, too -- it's really crappy code.
David-W-Fenton