Multi item Selection from ListBox to a Query

[ragu111]

My Multi selection is working fine, but my SQL is not working with the selection.

[red]mySQL[/red]

SELECT Assets.AssetID, Assets.Model, Assets.Asset_Cat_Code
FROM Assets
WHERE (((Assets.Asset_Cat_Code) In ([forms].[Report].[Text28])));
[blue]------------------------------------------------------[/blue]

[red]My Code to Select Multi items[/red]

Private Sub List20_AfterUpdate()
    Dim varItem As Variant
    Dim txtTemp As String
    For Each varItem In Me.List20.ItemsSelected
        txtTemp = txtTemp & "'" & Me.List20.ItemData(varItem) & "'" & " Or "
    Next
    If Len(txtTemp) > 0 Then
        txtTemp = Left(txtTemp, Len(txtTemp) - 4)
    End If
    Me.Text28.Value = txtTemp
End Sub

can someone check the code and tell me what went wrong?

ragu

 

[Remou]

I do not think this is going to work with a saved query. Build the SQL string and add it to the query, rather than referencing the form. I notice you have 'Or' in the control you are using for the In string; 'In' looks like this:

In ("Avenue","Boulevard","Calle")

 

[ragu111]

dear Remou

request you to give little more explanation how to build the SQL string and add it to the query

thanks
ragu

 

[Remou]

Sub BuildQuery()
Dim qdf As QueryDef
Dim strIn, strSQL

strIN = "'aaa','bbb'"
strSQL = "SELECT Members.Code From Members " _
& "WHERE Members.Code In ( " & strIN & ")"

If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='qryQuery'")) Then
'If query exists, update the SQL ...
    Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)
Else
'If it doesn't, build it.
   Set qdf = CurrentDb.QueryDefs("qryQuery")
   qdf.SQL = strSQL
End If
        
'DoCmd.OpenQuery "qryQuery"

End Sub

 

[ragu111]

where will i put this code?

 

[Remou]

Where is your SQL?