How to get a List Box with nothing Chosen to be All Chosen

[DOYLE60]

The code copied below is for a list box that must have at least one item
chosen. The items chosen are written to a query.

However, I need to change this code so that if nothing is chosen, than all are
actually chosen and written to the query. But if one is chosen, then that one
is chosen (not the others not chosen). In other words, I want it to work like
my Combo Boxes where if left null, all are chosen (for a filtering query), and
if filled in, just that one is chosen.

Thanks,

Matt
_____________________________________

Private Sub Command9_DblClick(Cancel As Integer)

Dim Q As QueryDef, db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![SalesRepsChosen]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm


If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the list box!", 0, "No
Selection Made")
Exit Sub
End If

' Modify the query.
Set db = CurrentDb()
Set Q = db.QueryDefs("SandraSalesRepqry")
Q.SQL = "Select * From [SalesReptbl] Where [SalesRepLastName] In(" & Criteria
& ");"
Q.Close

End Sub

 

[Alex Dybenko]

you can go though all listbox items and put them in Criteria
or just remove Where if Criteria=""

if len(Criteria)=0 then
Q.SQL = "Select * From [SalesReptbl]"
else
Q.SQL = "Select * From [SalesReptbl] Where [SalesRepLastName] In(" &
Criteria & ");"
end if

 

[Andi Mayer]

On 18 Nov 2004 17:35:17 GMT, (e-mail address removed) (DOYLE60) wrote:

Attention this is airCode!!!!!!!!!!

_____________________________________

Private Sub Command9_DblClick(Cancel As Integer)

Dim Q As QueryDef, db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![SalesRepsChosen]

dim I as long
if ctl.ItemsSelected.count=0 then
for I = 0 to cboMyCombo.ListCount -1
cboMyCombo.Selected(I) = True
next I
else
For Each Itm In ctl.ItemsSelected
Criteria = Criteria & ",'" & ctl.ItemData(Itm) &"'"
next Itm
Criteria=mid(Criteria,2)
End If

' Modify the query.
Set db = CurrentDb()
Set Q = db.QueryDefs("SandraSalesRepqry")
Q.SQL = "Select * From [SalesReptbl] Where [SalesRepLastName] In(" & Criteria
& ");"
Q.Close

End Sub

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW