[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2010 search as you type list box

Posted on 2013-02-04
3
Medium Priority
?
4,444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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