Linking sub form with list box

[Guest]

Hi

How can I use a list box with a sub form. I basically want to display the
sub form in a list box but I cannot see the LinkChildFields and
LinkMasterFields properties.

Is there any way of doing this?

 

[Jeff Boyce]

Edgar

A list box is a control in which you can list multiple records. A subform
is a control in which you can list multiple records. Why do you feel you
need to embed one within the other?

 

[Allen Browne]

As you found, a list box does not have the LinkChildFields and
LinkMasterFields properties.

You can put the name of the list box in the LinkMasterFields, and your
subform will show the records matching the one in the list box. This is not
a bad design for simple navigation: list box on the left, subform on the
right.

If you want the list box to behave as a subform, so it displays the records
matching the main form record, you have to use the Current event of the main
form to set the RowSource property of the list box to a suitable SQL
statement.

 

[Guest]

Basically what I have is a Tab Control with the Main form displaying supplier
details and the second form displaying contracts relating to the suppliers.

My problem is that there could be more than one contract per supplier so I
wanted to display a list of the contracts so the user can then select one of
the contract from the list and then it should show the details of the
contract in a sub form below.

I can get list of all contracts in the list box and I can link this to the
contract details fields below but this doesn’t filter the list box depending
on the current supplier in the main form.

How would I set the Rowsource of the List Box in the current event? Would I
set this in the current event in the main form or the contracts tab? Is the
SQL below correct?

SELECT DISTINCTROW que_ContractInfo.ContractDate, que_ContractInfo.SupplierID
FROM que_ContractInfo
WHERE (((que_ContractInfo.SupplierID) Like [Forms]![Tab
Control_Experiment]![Text18]));

Linking field is SupplierID which is in the query que_contractinfo which is
the record source for the Contracts tab.
Thanks for your time

 

[Allen Browne]

That might work, though it might be better do do it dynamically:

Private Sub Form_Current()
Dim strSQL As String
strSQL = "SELECT DISTINCTROW que_ContractInfo.ContractDate,
que_ContractInfo.SupplierID FROM que_ContractInfo WHERE
que_ContractInfo.SupplierID = " & Nz(Me.Text18,0) & ";"

Me.[NameOfYourListBoxHere].RowSource = strSQL
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Basically what I have is a Tab Control with the Main form displaying
supplier
details and the second form displaying contracts relating to the
suppliers.

My problem is that there could be more than one contract per supplier so I
wanted to display a list of the contracts so the user can then select one
of
the contract from the list and then it should show the details of the
contract in a sub form below.

I can get list of all contracts in the list box and I can link this to the
contract details fields below but this doesn't filter the list box
depending
on the current supplier in the main form.

How would I set the Rowsource of the List Box in the current event? Would
I
set this in the current event in the main form or the contracts tab? Is
the
SQL below correct?

SELECT DISTINCTROW que_ContractInfo.ContractDate,
que_ContractInfo.SupplierID
FROM que_ContractInfo
WHERE (((que_ContractInfo.SupplierID) Like [Forms]![Tab
Control_Experiment]![Text18]));

Linking field is SupplierID which is in the query que_contractinfo which
is
the record source for the Contracts tab.
Thanks for your time

As you found, a list box does not have the LinkChildFields and
LinkMasterFields properties.

You can put the name of the list box in the LinkMasterFields, and your
subform will show the records matching the one in the list box. This is
not
a bad design for simple navigation: list box on the left, subform on the
right.

If you want the list box to behave as a subform, so it displays the
records
matching the main form record, you have to use the Current event of the
main
form to set the RowSource property of the list box to a suitable SQL
statement.


message