Searching Records

I want to create a form that will have four different options to search for a particular record (the date, the record number, who submitted the original record, term.) How would I do this? How would I link the command button to a text or combo box?

Thanks.

Why don't you try using the Filter by Form toolbar button?
This would allow you to search for records based on the
and/or content of any field or combination of fields.
If you need more explanation, post back.

Just a thought.

-----Original Message-----
I want to create a form that will have four different
options to search for a particular record (the date, the
record number, who submitted the original record, term.)
How would I do this? How would I link the command button
to a text or combo box?

Thanks.
.

Could you please explain this in more detail. Thank you.

" wrote:

Why don't you try using the Filter by Form toolbar button?
This would allow you to search for records based on the
and/or content of any field or combination of fields.
If you need more explanation, post back.

Just a thought.

-----Original Message-----
I want to create a form that will have four different
options to search for a particular record (the date, the
record number, who submitted the original record, term.)
How would I do this? How would I link the command button
to a text or combo box?

Thanks.
.

Hi again, Melissa,
When you have created the form and display it in form
view, the form view toolbar at the top of the screen will
contain (among others) three filtering buttons: Filter by
Selection, Filter by Form, and Apply Filter.

If you click on the Filter by Form button, the screen will
display a view of your form that has blanks where all the
fields are. You can type criteria expressions into these
blanks. For example, you could type the record number
you're looking for in the record number field. Then click
the Apply Filter button and the record having the record
number you typed will be displayed. Similarly, you could
type *Jones* into the submitter name field, and all the
records containing Jones anywhere in that field will be
selected for display. The status bar at the bottom of the
screen will note that you are looking at a filtered set of
records.

When you are through looking at the filtered record set,
click on the Apply Filter button again to turn it off, and
all the records will be available for display again.

There are more things to think about if you use this
approach, but if you try it and like the results, then you
can experiment a bit with the other possibilities. This
technique works pretty well for those who are comfortable
with using Access tools. If you are building forms for
users who have no knowledge of Access and who aren't able
or inclined to learn a bit, then you'll probably have to
use command buttons tied to select queries to get the
results you and your users want.

Using command buttons coupled to queries is another
discussion, but I'll be glad to launch you in that
direction if you prefer.

Hope this helps (a bit).

hcj

-----Original Message-----
Could you please explain this in more detail. Thank you.

" wrote:

Why don't you try using the Filter by Form toolbar
button?
This would allow you to search for records based on the
and/or content of any field or combination of fields.
If you need more explanation, post back.

Just a thought.

-----Original Message-----
I want to create a form that will have four different
options to search for a particular record (the date,
the
record number, who submitted the original record,
term.)
How would I do this? How would I link the command
button
to a text or combo box?

Thanks.
.


.

Thanks so much for your response, it was very helpful. Unfortunately the users will be peopel not very familiar with staff so I think command buttons may be the way to go. Any help you can offer on that will be greatly appreciated. Thanks again.

" wrote:

Hi again, Melissa,
When you have created the form and display it in form
view, the form view toolbar at the top of the screen will
contain (among others) three filtering buttons: Filter by
Selection, Filter by Form, and Apply Filter.

If you click on the Filter by Form button, the screen will
display a view of your form that has blanks where all the
fields are. You can type criteria expressions into these
blanks. For example, you could type the record number
you're looking for in the record number field. Then click
the Apply Filter button and the record having the record
number you typed will be displayed. Similarly, you could
type *Jones* into the submitter name field, and all the
records containing Jones anywhere in that field will be
selected for display. The status bar at the bottom of the
screen will note that you are looking at a filtered set of
records.

When you are through looking at the filtered record set,
click on the Apply Filter button again to turn it off, and
all the records will be available for display again.

There are more things to think about if you use this
approach, but if you try it and like the results, then you
can experiment a bit with the other possibilities. This
technique works pretty well for those who are comfortable
with using Access tools. If you are building forms for
users who have no knowledge of Access and who aren't able
or inclined to learn a bit, then you'll probably have to
use command buttons tied to select queries to get the
results you and your users want.

Using command buttons coupled to queries is another
discussion, but I'll be glad to launch you in that
direction if you prefer.

Hope this helps (a bit).

hcj

-----Original Message-----
Could you please explain this in more detail. Thank you.

" wrote:

Why don't you try using the Filter by Form toolbar
button?
This would allow you to search for records based on the
and/or content of any field or combination of fields.
If you need more explanation, post back.

Just a thought.

-----Original Message-----
I want to create a form that will have four different
options to search for a particular record (the date,
the
record number, who submitted the original record,
term.)
How would I do this? How would I link the command
button
to a text or combo box?

Thanks.
.


.

Hi again,
There are a number of ways to go about this, some of
them very sophisticated, as you might gather from some of
the other discussions on this board. I like plain and
simple, so I'll outline the approach I would use, and you
can decide how to go from there.
First, create a switchboard form which will contain
command buttons to activate the search queries you'll
create below. This form is the one your users will see at
the beginning.
Next, build a set of queries, one for each search
parameter you want your users to choose from. In each of
the queries, enter a criterion statement appropriate for
the particular field the query will search on. For
example, the criterion statement for submitter might read:

Like "*" & [Enter submitter name desired] &"*"

This will alow the user to enter any part of the
submitter's name and find records containing that entry.
The queries should select all the same fields. Save these
queries with names similar to the search parameter they
use.

Next, design a form to display the output of one of the
queries. Include on the the form a Command Button which
uses Form Operations to Close the form. Save the form
with a name similar to the query it uses. Copy this form
as many times as you have queries, one form copy for each
query, naming each form copy similar to the query it will
be tied to. In design mode for each of the copies, change
the recordsource to its matching query and save the
changed design.

At this point, you could open any one of the forms and
it would activate its particular source query, asking the
user for input appropriate for that particular copy of the
form. The form would then display all records which
satisfy the user's input.

Next, back at the switchboard form, add command buttons
which will use Form Operations to open the forms you've
created. You can label the buttons with text appropriate
to the form it's tied to, or add labels beside the buttons
with that information. Each form will open according to
the selected switchboard button, the user will be prompted
for input, and the matching records will display. Because
you added a Close Form button when you designed the form,
the user can press that button to close the form and
return to the switchboard.

I realize this is a long discussion, and I hope I haven't
lost you. It's difficult to do without being able to show
each step along the way. If you're still with me, and
need explanation of parts of the above, post back with
specific questions. If I have lost you because of
unfamiliarity with Access tools, then you'll need to do
some experimenting with forms tied to queries, criteria
expressions, and so forth before returning to the problem.

On the other hand, if you're way ahead of me, then I yield
to better heads and encourage you to repost your question
looking for a more direct solution.

I'll be looking to see what's next.


-----Original Message-----
Thanks so much for your response, it was very helpful.
Unfortunately the users will be peopel not very familiar
with staff so I think command buttons may be the way to
go. Any help you can offer on that will be greatly
appreciated. Thanks again.

" wrote:

Hi again, Melissa,
When you have created the form and display it in form
view, the form view toolbar at the top of the screen
will
contain (among others) three filtering buttons: Filter
by
Selection, Filter by Form, and Apply Filter.

If you click on the Filter by Form button, the screen
will
display a view of your form that has blanks where all
the
fields are. You can type criteria expressions into
these
blanks. For example, you could type the record number
you're looking for in the record number field. Then
click
the Apply Filter button and the record having the
record
number you typed will be displayed. Similarly, you
could
type *Jones* into the submitter name field, and all the
records containing Jones anywhere in that field will be
selected for display. The status bar at the bottom of
the
screen will note that you are looking at a filtered set
of
records.

When you are through looking at the filtered record
set,
click on the Apply Filter button again to turn it off,
and
all the records will be available for display again.

There are more things to think about if you use this
approach, but if you try it and like the results, then
you
can experiment a bit with the other possibilities.
This
technique works pretty well for those who are
comfortable
with using Access tools. If you are building forms for
users who have no knowledge of Access and who aren't
able
or inclined to learn a bit, then you'll probably have
to
use command buttons tied to select queries to get the
results you and your users want.

Using command buttons coupled to queries is another
discussion, but I'll be glad to launch you in that
direction if you prefer.

Hope this helps (a bit).

hcj

-----Original Message-----
Could you please explain this in more detail. Thank
you.

" wrote:

Why don't you try using the Filter by Form toolbar
button?
This would allow you to search for records based on
the
and/or content of any field or combination of fields.
If you need more explanation, post back.

Just a thought.

-----Original Message-----
I want to create a form that will have four
different
options to search for a particular record (the date,
the
record number, who submitted the original record,
term.)
How would I do this? How would I link the command
button
to a text or combo box?

Thanks.
.


.


.

I want to create a form that will have four different options to search for a
particular record (the date, the record number, who submitted the original
record, term.) How would I do this? How would I link the command button to a
text or combo box?

Thanks.

The free downloadable sample database at www.bullschmidt.com/access uses the
query by form concept so that on the invoices dialog one can optionally choose
a rep, a customer, and perhaps a date range, click on a button that says
"Input," and then have the invoice form open up showing all the invoices that
match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open
the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e.
Nulls) in fields that are going to be used with Like in any criteria for that
field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a DefaultValue of *
and not allow the search fields to be blank.

Or a more complicated solution would be to create the query's SQL statement
dynamically so that the criteria on a particular field isn't used unless
needed.

For example:

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Not IsNull(Rep) Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Not IsNull(Customer) Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Best regards,
J. Paul Schmidt, Freelance Access and ASP Developer
www.Bullschmidt.com/Access - Sample Access Database
www.Bullschmidt.com/login.asp - Web Database Demo