search

[mattbland]

hey again,

im tryin to create a search facility which will bring up a particular field in my database (very simple, i know) but i can only get it to go to a record if i specify the record no in the code. I cant get the expression to add to findrecord command. This is the command line:

DoCmd.FindRecord "search", acEntire, False, acSearchAll, True, acAll, True

the text bocx (search) is unbound.

 

[jimbrooking]

Here are a couple of suggestions:

First, the DoCmd.FindRecord will only work if the a text box bound to the field containing the search value has the focus. So immediately beforethe DoCmd.FindRecord you should

DoCmd.GoToControl "tbxSearchThisFieldInTheTableOrQuery"

Second, an alternative approach might be to apply a filter to the form. Say the text box the user enters the Search term is called tbxSearch. In tbxSearch's AfterUpdate event you could write

If Nz(Me.tbxSearch,"")="" then
Me.FilterOn = False
Else
Me.Filter = "[SearchableField] = """ & Me.tbxSearch & """"
Me.FilterOn = True
End If

You could also define the filter as

"[SearchableField] Like ""*" & Me.tbxSearch & "*"""

to find the search term anywhere in the field. (That is, searching for "dog" would find hits on fields containing "dog", "A Dog of Flanders", or "You lie like a dog!"

Jim

 

[mattbland]

hey,

got the gotocontrol bit down, and it moves to the control 'product code', but its not displaying the record i want it to (specified in the search box). Any more suggestions??

 

[jimbrooking]

Look at the Ctrl-F search dialog. There are some combo boxes that dictate how and what is searched, e.g., this field vs. all fields, or whole field vs. start of field vs. any part of field. These selections all have equivalents in the DoCmd.FindRecord call. I note that you have chosen acSearchAll, which SHOULD mean search all fields in the table. I'd change this to acCurrent to look only in the one field. Look carefully at this and the other arguments.

Personally, I find the DoCmd.FindRecord to bee too finicky, and would recommend the filter technique I suggested in the first post as being easier to write and less difficult to debug. With it you can also implement different search options like find all words, find any word, or find exact phrase, so it's a lot more robust than FindRecord.

Jim

 

[mattbland]

sorry to be an arse jim, but i took your advice and got rid of the docmd.findrecord statement and tried your filter, but the error window comes up saying that i have cancelled the previous operation, and hilites this in the code window.

Me.Filter = "[product_code] = """ & Me.txtsearch & """"

anymore help would be much appreciated!

 

[jimbrooking]

Matt,

When the error stop occurs, and you click Debug, you can move the cursor over variables in the code to see their values. What's the value of Me.txtsearch when you do this after the stop?

If your database is not huge you can zip and email it to me. I would have an easier time helping if I could see the whole thing.

Jim

 

[mattbland]

just went to zip it and e-mail to ya, and in the time it took to zip, the size has gron from 1mb-odd to 1.3Gb!!! The zip file alone is 655mb! whats goin on?????

 

[mattbland]

doesnt matter, cut n paste into new database n it went down to 588kb! wierd.

 

[jimbrooking]

Matt,

Thanks - I'll have a look.

BTW I guess I might have mentioned that you should compact the database first - click Tools>Database Utilities>Compact and Repair... That's always a good idea after a spate of mods. Access isn't good at garbage collection, and this is what MS implements to compensate.

Jim

 

[jimbrooking]

Mike - sorry, I have Access 2000 and it won't open your database. If you're using 2002/XP can you save it in an earlier version and resend? BTW the ZIP of the DB was only 49KB.

Jim

 

[mattbland]

sorry! i think winzip is set to maximum compression its gone well small, but still works. the access 2000 file is even smaller too!!

 

[jimbrooking]

Mike-

That wasn't bad. The short answer: Change the filter def to

Me.Filter = "[product_code] = " & Me.Txtsearch

You are searching a numeric field, so you don't enclose the search term in double-quotes. Other than that it seems to go OK.

The "Find" button isn't really necessary. You apply the filter after the user hits Enter, Tab, or otherwise moves the focus out of the field.

Jim

 

[mattbland]

hey jim,

the new filter works great, but is there a way to carry on moving through the fields without the filter, once it has been applied?

say if you search the db, and come up with record no.3, there would be no way you could move to record no.4, cos the filter is still in place??

the only way to do this is to empty the search box, and i think the user wont like it! sorry!

 

[Oldsoftboss]

Any reason why you couldn't use a combo box and let the wizard do the work for you

 

[mattbland]

yeh, those 4 records i have put in are just to test the db. there are about 350 extra to put in yet. cheers anyway!

ive put a FilterOn = False line in the LostFocus event of the Txtsearch box n that works fine. Thanks!!!

no, no it doesnt. crap.