Solved

Access 2010 search as you type list box

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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