doubleclick on listbox populating subform

[Guest]

Hey guys,
I have an main for "Client2", with a subform "Deal", and "Deal" has a
subform "Portfolio" which is on a tab. On "Deal" I have a listbox that brings
up a list of Portfolios for that specific Deal_ID. I want the doubleclick on
the listbox to advance the "Portfolio" subform on the tab to the
corresponding Portfolio. This way the user can see a list of related
Portfolios and pick the one that they want to see information on.
Does anyone have any ideas on how to get the corresponding record into the
subform? I've tried:

Private Sub List21_DblClick(Cancel As Integer)
'Forms!frmClient2!frmDeal!frmPortfolio!Portfolio_Key = Me.List21.Column(0)
End Sub

I am getting "Run-time error '2448'
You can't assign a value to this object.

I know this means I am trying to replace the primary key in the Portfolio
record. Can anyone make a suggestion as to the correct direction I should
attempt this from? I have thought about GoToRecord, but I'm not a VBA expert.
Thanks for any help.

 

[Barry Gilbert]

Presumably, you have Link Child Fields and Link Master Fields
properties filled to link the form with the subform. The child field
should be set to Portfolio_Key for the Portfolio subform. If this is
the primary key in the portfolio subform, you just need to set the
corresponding field in the Deal form to the value you grabbed from your
listbox.
I'm not sure how your data is structured, but I would look into using
this form/subform relationship to do the link.

HTH,
Barry

 

[Guest]

Thanks for the response. Should the Child and Master fields be the same? Here
is my table structure:

A Client may have many Deals [Primary key: Client_ID]
A Deal may have many Portfolios [Primary key: Deal_ID]
Portfolio table primary key is Portfolio_ID

I changed the Link Child Field to Portfolio_ID, but it still would not let
me change it with the code from my previous post. I have referenced the
tables correctly using the primary key of the table above it in the
hirearchy, therefore Deal_ID is in the Portfolio form. Thank you for any
assistance you can provide.

 

[Barry Gilbert]

Your deals table should have a column with thr Client_Id in it and your
Portfolios should have a table with the Deal_Id in it. In the deals
form, when you click on the portfolios subform object, the Link Master
Fields should be set to the name of the primary key of the deals table.
The Link Child Fields should be set to the name of the name of the
deals field in the portfolios table.

If you work out the subform links, your code won't be necessary.

HTH,
Barry

 

[Guest]

It was the Link master field reference I wasn't getting. My tables are
referenced correctly, but the code I was trying to use was interpreting the
value from the 1st column of the list box as a txt value instead of an
integer. Glad it was such an easy fix.

Thank you very much for your help.