Limiting object group selection in listbox

[Frizzellio]

I have an object group setup like a telephone rolodex (A, B, C, etc) that populates a listbox with names based on the letter chosen. When the form opens, the listbox is populated with names based on a query with particular checkbox criteria indicating the name is active. This works fine. However, when I press any button in the object group, it populates the listbox with all names for the given letter, including those marked inactive. Here's the code I'm using for the object group:

Private Sub selAlpha_Click()
Dim strStart As String
Dim strEnd As String
Dim strOrder As String
Dim strKeyField As String
Dim i As Integer

    strStart = Left(Me.Controls("B" & Trim(Int(Me.selAlpha.Value))).Caption, 1)
    strEnd = Right(Me.Controls("B" & Trim(Int(Me.selAlpha.Value))).Caption, 1)

    'Begin revision 7/6/00
    If InStr(1, Me.NameList.RowSource, ";") > 0 Then
        Me.NameList.RowSource = Left(Me.NameList.RowSource, InStr(1, Me.NameList.RowSource, ";") - 1)
    End If
    'End revision 7/6/00
    
    i = InStr(1, Me.NameList.RowSource, "ORDER BY")
    If i = 0 Then
        Exit Sub
    Else
        strOrder = Mid(Me.NameList.RowSource, i)
        strKeyField = Mid(Me.NameList.RowSource, i + 9)
        Me.NameList.RowSource = Left(Me.NameList.RowSource, i - 1)
    End If
    
    i = InStr(1, Me.NameList.RowSource, "WHERE")
    If i > 0 Then Me.NameList.RowSource = Left(Me.NameList.RowSource, i - 1)
    
    Me.NameList.RowSource = Me.NameList.RowSource & " WHERE Left(" & strKeyField & ",1) >= '" & strStart & "' AND Left(" & strKeyField & ",1) <= '" & strEnd & "' " & strOrder
    Me.NameList.Requery
End Sub

How do I limit the object group selection to active records only?

Thanks in advance.

 

[Zion7]

Wouldn't you just add that clause to your WHERE statement?

Me.NameList.RowSource = Me.NameList.RowSource & " WHERE Left(" & strKeyField & ",1) >= '" & strStart & "' AND Left(" & strKeyField & ",1) <= '" & strEnd & "' " & strOrder & _
" AND chkActive = -1"

 

[Frizzellio]

Figured it out. You were on the right track, I just had to plug the added reference in a different spot as follows:

Me.NameList.RowSource = Me.NameList.RowSource & " WHERE [B][u]Active = True AND[/u][/B] Left(" & strKeyField & ",1) >= '" & strStart & "' AND Left(" & strKeyField & ",1) <= '" & strEnd & "' " & strOrder

Thanks!