Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Search a field chosen by the User

Posted on 2015-02-04
4
Medium Priority
?
110 Views
Last Modified: 2015-03-10
I wish to have a ComboBox that would contain a list of all the fields that are available in a DataSet. The User would select, from the ComboBox, the field he/she wishes to search. The User would then tab to a TextBox and enter the search criteria. The code would search, the selected field in the DataSet, for the records that "contain" the search criteria and present a list of matching records to the User. The User would then choose a record from the list and select the record to fill a form with the appropriate data.

Any assistance would be appreciated.
0
Comment
Question by:ergenbgr
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40589682
1.  Set the RecordSourceType property of the combo box to: Field List
2.  Set the RecordSource property of the combo box to the table or query you want to list the fields for.
3. I generally, include a "Filter" or "Search" button to the right of the "search for" textbox.  In the click event of that button, I generally do something like:

Private Sub cmd_Search_Click

    Dim strFilter as string

    if me.cbo_SearchWhat & "" = "" Then
       msgbox "Select a field to search!"
       me.cbo_SearchWhat.SetFocus
       Exit Sub
    Elseif me.txt_SearchFor & "" = "" Then
       msgbox "Enter a value to search for!"
       me.txt_SearchFor.SetFocus
       Exit Sub
   End If

   strFilter = "[" & me.cbo_SearchWhat & "] LIKE '*" & me.txt_SearchFor & "*'"
   me.filter = strFilter
   me.FilterOn = True

End Sub

Open in new window

I would normally put the combo, textbox, and command button in the form header and have the filter apply to the data displayed in a continuous form below the header.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40591707
Text searches don't always work the way you expect them to on numeric fields including dates.  I suggest separate fields for numeric values and dates.  Either you have to know what data type a field is or the user has to know and use the appropriate control.  Then for numbers and dates use =, >, or < rather than LIKE as the relational operator.

Also, using wildcards in front of a text string (LIKE *aard*) will prevent the database engine from using any index.  That means it will do a full table scan.  If your table only has a few rows, you won't notice the performance hit but as it grows it will become slower so make sure you test this feature on a full-size database so you know what to expect.  If you can avoid the starting wildcard (Like aard*), most database engines can make use of indexes (I don't remember which is the case with Jet/ACE, you would have to examine query plans to determine what the engine is doing).
0
 

Author Closing Comment

by:ergenbgr
ID: 40657081
That is exactly what I needed.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40657221
glad I could help.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

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.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

885 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