A2K: possibilities of adding snazzy search functions?

Perhaps this has been asked before but there might be some up to date
thinking about this.

I really need a better search function for my asset register. I allow
assets to be entered and tracked over a period of time. There can be quite
similar assets and simply choosing from the combo box is not enough. I need
something more sophisticated.

How hard is it to get a listing that grows as I type in a search term,
character by character? Is this something I could only get by buying a
third-party control or is it doable in Access?

As the list is populated the user would be able to click on any result (or
first scroll the listbox to see further results) which then goes immediately
to that record.

ta

re: A2K: possibilities of adding snazzy search functions?

You just need a standard combobox with its *autofill* feature to do this.
First you must have your form set up to display the record you want to
retrieve, i.e. you must have controls set up with the appropriate Control
Sources to bind them to the fields you want displayed. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up
Select "Find a record based on the value I selected in my combobox."
Hit Next.
From the same query as the form is based on, click on the field you're
searching by to move it to the right side.
Hit Next.
Size the column appropriately.
Hit Next.
Name the combobox.
Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type.
Hit <Enterand the record will be retrieved.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via http://www.accessmonster.com

re: A2K: possibilities of adding snazzy search functions?

"Deano" <deano@mailinator.comschreef in bericht news:5dnc1uF35vl1uU1@mid.individual.net...I don't know what you mean by 'snazzy', but recently I made a 'custom' search form for a client.
This is a search-form for students where the client can use 6 items to search on.
e.g there are controls for name, address, city, class and such.
When the client types in a search-string in one or more of the search-controls the list of students is *not* growing, but the list gets shorter with each character that is typed. The search-string can be any string in a search-control, or any combination in all the search-controls, (does not need to start at the beginning)
The search-form filters immediately. The Students are found very fast and easy that way.
A click on a specific record closes the search-form, and opens the Students-form with the record of that student.

If this is what you need, then let me know. I will explain further.

Arno R

re: A2K: possibilities of adding snazzy search functions?

"Arno R" <arracomn_o_s_p_a_m@planet.nlwrote in message
news:4676950f$0$25490$ba620dc5@text.nova.planet.nl ...

"Deano" <deano@mailinator.comschreef in bericht
news:5dnc1uF35vl1uU1@mid.individual.net...quiteneedimmediatelysearch form for a client.search on.search-controls the list of students is *not* growing, but the >>list >gets
shorter with each character that is typed. The search-string can be any
string in a search-control, or any >>combination in all the search-controls,
(does not need to start at the beginning)easy that way.Students-form with the record of that student.
That sounds pretty much what I'm trying to accomplish. It doesn't matter if
the list is being reduced or expanded as long as I end up with a match or
set of matches. With my data it would probably just match one.

Let me know more!

re: A2K: possibilities of adding snazzy search functions?

"missinglinq via AccessMonster.com" <u28780@uwewrote in message
news:73e25e2ddeab6@uwe...by,youAh! Yes, I know what you're saying there and I do have that but;

1 - it's not as clear to the user as I would like, putting in a new search
would enable possibilities to be filtered
2 - i would like something snazzier/cooler which if not overdone should help
with usability

re: A2K: possibilities of adding snazzy search functions?

"Deano" <deano@mailinator.comschreef in bericht news:5dnjprF356fo1U1@mid.individual.net...OK, I wil give an example with only two search-controls. You can adapt or expand that idea.
We are going to search Students here on Surname and City.

We need a Mainform (FrmSearch) with the two search-controls (strCity and strSurname, unbound)

We need a subform that will show the results.
Subform (Subform and control is called FrmSearchSub) might have an initial recordsource like:
Select * FROM TblStudents WHERE
Surname = Like "*" & Forms!FrmSearch!strSurname & "*" AND
City=Like "*" & Forms!FrmSearch!strCity & "*"
==The form will give you all the students when opened.
Make sure this works !!

You will then need to change the recordsource for the subform while typing in a search-string in a search-control like:

Private Sub strSurName_Enter()
Dim strSQL as string
strSQL="Select * FROM TblStudents WHERE"
strSQL=strSQL & " Surname = Like "*" & Forms!FrmSearch!strSurname.Text & "*" AND
strSQL=strSQL & " City=Like "*" & Forms!FrmSearch!strCity & "*"
Me.FrmSearchSub.Form.RecordSource = strSQL
End Sub

==The main point here is that we need to use the .Text property of the search-control in the recordsource.

Similar for strCity:
Private Sub strCity_Enter()
Dim strSQL as string
strSQL="Select * FROM TblStudents WHERE "
strSQL=strSQL & " Surname = Like "*" & Forms!FrmSearch!strSurname & "*" AND
strSQL=strSQL & " City=Like "*" & Forms!FrmSearch!strCity.Text & "*"
Me.FrmSearchSub.Form.RecordSource = strSQL
End Sub

You need a bit more code for the search-controls:
Private Sub strSurName_Change()
Me!FrmSearchSub.Requery
End Sub

Also for the second control:
Private Sub strCity_Change()
Me!FrmSearchSub.Requery
End Sub

==The requery will force the subform to show a limited set of records for each character that you type.

Suppose your Student is found, (or your Student is one of the xxx left...)
Now your sub can easily be coded to open the form FrmStudents with the record you want:
Let's suppose your PK is StudentNr.

Private Sub StudentNr_Click()
Dim lngStudent As Long
lngStudent = Me!StudentNr
DoCmd.Close acForm, Me.Parent.Name 'Closing the searchform will also close the subform
DoCmd.OpenForm "FrmStudents", , , "StudentNr = " & lngStudent
End Sub

I hope this makes sense to you.
Also hope that I did not make too much typo's here (translated the code somewhat) ;-)

Arno R