Restricting combobox to Filtered Form

I have a form whose rowsource is a single table, i.e. 'Datasystem'.

I use a combobox to search for records in that table based on the
value of a single field, i.e., 'systemname'.

I use a multi-select listbox to filter the form on a different field,
i.e. 'domain', so that the filtered form can have several allowed values
of this field


I want to restrict my combobox to only the records in the filtered
form. I don't know how to write a query for its rowsource that will
use the selected values of the listbox to determine whether a record
is in the search space.

I'm sure I'm missing something obvious: things can't be as baroque as
I'm imagining them.

--thelma

Re: Restricting combobox to Filtered Form

Thelma Roslyn Lubkin wrote:

> I have a form whose rowsource is a single table, i.e. 'Datasystem'.
>
> I use a combobox to search for records in that table based on the
> value of a single field, i.e., 'systemname'.
>
> I use a multi-select listbox to filter the form on a different field,
> i.e. 'domain', so that the filtered form can have several allowed values
> of this field
>
>
> I want to restrict my combobox to only the records in the filtered
> form. I don't know how to write a query for its rowsource that will
> use the selected values of the listbox to determine whether a record
> is in the search space.
>
> I'm sure I'm missing something obvious: things can't be as baroque as
> I'm imagining them.
>
> --thelma
>

The code below will come close to what you need if you change the
listbox, combobox, and field names. Just because you select something
to filter in the listbox, you need an event to put your code in to
update the forms filter and combobox. That's where you'd call the code
below.


Dim var As Variant
Dim strHold As String
Dim strSQL As String
Dim intPos As Integer

'assumes rowsource is a select statement, not a query. Change
'ComboboxName to name of your combo. Get the current SQL statement
'for the combo box
strSQL = Me.ComboboxName.RowSource

'remove the ; at end of sql statement if it exists
intPos = Instr(strSQL,";")
if intPos > 0 then strSQL = Left(strSQL,intPos-1)

'remove the Where if it exists. If you have OrderBys and GroupBys
'you'll need to adjust this code even further
intPos = Instr(strSQL,"Where ")
if intPos > 0 then strSQL = Left(strSQL,intPos-1)

If Me.ListBoxName.ItemsSelected.Count > 0 Then
'some items in listbox are selected. Change ListBoxName
'to your name. Assumes the key is the first column in the
'list box. If you have 4 columns, the first is 0.
For Each var In Me.ListBoxName.ItemsSelected
strHold = strHold & Me.ListBoxName.Column(0, var) & ", "
Next

'remove the ", "
strHold = Left(strHold, Len(strHold) - 2)

'now recreate the SQL statement with filter, put space
'prior to word Where
strSQL = strSQL & " Where [YourFieldName] In (" & strHold & ")"
Endif

'if you have an orderby
'strSQL = strSQL & " Order By " & ...

'now present the comboxbox filtered from list
Me.Comboboxname.Rowsource = strSQL

West India
http://www.youtube.com/watch?v=cl4_XNa7JKI

Re: Restricting combobox to Filtered Form

Salad wrote:
: Thelma Roslyn Lubkin wrote:



:> I want to restrict my combobox to only the records in the filtered
:> form. I don't know how to write a query for its rowsource that will
:> use the selected values of the listbox to determine whether a record
:> is in the search space.

: The code below will come close to what you need if you change the
: listbox, combobox, and field names. Just because you select something
: to filter in the listbox, you need an event to put your code in to
: update the forms filter and combobox. That's where you'd call the code
: below.



Thank you very much.

I realized after I posted this that what I really wanted to say
is how do I write a query for the combo box based not on the
field in the table, but on the form's current record set.

I will try to implement this today.

--thelma

: West India
: http://www.youtube.com/watch?v=cl4_XNa7JKI

Re: Restricting combobox to Filtered Form

Thelma Roslyn Lubkin wrote:

> Salad wrote:
> : Thelma Roslyn Lubkin wrote:
>
>
>
> :> I want to restrict my combobox to only the records in the filtered
> :> form. I don't know how to write a query for its rowsource that will
> :> use the selected values of the listbox to determine whether a record
> :> is in the search space.
>
> : The code below will come close to what you need if you change the
> : listbox, combobox, and field names. Just because you select something
> : to filter in the listbox, you need an event to put your code in to
> : update the forms filter and combobox. That's where you'd call the code
> : below.
>
>
>
> Thank you very much.
>
> I realized after I posted this that what I really wanted to say
> is how do I write a query for the combo box based not on the
> field in the table, but on the form's current record set.
>
> I will try to implement this today.
>
> --thelma

If your combo can be associated with the filter, you could also try
something like
strSQL = "Select FieldName From TableName "
If Me.Filter > "" then
strSQL = strSQL & "Where " & Me.Filter
'for testing
msgbox strSQL
Endif
Me.ComboName.RowSource = strSQL

>
> : West India
> : http://www.youtube.com/watch?v=cl4_XNa7JKI

Re: Restricting combobox to Filtered Form

Salad wrote:

: If your combo can be associated with the filter, you could also try
: something like
: strSQL = "Select FieldName From TableName "
: If Me.Filter > "" then
: strSQL = strSQL & "Where " & Me.Filter
: 'for testing
: msgbox strSQL
: Endif
: Me.ComboName.RowSource = strSQL

*This* is that 'obvious' solution that I was looking for.
It works.
--thelma
:>
:> : West India
:> : http://www.youtube.com/watch?v=cl4_XNa7JKI