Solved

Search a field chosen by the User

Posted on 2015-02-04
4
103 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 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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now