List Box

[johnaregan]

In Access I have set the RowSource of a list box to get its' values from a query, one of the results is then selected from the list box to be used in another 2nd query.

I also the user to have the option to select an option called "All" which would then be used in the 2nd query to return all the values from the table.

So how would I add the "All" option to the the listbox? And how would I use this in an SQL statement?

Thanks in advance
John

 

[RickSpr]

When I had to add "All" to a combo box, I used a custom VBA function as the Row Source property. It's a bit of coding, but you can start with the example in the Help. In the function, you'll have to read the data from the query with DAO and save it in an array, adding the "ALL" entry. When your function is called to get the row data, you provide the items from the array.

To use it in an SQL query, as part of a WHERE clause, try something like this:
...AND ((Forms!cboMyCombo = "ALL&quot OR ([fieldname] = Forms!cboMyCombo)...

Rick Sprague

 

[Guest_imported]

how do you select multiple items in a lstbox to a label wi commas on it?

Ex:
lstcondo.Additem"ketchup"
lstcondo.Additem"mustard"
lstcondo.Additem"relish"
lstcondo.Additem"mayo"

to a label

labelorder.caption= "You ordered a sandwich with" & lstcondo

To have it read : you ordered a sandwich with ketchup,mayo,and relish.

I took a Qbasic class and am now in vidual basic. We are on chapter 10 but these examples start in chapter 14 and the examples in he book do not show how this is done and the teacher isn't any help...
I would appreciate it and would like a response sent to my work address Vcarbonara@amerfirst.org
The problem i'm having is that the lstbox is checkbox style and it won't let me use the Multiselect feature for some reason?
please help..

 

[GTO]

'This might get you by

Option Explicit
Dim i As Integer
Dim chkcount As Integer
Dim iCheck As Integer
Dim condo As String
Dim countStr As String
Dim chkArray() As Integer

Private Sub Command1_Click()
Labelorder = ""
condo = ""

Lstcondo.ListIndex = 0
For i = 0 To Lstcondo.ListCount - 1
If chkcount = 0 Then GoTo errorhandler

If Lstcondo.ListIndex = 0 Then '1st item
If chkArray(i) = 1 Then condo = Lstcondo 'is checked, start building sentence
End If
If Lstcondo.ListIndex = 1 Then '2nd item & 3rd items
If chkArray(i) = 1 Then 'is checked
If condo = "" Then
condo = Lstcondo
ElseIf chkcount > 2 Then
condo = condo & ", " & Lstcondo
Else
condo = condo & " and " & Lstcondo
End If
End If
End If
If Lstcondo.ListIndex = 2 Then '3rd item
If chkArray(i) = 1 Then 'is checked
If condo = "" Then
condo = Lstcondo
ElseIf chkcount > 2 Then
condo = condo & ", " & Lstcondo
Else
condo = condo & " and " & Lstcondo
End If
End If
End If
If Lstcondo.ListIndex = 3 Then '4th item
If chkArray(i) = 1 Then 'is checked
If condo = "" Then
condo = Lstcondo
Else
condo = condo & " and " & Lstcondo
End If
End If
End If

If Lstcondo.ListIndex < Lstcondo.ListCount - 1 Then 'avoid going past end of listindex
Lstcondo.ListIndex = Lstcondo.ListIndex + 1
End If

Next
condo = condo & &quot; .&quot;
errorhandler: 'in case nothing selected

If chkcount = 0 Then
Labelorder = &quot;No Condiments ordered.&quot;
Else
Labelorder = &quot;You ordered a sandwich with &quot; & condo
End If
End Sub

Private Sub Form_Load()


With Lstcondo
.AddItem &quot;ketchup&quot;
.AddItem &quot;mustard&quot;
.AddItem &quot;relish&quot;
.AddItem &quot;mayo&quot;
End With


End Sub

Private Sub Lstcondo_ItemCheck(Item As Integer)
chkcount = 0 'start count of chks with 0
iCheck = Lstcondo.ListCount - 1
ReDim Preserve chkArray(iCheck)

If chkArray(Item) = 0 Then 'are list items checked?
chkArray(Item) = 1
Else
chkArray(Item) = 0
End If

For i = 0 To Lstcondo.ListCount - 1
If chkArray(i) = 1 Then
chkcount = chkcount + 1 'How many?
End If
Next
End Sub

 

[edderic]

For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
Label1 = Label1 & List1.List(i) + &quot;,&quot;
End If
Next i Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com