A97 - Creating a Seach form

[Iain Miller]

Now this shouldn't be hard but I've been struggling on the best way as to
how to do this one for a day or 3 so I thought I'd ask the assembled
company.....

I'm writing an application that tracks a group of Sales people, the
customers they deal with and the business they transact with them.

I've got my head around all the tables & some of the basic Query structures
OK and am beginning to delve into creating the forms I need to be able to
add, edit & view information.

One of the things I need to be able to do is to find & display a record on
an individual Salesman, Customer or a single order quickly. I'm trying to
create a fairly "sealed" environment for the user so all standard toolbars &
menus will be locked off . The basic forms I have created for viewing data
are non-editable & you can't add new records from them - you need to go
elsewhere to do that. To edit you need to press an "Edit button" & this will
then isolate the single record to be edited. There will be a clear
indication when the thing is in "Edit Mode" and when its not. Some data can
not be edited by users at all. Basically I'm trying to make it fairly idiot
proof!

What I want is to create the ability for the user to search for data from
the "View" form. There are, realistically, about 4 or 5 different fields
they could search on to find a particular Salesman or Customer's record for
example. So I've thought about putting the form into filter mode or I could
use a simple dialogue to "Find" a record. Ideally what I'd like to do is to
pop a small form up that has text boxes on it to capture search criteria for
any or all of the 4 or 5 different things that a user might search on....for
example he might find a Salesman based on his name, his territory number or
his Unique reference number (which is something I have created).

So I have a small form with something like

Name: ..............
Territory No..........
URN............

The user types some text into one or more of the boxes & then hits a button
(or possibly using an "After Update Event) the thing locates the Salesman's
data, the search form closes and the relevant record is displayed on the
main "View" Form behind.

It could execute some kind of query that might produce a small set of
records that the user could scroll through or another option might be to
pull up a continuous form/list with basic details of Salesmen who part-match
the criteria entered from which the user can select one to be displayed by
clicking on a row in the list.

The user might have to hit another button on the view form to return it to
"View all Records Mode".

Any help as to how to make this work or indeed alternative & more efficient
suggestions gratefully received. On scaling, there might be several tens of
sales people, Hundreds of customers and possibly thousands of orders over
time.

All help gratefully received!

rgds

Iain

[Allen Browne]

Iain, your question is something of an onion: could be lots of layers here.
:-)

A simple interface is a form in Continuous view to display the search
results. Place the search boxes and buttons into the Form Header section.
The Search button either applies a filter to the form, or changes its
RecordSource if you need more power than a filter can give.

The basic idea looks like this:
-----------code starts--------------
Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterName) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFilterName & """)
AND "
End If

If Not IsNull(Me.txtFilterTerritory) Then
strWhere = strWhere & "([TerritoryNo] = " & Me.txtFilterTerritory &
") AND "
End If

'etc for other boxes.

'Apply the filter
lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
-----------code ends--------------

You can add as many boxes to that as you need.

Notes:
1. Use the correct delimiter for each field types: quotes for Text, # for
dates, nothing for numbers.

2. If you have a field named "Name", watch out: Forms and other objects have
a Name property, and Access may get confused about what you are talking
about.

3. Use the Like operator for text fields if desired.
... & "([Surname] Like """ & Me.txtFilterName & "*"") AND "

4. Build a complete SQL statement (like a query in SQL View), and assign to
the form's RecordSource if necessary.

5. Use subqueries as well if you need to filter on data that is in related
tables.

6. There are a couple of bugs in Access that can mess up the display of
controls in the Form Header section when the filter returns no records and
no new records can be added.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Iain Miller" <do***@spam.me> wrote in message
news:Zf*****************@newsfep4-winn.server.ntli.net...

Now this shouldn't be hard but I've been struggling on the best way as to
how to do this one for a day or 3 so I thought I'd ask the assembled
company.....

I'm writing an application that tracks a group of Sales people, the
customers they deal with and the business they transact with them.

I've got my head around all the tables & some of the basic Query structures OK and am beginning to delve into creating the forms I need to be able to
add, edit & view information.

One of the things I need to be able to do is to find & display a record on
an individual Salesman, Customer or a single order quickly. I'm trying to
create a fairly "sealed" environment for the user so all standard toolbars & menus will be locked off . The basic forms I have created for viewing data
are non-editable & you can't add new records from them - you need to go
elsewhere to do that. To edit you need to press an "Edit button" & this will then isolate the single record to be edited. There will be a clear
indication when the thing is in "Edit Mode" and when its not. Some data can not be edited by users at all. Basically I'm trying to make it fairly idiot proof!

What I want is to create the ability for the user to search for data from
the "View" form. There are, realistically, about 4 or 5 different fields
they could search on to find a particular Salesman or Customer's record for example. So I've thought about putting the form into filter mode or I could use a simple dialogue to "Find" a record. Ideally what I'd like to do is to pop a small form up that has text boxes on it to capture search criteria for any or all of the 4 or 5 different things that a user might search on....for example he might find a Salesman based on his name, his territory number or his Unique reference number (which is something I have created).

So I have a small form with something like

Name: ..............
Territory No..........
URN............

The user types some text into one or more of the boxes & then hits a button (or possibly using an "After Update Event) the thing locates the Salesman's data, the search form closes and the relevant record is displayed on the
main "View" Form behind.

It could execute some kind of query that might produce a small set of
records that the user could scroll through or another option might be to
pull up a continuous form/list with basic details of Salesmen who part-match the criteria entered from which the user can select one to be displayed by
clicking on a row in the list.

The user might have to hit another button on the view form to return it to
"View all Records Mode".

Any help as to how to make this work or indeed alternative & more efficient suggestions gratefully received. On scaling, there might be several tens of sales people, Hundreds of customers and possibly thousands of orders over
time.

All help gratefully received!

rgds

Iain

[Iain Miller]

"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:uR*********************@news-server.bigpond.net.au...

Iain, your question is something of an onion: could be lots of layers here. :-)

Allen,

thanks for your help in skinning my onion!

I.