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
Solved

Access 2010 search as you type list box

Posted on 2013-02-04
3
3,842 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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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