Search as you type

[Bre-x]

My search form as a subform (Datasheet view) that its source record is a
query

I have provided the user with several text field that they are use to filter
information, when
the search button is click

I was thinking that it would be nice to add a search as you type feature.

I have add to the cust_name text field the following command, but it doesnt
work

Private Sub cust_name_Change()
Me.do_query
Me.cust_name.SetFocus
End Sub

Search Button
------------------------------------
Private Sub Command_search_Click()
On Error GoTo Err_Command_search_Click
Me.do_query
Exit_Command_search_Click:
Exit Sub
Err_Command_search_Click:
MsgBox Err.Description
Resume Exit_Command_search_Click
End Sub


My Search Function
-----------------------
Public Sub do_query()
Dim sql As String, mycust As String, mymach As String, myprog As String,
mydesc As String, mydraw As String, myrev As String
Dim myoper As String, mymat As String, mymachg As String, myemp As String

'SQL SCRIPT
Me.fsearchsub.SourceObject = "Blank"

'Customer ID
If IsNull(Me.cust_name) = True Then
mycust = "tickets.tccustid Like '*'"
Else
mycust = "tickets.tccustid Like '" & Me.cust_name & "*'"
End If

'Machine ID
If IsNull(Me.mach) = True Then
mymach = "tlines.mach Like '*'"
Else
mymach = "tlines.mach = " & Me.mach
End If

'Program ID
If IsNull(Me.prog) = True Then
myprog = "tlines.prog Like '*'"
Else
myprog = "tlines.prog = " & Me.prog
End If

'Ticket Description
If IsNull(Me.desc) = True Then
mydesc = "tickets.tcdesc Like '*'"
Else
mydesc = "tickets.tcdesc Like '*" & Me.desc & "*'"
End If

'Drawing
If IsNull(Me.drawing) = True Then
mydraw = "tlines.draw Like '*'"
Else
mydraw = "tlines.draw Like '*" & Me.drawing & "*'"
End If

'Revision
If IsNull(Me.rev) = True Then
myrev = "tlines.rev Like '*'"
Else
myrev = "tlines.rev Like '" & Me.rev & "*'"
End If

'Operation
If IsNull(Me.oper) = True Then
myoper = "tlines.oper Like '*'"
Else
myoper = "tlines.oper Like '*" & Me.oper & "*'"
End If

'Material
If IsNull(Me.mat) = True Then
mymat = "tickets.tcmat Like '*'"
Else
mymat = "tickets.tcmat Like '*" & Me.mat & "*'"
End If

'Machine Group
If IsNull(Me.machg) = True Then
mymachg = "tmach.mach_group Like '*'"
Else
mymachg = "tmach.mach_group Like '" & Me.machg & "*'"
End If


'Employee
If IsNull(Me.emp) = True Then
myemp = "tlines.tlemp Like '*'"
Else
myemp = "tlines.tlemp Like '" & Me.emp & "*'"
End If

sql = "SELECT DISTINCT tlines.tlid, tlines.tcid, tickets.tccustid,
tlines.mach, tlines.tlv, tlines.prog,
tickets.tcdesc, tlines.draw, tlines.rev, tlines.oper, tickets.tcmat,
tickets.tcnote, tickets.tcwo,
tmach.mach_group, tlines.tlemp, tlines.tldate " & _
" FROM (tlines INNER JOIN tickets ON tlines.tcid=tickets.tcid) INNER JOIN
tmach
ON tlines.mach=tmach.mach_id " & _
" WHERE " & mycust & " AND " & mymach & " AND " & myprog & " AND " & mydesc
& " AND " & mydraw & " AND " & myrev & " AND " & myoper & " AND " & mymat &
" AND " & mymachg & " AND " & myemp

'SEND SQL TO QUERRY
CurrentDb.QueryDefs("qsearch").sql = sql
Me.fsearchsub.SourceObject = "fsearchsub"
End Sub

 

[Al Campagna]

Bre-x,
An easier solution would be to make CustName
(your unbound search value) a combobox.
The query behind the combo box will deliver all the legitimate
CustName values in the recordset you are seaching.
Now, with the combo's AutoExpand set to Yes, the combo
will "drill down" into the recordset... as the user types... in an
attempt to locate the record

This accomplishes two things. It prevents initiating a search for
a value that doesn't exist in the recordset.
Performs the Auto Expand you wanted...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Bre-x]

Thanks for you help

I was thinking about that, but we are talking about at least 170,000 records

wouldn't it make it the search much slower?

Bre-x

 

[Jeff Boyce]

Yes, but ...<g>

Take a look at Allen Browne's website for his technique for handling a
combobox with "thousands of rows".

The basic approach is to have Access hold off on doing that Auto-Expand
until after a set number of characters have been entered (e.g., 3
characters, 4 characters, ...). This way, by typing in the first 'n'
characters, you've already reduced the size of the list that the combobox
needs to retrieve/display.

Regards

Jeff Boyce
Microsoft Office/Access MVP