Solved

Search a field chosen by the User

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

744 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

12 Experts available now in Live!

Get 1:1 Help Now