[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Access 2010 search as you type list box

Posted on 2013-02-04
3
Medium Priority
?
4,813 Views
Last Modified: 2013-02-04
Hi,

I have a List Box (Me.lstCustomer) that is populated from a query "qryCustomerListBox" with that shows 3 columns "Contact Name", "PostCode", "Address". The bound column (1) that contains the customer ID is hidden.

I wish to put a text box (Me.txtCustomerSearch) in the form that "Searches as you type" and filter the List Box results to reflect this but am having a bit of difficulty.

Can anyone provide a vba code sample to assist please?

Many thanks
0
Comment
Question by:andrewpiconnect
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38852701
0
 
LVL 75
ID: 38852729
You might look at this also:

Access Techniques: Fast Table Lookup Functions
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1921-Access-Techniques-Fast-Table-Lookup-Functions.html 

In fact, Harfang has an entire series of articles on this regarding this subject in general.

For example:
Using a Criteria Form to Filter Records - Part 1 of 3
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_6069-Using-a-Criteria-Form-to-Filter-Records.html

mx
0
 

Author Closing Comment

by:andrewpiconnect
ID: 38853225
Thanks for that.

I downloaded the sample database from he link you provided and implemented the following code which i have copied here for anyone else that wants a quick solution:

Many thanks

VBA for the Text Box
===============================================
Private Sub txtCustomerSearch_Change()
' This is fired with every change made with to the data in the combo - either by typing or by making a drop down selection.
' The .TEXT property is used to filter the listbox as the user types or makes a selection.
On Error GoTo EH
    Me.lstCustomer.RowSource = "SELECT qryFCCustomerList.Customer_ID, qryFCCustomerList.[Contact Name], qryFCCustomerList.PetName, " & _
    "qryFCCustomerList.Postcode FROM qryFCCustomerList WHERE " & _
    "LastName LIKE " & Chr(34) & Me.txtCustomerSearch.Text & "*" & Chr(34) & " " & _
    "OR PetName LIKE " & Chr(34) & Me.txtCustomerSearch.Text & "*" & Chr(34) & " " & _
    "OR Postcode LIKE " & Chr(34) & Me.txtCustomerSearch.Text & "*" & Chr(34) & " " & _
    "ORDER BY qryFCCustomerList.[Contact Name], qryFCCustomerList.PetName;"
    Exit Sub
EH:
    MsgBox ("Error " & Err.Number & ": " & Err.Description)
   
End Sub
===============================================

VBA for the Clear Search field Button
===============================================
Private Sub cmdClearSearch_Click()
' Clear form controls
On Error GoTo EH
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acListBox Or ctl.ControlType = acComboBox Then ctl = Null
    Next
    Me.lstCustomer.RowSource = "SELECT qryFCCustomerList.Customer_ID, qryFCCustomerList.[Contact Name], qryFCCustomerList.PetName, qryFCCustomerList.Postcode FROM qryFCCustomerList ORDER BY qryFCCustomerList.[Contact Name], qryFCCustomerList.PetName;"

    Exit Sub
EH:
    MsgBox ("Error " & Err.Number & ": " & Err.Description)
   
End Sub
===============================================

VBA for when double click on a row in the list box
===============================================
Private Sub lstCustomer_DblClick(Cancel As Integer)
On Error Resume Next

        Dim strLinkCriteria As String
        strLinkCriteria = "[Customer_ID]=" & Me.lstCustomer.Column(0)
        DoCmd.OpenForm "frmCustomerDetails", acNormal, , strLinkCriteria
       
End Sub
===============================================
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question