Limit unbound filter (combobox) only to the values available in the form

[mishash]

My form Deals is based on query, lists securities' buy/sell deals and includes SecurityName, SecuritySymbol, DateOfDeal, PriceOfDeal etc.
The forms opens from another form Clients bearing the client name filter (it contains only the deals of the client, chosen in the Clients form), so it has subform with records of the deals and mainform which inherits the client name.
In the Deals subform I have an unbound combo-box SelectSymbol, which filters the form by selected security' deals.

SELECT [SecurityDetails].SecurityName, [SecurityDetails].SelectSymbol
FROM [SecurityDetails]
ORDER BY [SecurityDetails].SecuritySymbol;



Private Sub SelectSymbol_AfterUpdate()
Me.Filter = "SecurityName= '" & Me!SelectSymbol& "'"
Me.FilterOn = True
End Sub

The combobox, naturally, lists all the securities there are in the DB (thousands of records).
I would like to limit it to only the securities which are available int the current form Deals, shown for the chosen client (tens of records).
Tried to put criteria [Forms]![Deals ]![DealsSubform].[Form]![SecuritySymbol] in the combobox' Row Sours, but it limits the combobox list only to the 1st record.

Please help.

[andy49]

I'd suggest creating a text box somewhere on the form (invisible) showing the security symbol

Then add where [securitydetails].security symbol = me.nameoftextbox to the rowsource of the combobox


Sent from my iPhone using Tapatalk

[aytee111]

In the query for the combobox, put in the criteria of the field "=Forms!mainformname!Client" (without the quotes). Then in the AfterUpdate of the client in the main form requery the combobox (Me!subformname!comboboxname.Requery)

[mishash]

Thanks Andy!