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
Solved

Find Value in List Box Access VB

Posted on 2011-03-13
4
511 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

839 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