Help me understand what this code is doing

[Miked1978]

I have a search query form and created a list box that the user will select a hull(s) from and the subform will return the results. I pieced together the following code and it works, sort of, but I don't quite understand what it is doing. When I say it works it does work if the list box MULTI SELECT property is set to NONE but it gives me an error (syntax error near WHERE clause) when I set the property to SIMPLE so the user can select more then one hull.

Im new to VBA and if I knew what the code did I might can figure it out.

Private Sub lstHull_AfterUpdate()
Dim myHull As String
Dim strSQL As String
Dim VarItem As Variant

'What does this do???
For Each VarItem In Me!lstHull.ItemsSelected
   myHull = myHull & ",'" & Me!lstHull.ItemData(VarItem) & "'"
Next VarItem


' Is this just for when a user selects multiple hulls?  
If Len(myHull) > 0 Then
  myHull = Right(myHull, Len(myHull) - 1)
  strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & myHull & ") AND "
End If



' I understand this is the SQL script that actually queries the table
strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
    If Len(Me!lstHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN ('" & Me!lstHull & "') "

'this is just telling it to use the SQL

Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL
Me.dbo_tblPrintCenter_subform.Form.Requery
End Sub

[CJ_London]

please use the code tags to preserve formatting. Otherwise difficult top read and many won't bother responding - I won't after this occasion

Private Sub lstHull_AfterUpdate()
Dim myHull As String
Dim strSQL As String
Dim VarItem As Variant

'What does this do??? iterating through a multiselect listbox to collect the items selected and putting them in a comma separated string call myHull
For Each VarItem In Me!lstHull.ItemsSelected
    myHull = myHull & ",'" & Me!lstHull.ItemData(VarItem) & "'"
Next VarItem


' Is this just for when a user selects multiple hulls? No - user has selected at least one hull so creates a criteria string, otherwise doesn't bother
If Len(myHull) > 0 Then
    myHull = Right(myHull, Len(myHull) - 1)
    strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & myHull & ") AND "'this line not required - it is used in the next block of code
End If



' I understand this is the SQL script that actually queries the table
strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
If Len(Me!lstHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN ('" & Me!lstHull & "') " 'this line is doing the same as the previous bit of code

'this is just telling it to use the SQL

Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL
Me.dbo_tblPrintCenter_subform.Form.Requery 'this line is not required, form is automatically requeried on the previous line
End Sub

[Miked1978]

Any idea why the Multi Select isn't working?

[pbaldy]

You don't use the variable in this line, you use the listbox. When it's multiselect, the value will be Null:

If Len(Me!lstHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN ('" & Me!lstHull & "') "

[Miked1978]

You don't use the variable in this line, you use the listbox. When it's multiselect, the value will be Null:

If Len(Me!lstHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN ('" & Me!lstHull & "') "

I changed it but still not working? Same error


If Len(Me!lstHull) > 0 Then Me!lstHull = Me!lstHull & "dbo_tblPrintCenter.hull IN ('" & Me!lstHull & "') "

[pbaldy]

That's not what I meant, but I'd delete that line anyway, the bit above is doing what you want (the bit Ajax said wasn't required ). You want it to work as a multiselect, and it won't work to refer directly to the listbox. You do need to add a line to trim the "AND" off the end (or if this is all the code, don't include it to begin with).

[CJ_London]

oh yes, missed that - but still not required because the second instance the sql includes the select clause. In the first instance, it doesn't. But the second instance should be referencing myHull, not lstHull

[pbaldy]

Agreed, one instance done right rather than two done wrong.

It also needs to be tweaked to account for no hull being selected. As is you'll get an invalid SQL statement.

[CJ_London]

This is how I would tweak it

Private Sub lstHull_AfterUpdate()
Dim myHull As String
Dim strSQL As String
Dim VarItem As Variant
Dim strCriteria as string

    'get selections from multiselect listbox    
    For Each VarItem In Me!lstHull.ItemsSelected
        myHull = myHull & ",'" & Me!lstHull.ItemData(VarItem) & "'"
    Next VarItem
    'if no items selected then myHull="" (default initialisation value)
    
    'build criteria string
    If myHull<>"" Then strCriteria = "dbo_tblPrintCenter.hull IN (" & mid(myHull,2) & ")"

    'build SQL string
    strSQL="SELECT * FROM dbo_tblPrintCenter"
    if strCriteria<>"" then strSQL=strSQL & " WHERE " & strCriteria

    'assign to form recordsource
    Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL

End Sub

[Miked1978]

This is how I would tweak it

Private Sub lstHull_AfterUpdate()
Dim myHull As String
Dim strSQL As String
Dim VarItem As Variant
Dim strCriteria as string

    'get selections from multiselect listbox    
    For Each VarItem In Me!lstHull.ItemsSelected
        myHull = myHull & ",'" & Me!lstHull.ItemData(VarItem) & "'"
    Next VarItem
    'if no items selected then myHull="" (default initialisation value)
    
    'build criteria string
    If myHull<>"" Then strCriteria = "dbo_tblPrintCenter.hull IN (" & mid(myHull,2) & ")"

    'build SQL string
    strSQL="SELECT * FROM dbo_tblPrintCenter"
    if strCriteria<>"" then strSQL=strSQL & " WHERE " & strCriteria

    'assign to form recordsource
    Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL

End Sub

Thank you that works great. I'm probably going to add some a couple more list boxes to the criteria so I may have some more questions lol