[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Find Value in List Box Access VB

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
creativefusion
Asked:
creativefusion
3 Solutions
 
DockieBoyCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
creativefusionAuthor Commented:
Thanks guy's,

Great help as always.

CF
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now