Can't get my query work with a combo box

[Grek]

Hi,

I'm trying to build a simple query from a form where I have a combo box listing my clients.



My form is called frmPipelineResults.
In this form I have:
- a combo box named lstClients - row source = SELECT [tblPipeline].[ID], [tblPipeline].[Client Name] FROM tblPipeline ORDER BY [Client Name];
- a subform named subformpipeline with source object Query.qryPipeline
- a command button that says: Private Sub cmdRunQuery_Click()
Me.subformpipeline.Requery
End Sub

In my query qryPipeline I put as criteria [frmPipelineResults]![lstClients]

I'm expecting the subform to display the results of my selection, but unfortunately it doesn't work. What am I doing wrong? (file is attached)

Many thanks for your help

[CraigDolphin]

The problem is that your listbox (not a combo box btw) is showing the name but storing the ID value. Consequently, you're putting the ID value as a criteria in the name field of your query.

So, when you select 'Client 1' the listbox 'stores' the ID value '1'

In your query, you're listing the names 'client 1', 'client 3' etc and restricting the list to those that match the stored id value in your listbox. 'Client 1' <> '1' Apples do not equal oranges.

You either need to get rid of the id field from your listbox rowsource, OR change the bound column property from 1 to 2 OR leavew the listbox as is and change the query so that the criteria is applied to the ID column, not the name column.

HTH

[Grek]

Many thanks for the detailed explanation. It worked well!

I have a few additional questions though (sorry!):

- I tried to change the bound column to 2 – it worked fine. However to avoid doing this I also tried what you suggested (deleting the ID column in the query so now I have SELECT T_NewLaunches.[Client Name] FROM T_NewLaunches; as row source for my list box, but now it doesn’t return any result any more. Any idea why?
- How can I make the query work with multi selection? I tried to change the propriety “multi select” to “extended”, but my query stopped working :/
- How can I add a “Null” value in each of my list boxes? (so that when nothing is selected my sub form returns all the results). I do not see how to do that since the values in my list boxes come from the values in my table T_NewLaunches

Thanks again for your help

[CraigDolphin]

Well, for one: your listbox properties are still set to have 2 columns and the width of the first column is set to 0: so you can;t see the values in the list.

Second, you've added two more criteria to the query so all three conditions must be met to return any rows.

You can use a criteria with the follwing syntax:
Like Forms!fmName!ControlName & "*"
to return values when no item is selected in the control.

As for multiselect, that's a whole different kettle of fish and I currently do not have time to go into it in detail. Sorrry

[Grek]

Hi,
If it can help someone, I used this for multiselect list boxes, and it works great (not my code - don't know the source)
Not sure to understand how it works though

To put in a module:

Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(strListBoxName)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End Function

To put in your query, as a new field: IsSelectedVar("F_MainSearch","lstClients",[Client Name])
You also need to put -1 as criteria (don't know why)