Creating a master search feature

  • Thread starter Thread starter Curis
  • Start date Start date
C

Curis

I am currently developing a database to keep track of records requests and
all of the pertinent information for each request. For the purposes of this
question, I do not believe that the structure of the tables is important;
however, I will happily post them if requested.

Basically, what I want to do (or, more accurately, what the users want to
do) is to allow the users to search on any value across all fields of
multiple tables.

For example, I have tables called:

tblSubpInfo
tblPartyInfo
tblPartyAddresses
tblPartyPhones
tblSubpMemos
tblSubpMedicalInfo

.... and many more similarly designed. Nearly all of the tables are related
to at least one other.

I will eventually have a main entry form called frmSubpInfo that will
essentially pull all of the most important data together and allow the users
to view/edit less important data via additional forms.

What the users want is the ability to use a search feature that would
operate identically to the Find command from the Edit menu, but I am not
about to bind the frmSubpInfo form to a gigantic “master†query in order for
the Find command to work with that form. One main issue to consider is that
the nature of these records requests requires the use of memo fields to
accommodate the amount of text entered (these memos are what the tblSubpMemos
table stores), and these memo fields would frequently need to be searched
against to find records. As such, the speed of the operation will play a
factor.

I have thought about creating a small unbound form, perhaps called
frmMasterSearch, that would, through VBA, create master QueryDef using a
single parameter as the criteria for all of the searched fields, and then
using the record navigation at form in conjunction with its OnCurrent event
to syncronize the frmSubpInfo form to the found record(s).

I am just not sure if that is the best method, or what other method would be
the best approach. I need to balance the flexibility that they want with the
feasibility of such a search, and the speed with which that search can be
performed.

I hope that I explained the issue clearly enough, and I apologize for the
length of the post.

I look forward to any suggestions.

Thanks in advance,
Jeff
 
Build a form for the search. It is relatively easy to put a command button on
the form to do a search. If you have to do some subforms the search function
will work there as well. Then you could write some basic code to open each
form based on the search. Hopes this helps any.
 
When you say "build a form for the search," do you mean for me to build one
similar to what I previously described? Or should I go about it differently?

I am probably making this more complicated than it needs to be (I tend to do
that frequently, but the end result has always been that I learn more). I
just feel like there has to be an easier way that the method I currently have
in my head.

Thanks,
Jeff
 
I think you either need to build a form just for the search function and once
the user selects the desired info it takes them directly to that form (like
with an onexit event) or maybe more easy would be to have a search function
command button on the mainform that takes the user to form they need.
 
Thanks for the help so far. I do have additional questions, though.

How would you suggest writing the SQL string in order to search for one
value across all of the fields in a table/query? Am I on the right path with
my thought of creating a "master" QueryDef in VBA using a single Parameter as
the WHERE Criteria for each field? Or should I simply create a "master
query" using the QBE, and then somehow search it for a value.

Were I simply searching one field at a time, it would be easy enough. I am
confused on how to effectively search multiple/all field in a table/query
simultaneously, though.

I consider myself intermediate-level when it comes to Access, and understand
fully that as much as I know, I probably know less than half of what Access
is capable of. This issue may very well be an illustration of that. For all
I know, there is a very easy way of accomplishing this that I simply do not
know.

-Jeff
 
Back
Top