Solved

Find Value in List Box Access VB

Posted on 2011-03-13
4
510 Views
Last Modified: 2012-05-11
Hi All,

I have a list box ("SKUList") that is populated with an SQL using VBA. It contains 2 items; 1 = Item No, 2 = Weeks Before Review (which is a calculated value). The SQL is sorted by item 2 due to its importance, however, it is often difficult to find a particular Item No by scrolling through as the number of records in most cases reach into the hundreds.

Is there any way I can use a search or find function to locate a particular record inside the list box?


CF
0
Comment
Question by:creativefusion
4 Comments
 
LVL 3

Assisted Solution

by:DockieBoy
DockieBoy earned 125 total points
ID: 35125580
I generally use the on change event of a text box to specify the "Where" criteria in the query, for example,

Private Sub MyTextBox_Change()
 Dim MySource As String
 MySource = "select * from MyQueryOrTable where MyField Like '*" & Me.MyTextBox.Text & "*';"
 Me.MyListBox.RowSource = MySource
 Me.MyListBox.Requery
End Sub

This would effectively limit the list items to items that contain the text in the textbox on a character by character basis, if you want to search after you have entered the full criteria simply trigger the code from the On Exit or Lost Focus or Keypres (then look for the enter key being pressed), or use a command button, plenty of options :)
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35125683
"Is there any way I can use a search or find function to locate a particular record inside the list box?"
If you use a Combo box instead, then you can take advantage of the type ahead feature ... start typing to match the desired item.  Be sure the Auto Expand property of the Combo box is set Yes - see image.

mx


Capture1.gif
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 35130612
First, consider using a combobox as MX suggest, ...another reason is that you are just needing to find 1 record (Not filter for multiple selections)


There is a wizard that will create this functionality for you
...
Open the form in design view.
Insert a combobox on the form.
When the wizard starts, select: "Find a record on my form..."
You should then be able to follow all the rest of the Wizards steps...

JeffCoachman
0
 

Author Closing Comment

by:creativefusion
ID: 35135016
Thanks guy's,

Great help as always.

CF
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 5 53
Access 2007 Switch To/Retry/Cancel Pop-Up 6 30
Data Type Mismatch in Criteria - Access 6 31
Problem copying record details to a new record 5 8
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now