List Box

[melika]

Hi,

I want to be able to select multiple values from a form and then run a query from those values. I read through some posts and learned I cannot use a combo box, that I have to use a list box. I have no idea what to do next besides create the list box.

I have a table of Plants that I want to be my drop down. I assigned those to the list box. I created an OK button to open the query with the following code:
Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "ShortCode_byPlant", acViewPivotTable, acEdit
DoCmd.Close acForm, "Test"
End Sub

Then I put the following in the query criteria for the Plant field: Like IIf([Forms]![Test]![PlantList]="All","*",[Forms]![Test]![PlantList])

But, I don't get any values returned. Any help would be appreciated. Thanks!

 

[KenHigg]

Post 2 has a sample of one way to do this:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

(It's a little heavy on comments so it looks worse than it is - )

 

[melika]

Whew. That's a lot of code. I tried to plug in my variables in place of yours. But it didn't produce any results, not even an error.

I've highlighted the parts I've changed. The variables I had were:
PlantList which is the name of my list box and ShortCode_byPlant is the name of my query, with field Plant Code. I'm not very good at this...

Private Sub cmdUpdate_Click()

'This var holds the number of items in the list box
Dim intListCount
'This var is used to loop through all the items in the list box
Dim intX As Integer
'This var is used to build the WHERE part of the sql statement
Dim strSQL As String

'Get the number of items in the list box to use in the FOR NEXT loop below
intListCount = Me!PlantList.ListCount

'Start looping through list box items
For intX = 0 To intListCount
' If the item is selected
If Me!PlantList.Selected(intX) Then
' If there has not been any items selected yet
If strSQL & "" = "" Then
'Start the WHERE pc of the SQL statement
strSQL = "(ShortCode_byPlant.Plant Code)='" & Me!PlantList.ItemData(intX) & "' "
' If there has been an item selected
Else
'Add to the existing WHERE pc of the sql statement
strSQL = strSQL & " or " & "(ShortCode_byPlant.Plant Code)='" & Me!PlantList.ItemData(intX) & "' " '
End If
End If
'Got to the next item in the list box
Next intX
'If the above code did not find anything selected in the list box
If strSQL & "" = "" Then
' Do a message
MsgBox "No criteria selected, all records will be displayed", vbInformation, "Sytem Message..."
'Set the sql string to return all records
strSQL = "SELECT ShortCode_byPlant.Plant Code, ShortCode_byPlant.CSF Code FROM ShortCode_byPlant;"
'If the above code did find items selected in the list box
Else
'Build the sql statements with the dynamic WHERE pc
strSQL = "SELECT ShortCode_byPlant.Plant Code, ShortCode_byPlant.CSF Code FROM ShortCode_byPlant WHERE ((" & strSQL & "));"
End If


End Sub

 

[KenHigg]

It may be working but I don't see at the end where you are doing anything with the sql statement - ? Do you just want it to run and view the results in a datasheet view?

 

[melika]

I'm not really sure what the SQL statment does in your code.

I just want to be able to select multiple plant locations from the list box form, then send those to my query ShortCode_byPlant and have it produce the query in datasheet view with only the plants I've selected and other data (ShortCode_byPlant.Quantity, ShortCode_byPlant.FiscalYear, etc)... I don't know if I called on all of it below.

I also deleted a line from your original code at the very end because it said it as for a subform?

Sorry, I'm not much help... not really sure what I did vs didnt do

 

[KenHigg]

At the end of the code you are left with an sql statement which is analogous (for the most part) to a query. So for example if you are running a report you could have the reports record source set to the sql statement the above code built.

Hope this makes sense.