ListBox Search

[r3df1sh]

Need some guidance/suggestions on this one please.

I have a form Frm_IPList. That form house a ListBox LB_IPList. The list box utilizes a query to populate the multiple fields displayed from a table TBL_TCPIPLIST. I have already successfully setup a ComboBox that will change the records displayed based on it's value. What I am having dificulty with is the following:
I have created a Textbox IP to make a simple search. I have already made the necessary criteria in the query [Forms]![Frm_IPList]![IP]. I have used the After_Update and placed the following code:
Private Sub IP_AfterUpdate()

If Me!IP = "" Then
Me!LB_IPList.RowSource = "select * from TBL_TCPIPLIST;"
Else
Me!LB_IPList.RowSource = "Select * from TBL_TCPIPLIST where TCPIP = '" & Me!IP & "';"
End If
Me.Refresh
Me.LB_IPList.Requery
End Sub

As it works now I have to enter the data then change focus from the field for it to display the specific record(s). Additionally I created a commandbutton to reset the data based on the entry in the beforementioned combobox.

I am looking for the following:
1) Create a more real time search, meaning as I type it narrows the records shown.
2) When the command button is pressed (Without running a macro), it not only performs the requery, but also emptied the contents of the IP text box.

I have included my code for my combobox/reset buttons:

If Me!CB_IPList = "ALL" Then
Me!LB_IPList.RowSource = "select * from TBL_TCPIPLIST;"
Else
Me!LB_IPList.RowSource = "Select * from TBL_TCPIPLIST where location = '" & Me!CB_IPList & "';"
End If

Me.LB_IPList.Requery

Thanks in advance for any responses/suggestions.

(I am relatively new to operating in the Access/VB world, as I spend most of my time in the Networking world.)

 

[r3df1sh]

Please disregard question 2.
It might not be the right way to accomplish this but here's what I did.

In the command button I placed:

If Me!CB_IPList = "ALL" Then
Me!LB_IPList.RowSource = "select * from TBL_TCPIPLIST;"
Else
Me!LB_IPList.RowSource = "Select * from TBL_TCPIPLIST where location = '" & Me!CB_IPList & "';"
End If
Me!IP.Value = ""
Me.LB_IPList.Requery

 

[r3df1sh]

For those interested I have changed the way my text boxes search to now allow partial entry.

Placed in After_Update of TextBox

If Me!DID = "" Then
Me!LB_IPList.RowSource = "select * from TBL_TCPIPLIST;"
Else
Me!LB_IPList.RowSource = "Select * from TBL_TCPIPLIST where DID like '*" & Me!DID & "*';"
End If
Me.Refresh
Me.LB_IPList.Requery

Although this does not satisfy my origional question it will serve in my case as a route around what I am wanting to do.