Listbox and Subform

[cbrsix]

I have a listbox that updates its records when a textbox on the form is changed.

Private Sub ClientSearch_Change()
    Dim sql As String
    
    'if you want to match any part of the string then add wildcard (*) before the FilterBy.Text, too:
    sql = "SELECT ClientNameList, ClientName FROM ClientList WHERE ClientName Like '*" & Me.ClientSearch.Text & "*' ORDER BY ClientName"
    
    Me.ClientNameList.RowSource = sql
End Sub
Private Sub ClientSearch_Click()
Me.ClientSearch = ""
Me.ClientSearch.FontItalic = False
End Sub
 
Private Sub Form_Load()
Me.ClientSearch = "Search..."
Me.ClientSearch.FontItalic = True
End Sub

I have also have a subform that updates to the selected client from the listbox. This works fine up until I narrow down the listbox with the above code. Once I narrow the list down, when I select a record in the listbox to display in the subform, it doesn't update the subform at all.



On the subform, the Masterlink field is ClientNameList and the Childlink field is ClientID

[pbaldy]

Perhaps because ClientID isn't in the row source? Make sure that matches whatever the row source is before it's changed with this code.

[cbrsix]

Thanks, I was able to figure it out.

[pbaldy]

Posting your solution could help later users searching for help.

[cbrsix]

yes it would. here it is.

Private Sub ClientSearch_Change()
    Dim sql As String
    If Me.ClientSearch.Text = "" Then
        sql = "SELECT [ClientList].[ClientID], [ClientList].[ClientName] FROM ClientList ORDER BY [ClientName]"
     Else
        sql = "SELECT [ClientList].[ClientID], [ClientList].[ClientName] FROM ClientList WHERE ClientName Like '*" & Me.ClientSearch.Text & "*' ORDER BY [ClientName]"
    End If
    
    Me.ClientNameList.RowSource = sql
    
End Sub