Help with queries

[Grek]

Hello,

I'm working on my first real access project (database can be downloaded here: https://www.sugarsync.com/pf/D296036_87_6433855088 ), trying to build a form with several list boxes that would be used as filters in a query. Results are then displayed in a sub-form.

Questions:

- 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.

Many thanks for you help,

Greg

 

[boblarson]

Check out this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=216821
and specifically my answer in post #6. It shows how to use a set of listboxes with multi-select. And for multi-select listboxes, they are null even when selections are made. So you can't check for null. You would check the ItemsSelected.Count to see if anything has been selected.

So change this part in my thread response:

If Me.lstITLead.Selected(0) <> True Then

to this:

If Me.lstITLead.ItemsSelected > 0 Then

 

[Grek]

Thanks - will try to understand your code
Is there a way to do this without VBA (I'm using access 2010)
Thanks,

 

[boblarson]

Is there a way to do this without VBA (I'm using access 2010)

For multi-select listboxes, not really. Well, you could probably use the new macros in 2010 to do it as I believe you can loop using those. But VBA would be the way that would probably be the easiest for this type of thing.

 

[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)