Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find Value in List Box Access VB

Posted on 2011-03-13
4
Medium Priority
?
517 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
[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
4 Comments
 
LVL 3

Assisted Solution

by:DockieBoy
DockieBoy earned 500 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 - Microsoft MVP, Access and Data Platform) earned 1000 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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

661 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