How To Create A Search Facility

[Lee24]

I am trying to create a search facility for my database, where the user types in part/whole word and a list of appropriate records are returned (displayed on a form). The user can now double click on any record to be taken straight to it!


I think this a toughie! Any ideas?

Thanking You Kind People!

 

[JimHorton]

Lee,
It's actually easy in concept, but the scope may make it take a while--you said a search 'for my database'--if you have 100 tables, that's alot of flexibility. You could wrap it all in one form, using collections, ie tabledefs, fields, etc, then for each field/tabledef you'll search, use the .Type property to correctly build the sql string.

But to start with an example you can build on, let's say you have a form based on one of the tables, say tblCust (to make it flexible and have the ability to use any table--that's another thread). You have an unbound textbox for, say, CustName, at the top of the form somewhere, and below you have (continuous forms) the detail of customer records. In a button click event, you'd do:

'Below is the 'Find' way, there is also the 'Filter' way
dim rst as recordset
set rst = me.recordsetclone
rst.findfirst "CustName Like ""*" & me.txtUnboundCustName & "*"""
if rst.nomatch then
msgbox me.txtUnboundCustName & "Not found"
else
me.bookmark = rst.bookmark 'set the form to select the first match
end if

Now, theres so much to build upon there, for example:
Scope the recordset at Form level, set it to the clone on load.
Then instead of findfirst, you can use findnext (especially with Like), this way he can keep hitting the button to get to the next match, but in the NoMatch block, you can put:

rst.FindNext "Custname Like ""*" & blah blah & "*"""
if rst.NoMatch
If rst.NoMatch 'for FindNext, this allows you to go back to the top to start over
rst.movefirst
rst.findFirst "Custname Like ""*" & blah blah & "*"""
if rst.nomatch then
'msgbox
else
me.bookmark = rst.bookmark
end if
else
me.bookmark ...........

The filter way you would instead do (In the button click event):

me.filter = "CustName Like ""*" & me.txtUnboundcustname & "#"""
me.filteron = true

Then start adding other fields to search on, changing the Like to =, and so on, but this should give you a good starting block
--Jim

 

[Lee24]

JIM YOU DON'T HAVE A WORKING EXAMPLE DO YOU?

I WILL TRY THIS CODE FIRST ANYWAY!