Identify the first selected item from a list box

[jcc285]

I'm sure this is simple but don't seem to be able to solve it !!

I am looping through a multi select list box for the selected values and building an SQL WHERE Clause using the values, however, for the first selected value the action needs to be different.

strSQL = "SELECT ID FROM tbl_Albums WHERE tbl_Albums.GenreLink = "
'Check for a Genre or Genres selection
If Me.lstGenre.ItemsSelected.Count > 0 Then
blnGenreSelected = True
'Loop through the selected items to build an SQL WHERE Clause
With Me.lstGenre
For i = 0 To .ListCount - 1
If .Selected(i) Then


If ???????????????????? Then ------------- Need to identify that this is the first selected item
strSQL = strSQL & .Column(0, i) ------------ So only the value gets added to the SQL
Else
strSQL = strSQL & " OR tbl_Albums.GenreLink = " & .Column(0, i) ------- Whereas, here need to Add the 'OR Clause and the value
End If
End If
Next i
End With
End If

Any help gratefully received

[Gicu]

Please try this:

DIm boIsFirst as boolean

[strSQL = "SELECT ID FROM tbl_Albums WHERE tbl_Albums.GenreLink = "

'Check for a Genre or Genres selection
If Me.lstGenre.ItemsSelected.Count > 0 Then
    blnGenreSelected = True
    boIsFirst =False
    'Loop through the selected items to build an SQL WHERE Clause
    With Me.lstGenre
         For i = 0 To .ListCount - 1
                If .Selected(i) Then
                     boIsFirst =True 'set the flag to True for the first record
                     If boIsFirst =True Then '------------- Need to identify that this is the first selected item
                          strSQL = strSQL & .Column(0, i) ------------ So only the value gets added to the SQL
                     Else
                          strSQL = strSQL & " OR tbl_Albums.GenreLink = " & .Column(0, i) ------- Whereas, here need to Add the 'OR Clause and the value
                     End If
                 End If
             boIsFirst =False 'reset the flag
         Next i
      End With
End If

Cheers,

[pbaldy]

Vlad, I'm not sure that won't see every selection as first, though I haven't tested. That said, I think it's over-complicating matters to worry about first. Also, what if they don't make a selection at all? I'd build an IN() clause, which won't care if there's 1 item or 100, plus I'd check for a selection being made. This does something similar but could be adapted to build the full SQL instead of just a wherecondition:

http://www.baldyweb.com/multiselect.htm

[Gicu]

Dim iFirst as integer,boIsFirst as Boolean


[strSQL = "SELECT ID FROM tbl_Albums WHERE tbl_Albums.GenreLink = "


'Check for a Genre or Genres selection
If Me.lstGenre.ItemsSelected.Count > 0 Then
    blnGenreSelected = True
    iFirst =0
    boIsFirst =False
    'Loop through the selected items to build an SQL WHERE Clause
    With Me.lstGenre
         For i = 0 To .ListCount - 1             
                If .Selected(i) Then
                   if iFirst =0 Then  boIsFirst =True 'set the flag to True for the first record
                     If boIsFirst =True Then '------------- Need to identify that this is the first selected item
                          strSQL = strSQL & .Column(0, i) ------------ So only the value gets added to the SQL
                     Else
                          strSQL = strSQL & " OR tbl_Albums.GenreLink = " & .Column(0, i) ------- Whereas, here need to Add the 'OR Clause and the value
                     End If
                 End If
             iFirst=i
             boIsFirst =False 'reset the flag
         Next i
      End With
End If

You're right Paul, it would have treated all as the first record, I think this revision would work if the OP wants to go this route.

Cheers,

[jcc285]

Thanks for your help and suggestions. I've gone with the option to build the IN() Clause. Code as below.

'We need all Albums available with any selected Genres.
strSQL = "SELECT ID FROM tbl_Albums WHERE tbl_Albums.GenreLink IN ("
'Check for a Genre or Genres selection
If Me.lstGenre.ItemsSelected.Count > 0 Then
blnGenreSelected = True
With Me.lstGenre
'Loop through the selected items to build an SQL IN Clause
For i = 0 To .ListCount - 1
If .Selected(i) Then
strSQL = strSQL & .Column(0, i) & ","
End If
Next i
End With
'Remove the last comma and replace with closing bracket and quotes )"
strSQL = Left(strSQL, Len(strSQL) - 1)
strSQL = strSQL & ")"
Else
strSQL = "SELECT ID FROM tbl_Albums"
End If

This produces 'SELECT ID FROM tbl_Albums WHERE tbl_Albums.GenreLink IN (1,12,9)' if selections have been made or 'SELECT ID FROM tbl_Albums' i.e. All Albums are selected ignoring 'Genre' as a criteria.

Once again thanks for your help.

[pbaldy]

Glad you got it working. I feel like the loop from my link is a little more efficient, as it would only loop selected items. That said, the difference is probably not significant.