Re: Making A Search Form Like Albert Kallal's Form

From: Jeff Conrad (jeffc_at_ernstbrothers.com)
Date: 04/25/04 

Date: Sun, 25 Apr 2004 11:27:51 -0700

Hi Albert,

Comments below.

>Yes, the combo box has additional fields.
>
>They are:
>
>Last Name, or card# (by the way..this also searches for
>company name..but I did have room for the text!)
>Look For any Phone number (cell, phone, fax fields are
>searched)
>Email Address

I suspected there were more options; just wanted to check.
I followed this technique and have five search options in
the combo box. The default is set to Vendor Name since I
have found that is how most of the invoice searches are
done in this application.

>I still have all the fields displayed. For consistent
>through out the application, users generally ONLY need
>that combo box if they want to look for a email
>address...or a phone number (so, I am VERY consistent
>throughout the application when I have that combo box).

Understood. I'm also working through the entire
application trying to make a consistent layout. Lots of
work!

>You can see some more screen shots of that combo box idea
>here:
>
>http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

Yep, I was looking at those as well.

>However, I have actually designed a few search forms
>where I do change the fields displayed. Since the display
>area is a sub-form, you can easily change what the sub-
>form displays.
>
>Me.tblMainClient_subform.SourceObject = "frmPopSearch
SubForm"

I've been experimenting with that as well.

>I only show the results when they hit enter. (a bit too
>much processing to try and fill things as they type)..
>
>Further..I don't find any improving from a UI point of
>view by having the displayed show as they time anyway.
>Often, users type quite fast..and make a few
>mistakes...whack back space a few times..then they hit
>enter. Once they type in smith and hit enter...then do
>see all the smith names. If you want Joe Smith..then type
>in Joe (since we are now in the first name
>field...hitting enter after we type in Joe throws the
>cursor into the grid). If the user does NOT like what
>they see..ESC key clears all, and puts the
>cursor right back to the starting field (LastName).
>
>So, often, they type in the last name..or enough to
>match...the display shows only one name..so they whack
>enter key (which is now sitting in the firstname
>field)..and the cursor jumps into the gird...whacking
>enter again...is the same as hitting the details button
>(so, often..you will see the users type in a last
>name...then in a smooth motion..they realize that
>they got the right name...so Enter is hit 2
>times......tap tap...and you are now viewing the name.

Makes sense to me. The reason I was asking is because we
use a professional VB program at our restaurant locations
for invoice/purchase/food cost analysis. All the data is
stored in Access/Jet tables. On several of their search
forms they use that technique of changing the screen as
you are typing. I've found this to be "OK" some of the
time, but not all the time. Just like you said, I can
easily make a typo mistake and if I happen to hit Enter,
suddenly I'm taking to somewhere I don't want to be! There
is also a whole lot of screen flickering going on!

I was leaning towards just doing the search on the
AfterUpdate of the text box like you propose, but your
discussion has definitely set my mind on that option now.

>Well, if the combo box shows "Search by Vender...you
>might as well use the bottom half of the screen to
>display two calendars for the start, and end
>date. (if they don't use a date range very much...then
>make a date range check box...and when checked..display
>two large calendars in place of the sub-form results.

Already have finished that part. Spent lots of time
yesterday on getting the form to look just how I want. I
have text boxes in the upper right portion of the screen
to search by a specific day and/or date range.

>Yes...just build up the criteria as you go along.

I'm playing with that option right now. And yes, I'm
building up the SQL string in code and changing the
RecordSource of the subform. Debug.Print and I are
becoming the best of friends now!

>For example..I have a few options like only searching for
>the current season. I also have options for soundex
>search (fuzzy name). So, yea..you
>just build up the where as you go along.

I'm still a little confused by the fuzzy name search
thing. Is it something like searching for any combination
of letters/numbers? So, for example, I set the SQL Like
criteria to find all invoice numbers that start with the
digits 54. But I could also instead search for the
combination of 54 in ANY part of the invoice number? Is
that what you're getting at? I do know how to change the
Like clause for this, but I just wanted to make sure I
understood you.

Thanks for your thoughts/opinions/ideas on this. It has
really helped with my new improved search form. I'm very
pleased with the layout and will play with this some more
today. I had my wife test it last night and she was quite
pleased with the expanded search capabilities.

Thanks again Albert, 

--
Jeff Conrad
Access Junkie
Bend, Oregon