Solved

Search a field chosen by the User

Posted on 2015-02-04
4
106 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
[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
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 37

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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40657221
glad I could help.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

751 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