Solved: listbox

[tkaplan]

I have a form with a list box that i am trying to use as criteria for a query. (there is other criteria that goes into this query as well but that all works fine)

Form: MainForm
Listbox: lstCenter

Query:
Center
Date
Manager
...

QueryCriteria: Center = selected on lstCenter.

How can I do this???

thanks in advance

tkaplan

[Norie]

Not 100% sure if this is right but try this as a parameter for the query.




[Forms]![MainForm]!lstCenter!Value

[tkaplan]

that doesn't work.

anyone have any other suggestions?

[Norie]

How does it not work?

[Tommy]

Hi tkaplan,
This will access the listbox named lstCenter on form MainForm, keeping in mind that unless an item is selected the value will be null. So you will need to have


If MainForm.lstCenter.ItemsSelected.Count = 1 Then '> 1 more than 1 item selected
MsgBox MainForm.lstCenter.Value
Else
MsgBox "Please select a Center."
End If

QueryCriteria: Center = MainForm.lstCenter.Value

[tkaplan]

this does not give me the value either. it always returns null.
i found a roundabout way of doing it by setting a textbox value to loop through all of the selected and seperate by commas and that worked. but i was hoping there is an easier way???

[Norie]

So it's a multiselect listbox?

You never mentioned that.

[Tommy]

OK This way detects if there is an item selected, if there is then it will display, if there is more than 1 selected it will display all of them. This only accounts for 1 column. This also does not account for multiple queries which needs to be considered for multiple selections. :)

Dim mI As Long
If MainForm.lstCenter.ItemsSelected.Count > 0 Then
For mI = 0 To MainForm.lstCenter.ItemsSelected.Count - 1
MsgBox MainForm.lstCenter.ItemData(MainForm.lstCenter.ItemsSelected(mI))
Next
Else
MsgBox "Please select a Center."
End If

[tkaplan]

what would i put as the criteria for the query?

relating to this but another question:
once i have this working, i have a drop down box which displays items based on what was selected in the listbox (multiselect...).
i need that if i change a selection in the listbox, the combobox will change as well. this works by putting a refresh on the combobox enter event. however, when i refresh, the listbox no longer shows selected items (i currently have a text box holding the values of the listbox so it is maintaining it, but the user cant see it anymore.

any ideas?

[Tommy]

what would i put as the criteria for the query?

As nothing but a guess :)


Dim SQL As String
SQL = "SELECT * FROM TABLE WHERE Center = "
For mI = 0 To MainForm.lstCenter.ItemsSelected.Count - 1
If mI <> MainForm.lstCenter.ItemsSelected.Count - 1 Then
SQL = SQL & MainForm.lstCenter.ItemData(MainForm.lstCenter.ItemsSelected(mI)) & " or Center = "
Else
SQL = SQL & MainForm.lstCenter.ItemData(MainForm.lstCenter.ItemsSelected(mI))
End If
Next


The refresh method is going to reset everything on the form. I would suggest maybe a combo.requery using one of the selections in center.

[tkaplan]

requery did it.
thank you.

for the criteria, i'm just going to keep my text box situation for now.

thank you so much for your help.
tkaplan