Making A Search Form Like Albert Kallal's Form

[Jeff Conrad]

Hi,

Using Access 97, but this will work for later versions as
well.

I'm wanting to improve a search form in my application.
Right now they just select a vendor from a combo box and
all invoices for that vendor are displayed in a list box.
They can either double click on the list box entry or
click a command button to edit the invoice. It all works
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. 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 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?

Should I allow someone to search by vendor and then
further narrow the search by a date range? Can I just
build up the query criteria with more than one search
criteria?

Thanks for any opinions,

 

[Albert D. Kallal]

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

 

[Jeff Conrad]

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,

 

[Albert D. Kallal]

I'm still a little confused by the fuzzy name search
thing. Is it something like searching for any combination
of letters/numbers?

No, soundex has been around in the computing business for years and years.

soundex means search for names that SOUND the same.

So, if I search for my last name of kallal, then

kahlah etc, (just bout any spelling I type in that "sounds" the same..and
starts with k will show as a match. So, often, when we can't find a
name..then fuzzy name serach is used (but that is just a cool buzz word I
came up with...and it is actaully just plain old soundex).

For reasons of performance, I did want those soundex values to be indexed.
So, the solutin is in the after update event of the LastName field, you have
to stuff a soundex version of the name into another field LastNameSDX. The
same is done for first name.

You can use "like" on a field..and the indexing works but ONLY this works:

LastName like "kal*"

if you go:

LastName like "*kal*"

Then NO index is used. That is way too much of a performance hit. I would
never allow that on a customer file. You simply can't afford to do
sequential scans..as it will cause too much network traffic.

Anyway, there is lots of soundex routines around. Do a search on the web.

I used the following...the code is not as clean as it could be..since I
converted it from some Pascal routines that I wrote for my AppleII.


Function strSoundex(strSource As Variant) As Variant

'
' Produces a code based on the "Soundex" method
'
'
' Parms:
' strSource Passed string
'
'
' Written by Albert D. Kallal
'
'
' See page 392 of Knuths' book 'Sorting and Searching', Volume 3 of 'The
' Art of Computer Programming", Addison/Wesley publisher.
'
' Method used:
' 1. Change all lowercase to uppercase
' 2. Retain first letter of input string (must be alpha)
' 3. Ignore the letters A, E, H, I, O, U, W, Y, and any other
' non-alphabetic characters:
' 4. Subsitiute the following
' 1 = B F P V
' 2 = C G J K Q S X Z
' 3 = D T
' 4 = L
' 5 = M N
' 6 = R
' 5. Ignore identical letters next to each other
' 6. Add trailing zeros if the length is less than "max.soundlen"
' characters (in this example it is 4 numeric + 1 alpha = 5).
' Stop when the string reaches max.soundlen (ie:truncate the rest)
'
'START:--------------------------------------------------------------------
'
If IsNull(strSource) = True Then
strSoundex = Null
Exit Function
End If

Const maxsoundlen As Integer = 4 ' Max length of resulting soundex
code
Dim TransTable As String
Dim Offset As Integer ' Offset for easy TABLE translates
Dim SourceLen As Integer
Dim charptr As Integer
Dim testchar As String
Dim lastchar As String
Dim intLookup As Integer
Dim strDigit As String

' Conversion table
' [ABCDEFGHIJKLMNOPQRSTUVWXYZ]
TransTable = "01230120022455012623010202"
Offset = Asc("A") - 1 ' Offset for easy TABLE translates
'
' note: for "AEHIOUWY" are ignored by translating to zero
'
strSource = UCase(strSource) ' convert string to uppercase

SourceLen = Len(strSource) ' find/set string length to process

strSoundex = Left$(strSource, 1) ' Get/set first character
lastchar = strSoundex
charptr = 2 ' We skiped the first char above

Do While (charptr <= SourceLen) And (Len(strSoundex) < maxsoundlen)
'
testchar = Mid$(strSource, charptr, 1) ' get 1 char to test
'
' if different than last character, then process
'
If testchar <> lastchar Then
'
intLookup = Asc(testchar) - Offset
If (intLookup > 0) And (intLookup <= 26) Then
strDigit = Mid$(TransTable, intLookup, 1) ' table translate to
soundex
If strDigit <> "0" Then
strSoundex = strSoundex & strDigit
lastchar = testchar
End If
End If
End If
'
charptr = charptr + 1 ' move on to next character
Loop

'strSoundex = Left(strSoundex & "00000", maxsoundlen) ' pad with
trailing zeros (5 CHARS)

End Function

 

[Jeff Conrad]

Hi Albert,

No, soundex has been around in the computing business for
years and years.

soundex means search for names that SOUND the same.

So, if I search for my last name of kallal, then

kahlah etc, (just bout any spelling I type in
that "sounds" the same..and starts with k will show as a
match. So, often, when we can't find a name..then fuzzy
name serach is used (but that is just a cool buzz word I
came up with...and it is actaully just plain old soundex).

Ahhhh, I see now.
That makes more sense.

For reasons of performance, I did want those soundex
values to be indexed. So, the solutin is in the after
update event of the LastName field, you have
to stuff a soundex version of the name into another field
LastNameSDX. The same is done for first name.

You can use "like" on a field..and the indexing works but
ONLY this works:

LastName like "kal*"

if you go:

LastName like "*kal*"

Then NO index is used. That is way too much of a
performance hit. I would never allow that on a customer
file. You simply can't afford to do sequential scans..as
it will cause too much network traffic.

Ok, I gotcha. I was experimenting with the different Like
Operator syntaxes so this makes sense to me.

Anyway, there is lots of soundex routines around. Do a
search on the web.

I used the following...the code is not as clean as it
could be..since I converted it from some Pascal routines
that I wrote for my AppleII.

[Code Snipped]

Hummm, can't make heads or tails of that, but I'll trust
that it works! <g>
I'll try and play around with it.

Thanks again for your help and ideas Albert.