Multi select list boxes and SQL??

[VRach02]

I found some code that goes like this:


Dim strPara As String

Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!frmMain
Set ctl = frm!lstSubHeading
For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
strPara = "SELECT tblContent.ParaID, tblContent.paraName FROM tblContent INNER JOIN tblSubHeading ON tblContent.SubHeadingID = tblSubHeading.SubHeadingID WHERE tblContent.SubHeadingID = Forms!frmMain!lstSubHeading.ItemData(varItm)"
Me.lstPara.RowSource = strPara
Next varItm

All the items seem to be inorder and are right in the debug windows
but i won't display in the list box

Vishal

 

[VBslammer]

Each time through the loop you clear the lstPara.RowSource property and add a new one. What you should be doing is building a where clause and adding it to your base SQL statement:

Sub UpdateRowSource()
  
  Dim strSQL As String
  Dim strWHERE As String
  
  Dim frm As Form, ctl As Control
  Dim varItm As Variant
  
  Set frm = Forms!frmMain
  Set ctl = frm!lstSubHeading
  
  strSQL = "SELECT tblContent.ParaID, tblContent.paraName "
  strSQL = strSQL & "FROM tblContent INNER JOIN tblSubHeading "
  strSQL = strSQL & "ON tblContent.SubHeadingID = tblSubHeading.SubHeadingID"
  
  For Each varItm In ctl.ItemsSelected
    If Len(strWHERE) > 0 Then
      strWHERE = strWHERE & " OR tblContent.SubHeadingID =" & varItm
    Else
      strWHERE = strWHERE & " WHERE tblContent.SubHeadingID =" & varItm
    End If
  Next varItm
  
  Me.lstPara.RowSource = strSQL & strWHERE
  
End Sub

If each varItm is a string you'll have to add single quotes like this:

strWHERE = strWHERE & " OR tblContent.SubHeadingID ='" & varItm & "'"

VBSlammer

Unemployed in Houston, Texas

 

[BGrego]

Add this...

.
.
Me.lstPara.RowSource = strPara
>>Me.lstPara.Requery
Next varItm
.
.
.
you should notice though, the list box's record source will be changing on each iteration.