passing records from a multilist box to parameter setting in a uer

[Guest]

I have already posted this before but have notgot a working solution
Can some one please tell me how to do this , this is driving me mad.

My list box is "list147"" on the main form
my unbound control is "Control" - on the main form
form name is "Maintestform"
sub form name is "Enitylist"
this subform runs a query that needs the input from "control" above.

Thanks
danny

 

[Marshall Barton]

I have already posted this before but have notgot a working solution
Can some one please tell me how to do this , this is driving me mad.

My list box is "list147"" on the main form
my unbound control is "Control" - on the main form
form name is "Maintestform"
sub form name is "Enitylist"
this subform runs a query that needs the input from "control" above.

The query should refer to the control using this kind of
syntax: Forms!Maintestform.Control

If you need more help with it, pleas post the subform's
query that you're currently using along with the details of
what the list box has to do with Control.

 

[Guest]

Hi marshall

I am trying to link all the records in my list box to the records that are
shown in my sub form.

The list box has records "CoCode"

The sub form "EntityList" runs a query that is based on a table called
"ALLDAT"

One of the fields in the table is called "CoCode"

I was trying to show records on my sub form that are based on the list box
i.e. in practice put parameter values in the query using some sort of
automation p which is not based on user inout but based on the relationship
between "CoCde".

The sub form is ok for only 1 CoCode but if I want to get say more than 1
CoCode (selection) coming from the list Box I am stuck

Thanks
Danny

 

[Marshall Barton]

I am trying to link all the records in my list box to the records that are
shown in my sub form.

The list box has records "CoCode"

The sub form "EntityList" runs a query that is based on a table called
"ALLDAT"

One of the fields in the table is called "CoCode"

I was trying to show records on my sub form that are based on the list box
i.e. in practice put parameter values in the query using some sort of
automation p which is not based on user inout but based on the relationship
between "CoCde".

The sub form is ok for only 1 CoCode but if I want to get say more than 1
CoCode (selection) coming from the list Box I am stuck

Ahhh, now I see what's going on.

The big problem is that you can not use a query parameter to
match multiple values. You will have to construct the
subform's record source query using code. Something along
these lines should get you started:

Dim strWhere As String
Dim varItm As Variant

For Each varItm In Me.thelistbox.ItemsSelected
strWhere = strWhere & "," & Me.thelistbox.ItemData(varItm)
Next varItm

If Len(strWhere) > 0 Then
strWhere = " WHERE CoCode IN(" & Mid(strWhere,2) & ")"
End If

Me.EntityList.Form.RecordSource = "SELECT AllDat.* " _
& "FROM AllDat " & strWhere