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

From: Albert D. Kallal (kallal_at_msn.com)
Date: 04/25/04 

Date: Sun, 25 Apr 2004 01:18:41 -0600

"Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
news:3cf701c42a42$f230a960$a301280a@phx.gbl...

> fine, but I wish to improve the searching abilities. I'm
> wanting something similar to Albert Kallal's search form
> example here:
>
> http://www.attcanada.net/~kallal.msn/Search/index.html
>
> I've set up the continuous form which displays the vendor
> name, invoice date, invoice number, and invoice amount.
>
> So questions for Albert if he reads this:
> What are the other options available in the combo box? The
> screen shot displays "Last name, or Card# to search for."
>
> Do you have other search fields listed in that combo box?
> So, for example, another option is to search by company
> name.

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

> They type in a few letters and the form only shows
> company names with those letters. If I'm correct, do you
> still have the First Name search field visible or do you
> hide that if they select a different search criteria?

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 through
out the application when I have that combo box).

You can see some more screen shots of that combo box idea here:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

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 think what I'd like to do is have someone be able to
> search by vendor, date, invoice number, and maybe even by
> the amount. To do so I assume I will use a query criteria
> with the LIKE operator. Correct? Does the continuous form
> change with each keystroke by the user or only when they
> press Enter?

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 se
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.

> Should I allow someone to search by vendor and then
> further narrow the search by a date range?

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.

> Can I just
> build up the query criteria with more than one search
> criteria?

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

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.

   bolSoundex = (Me.chkSoundex = True)
   buildsearch = False
   GotOne = False

   myconds = ""
   If Me.optSearch = 2 Then
      ' search all history...no restrtions on names returned
      myconds = ""
   Else
      ' restrict to just this season (this is the default)
      myconds = " ( (TakenCreate >= " &
qudate(gblrecRides!StartSeason.Value) & _
                " and TakenCreate <= " & qudate(gblrecRides!EndSeason.Value)
& _
                ") or (FromDate >= " & qudate(gblrecRides!StartSeason.Value)
& ") )"
   End If

      Case "LastName" ' standard search

         If Len(txtSLastName) > 0 Then
            If myconds <> "" Then myconds = myconds & " and"
            If IsNumeric(txtSLastName) = True Then
               ' number for last name...lets assume this is a invoice number
               '
               myconds = " tblBooking.InvoiceNumber = " & txtSLastName
            Else
               myconds = myconds & "( tblMainClient.LastName like " &
quS(txtSLastName)
               myconds = myconds & " or tblMainClient.Company like " &
quS(txtSLastName) & ") "
               myorder = " order by tblMainClient.LastName,
tblMainClient.FirstName, FromDate"
            End If
         End If

      Case "Phone" ' search for any phone number

         txtSLastName = RidesPhone(Nz(txtSLastName, ""))
         If Len(Nz(txtSLastName, "")) > 0 Then
            If myconds <> "" Then myconds = myconds & " and "
            myconds = myconds & "(WorkPhone like " & quS(txtSLastName) & "
or " & _
                                "HomePhone like " & quS(txtSLastName) & " or
" & _
                                "Cell like " & quS(txtSLastName) & " or " &
_
                                "Fax like " & quS(txtSLastName) & ")"
            myorder = " order by LastName, FirstName, FromDate"
         End If

etc for as many options as you like.

Eventualy you get:

   If myconds <> "" Then

      MySql = CurrentDb.QueryDefs(strSearchQuery).SQL

      ' get rid of the ";"

      MySql = Left(MySql, InStr(MySql, ";") - 1)

      MySql = MySql & " where " & myconds
      MySql = MySql & myorder

      Me.tblMainClient_subform.Form.RecordSource = MySql

      If Me.tblMainClient_subform.Form.RecordsetClone.RecordCount > 0 Then
         Me.tblMainClient_subform.Visible = True
         'Me.tblMainClient_subform.SetFocus
         buildsearch = True
         GotOne = True
      End If
   End If

In some cases...I actually pull the field name to search from the combo
box....(numbers..or text). And, if done right..the code can use the field
name from the combo box (it would be hidden from the user). However, since a
good number of the searches had to use multiple fields..then I could not in
this case simply pull the field name from a hidden column in the combo box.
However, you can see in the above code that the combo box is based on a
table where I return the field name...but display prompt text... 

--
Albert D. Kallal        (MVP)
Edmonton, Alberta Canada
pleasenonoSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn
Loading