Solved

Access 2010 search as you type list box

Posted on 2013-02-04
3
4,055 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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

688 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