Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Search a field chosen by the User

Posted on 2015-02-04
4
Medium Priority
?
108 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 48

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 39

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 48

Expert Comment

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

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

670 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