Solved

Find Value in List Box Access VB

Posted on 2011-03-13
4
512 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 - Microsoft MVP, Access and Data Platform) 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

Industry Leaders: 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

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…
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 utilizing SQL Server views 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 Access…
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…

763 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