Trouble with a multiple parameter search

Hello,

I'm very new to Access, and have been struggling to get what seems to
me a very simple database to work correctly.

I've got just one table, and each record contains only nine fields
(including the primary key). The fields are all text, except for one
that is memo.

What I'd like to be able to do is build a form with input boxes for
seven of the fields that will launch a multiple parameter query. I'll
never be putting search terms into all of the fields on the form, but
I'll often be using different fields for different searches.

Seems like a fairly straightforward project. But I'm having trouble
with the query. Even without using the form, it will only return proper
results on data entered into the first two parameters. No results are
returned for anything that I enter into the last five parameters
(although i do get proper returns when i build simple single-parameter
queries for each of the fields).

So ... here's the SQL I've got. From all the SQL stuff i've been poring
over, I think I'm using AND correctly, but Access obviously doesn't
think so.

SELECT [Bridge Transcripts].TP, [Bridge Transcripts].TC, [Bridge
Transcripts].SPKR, [Bridge Transcripts].TRANSCRIPT, [Bridge
Transcripts].STARS, [Bridge Transcripts].WITH, [Bridge
Transcripts].KEYWORDS, [Bridge Transcripts].LOC, [Bridge
Transcripts].ID
FROM [Bridge Transcripts]

WHERE ([Bridge Transcripts].[TP] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![TP] Is Null)
AND ([Bridge Transcripts].[SPKR] = [Forms]![BTF]![SPKR] Or
[Forms]![BTF]![SPKR] Is Null)
AND ([Bridge Transcripts].[TRANSCRIPT] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![TRANSCRIPT] Is Null)
AND ([Bridge Transcripts].[STARS] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![STARS] Is Null)
AND ([Bridge Transcripts].[WITH] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![WITH] Is Null)
AND ([Bridge Transcripts].[KEYWORDS]= [Forms]![BTF]![TP] Or
[Forms]![BTF]![KEYWORDS] Is Null)
AND ([Bridge Transcripts].[LOC] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![LOC] Is Null);



Any help greatly appreciated.

-Mary

Hi Mary

What you are trying to do makes perfect sense, but I think it might be
easier to take a little step further and turn this into a search form. This
involves a bit of code, but is much simpler, more flexible, and more
efficient in the long run. For example, if you only used 2 of the criteria
boxes, the code will produce a SQL statement that only has 2 phrases in the
WHERE clause instead of 14 like the one you posted.

I suggest a continuous form where the search results are shown one per row.
The criteria boxes go in the Form Header section (View menu in form design),
along with a command button to execute the search. The search button builds
the SQL statement from the non-blank boxes, and applies it as the
RecordSource of the search form, so that it shows only matches as its
results.

The example below assumes the search criteria text boxes are named
txtFindTP, txtFindSPKR, etc. The code concatenates each non-null search
value into the string, tacking " AND " on the end ready for the next one.
The final trailing " AND " is then chopped off, and the string is added to
the stub of the SQL statement. (Note that the constant is one long line,
starting with SELECT and ending with the FROM clause."

Private Sub cmdSearch_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT [Bridge Transcripts].TP, [Bridge
Transcripts].TC, [Bridge Transcripts].SPKR, [Bridge Transcripts].TRANSCRIPT,
[Bridge Transcripts].STARS, [Bridge Transcripts].WITH, [Bridge
Transcripts].KEYWORDS, [Bridge Transcripts].LOC, [Bridge Transcripts].ID
FROM [Bridge Transcripts]"

'Build the WHERE clause from the non-Null boxes.
If Not IsNull(Me.txtFindTP) Then
strSql = strSql & "(TP = """ & Me.txtFindTP & """) AND "
End If

If Not IsNull(Me.txtFindSpkr) Then
strSql = strSql & "(SPKR = """ & Me.txtFindSPKR & ") AND "
End If

'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
oups.com...

I'm very new to Access, and have been struggling to get what seems to
me a very simple database to work correctly.

I've got just one table, and each record contains only nine fields
(including the primary key). The fields are all text, except for one
that is memo.

What I'd like to be able to do is build a form with input boxes for
seven of the fields that will launch a multiple parameter query. I'll
never be putting search terms into all of the fields on the form, but
I'll often be using different fields for different searches.

Seems like a fairly straightforward project. But I'm having trouble
with the query. Even without using the form, it will only return proper
results on data entered into the first two parameters. No results are
returned for anything that I enter into the last five parameters
(although i do get proper returns when i build simple single-parameter
queries for each of the fields).

So ... here's the SQL I've got. From all the SQL stuff i've been poring
over, I think I'm using AND correctly, but Access obviously doesn't
think so.

SELECT [Bridge Transcripts].TP, [Bridge Transcripts].TC, [Bridge
Transcripts].SPKR, [Bridge Transcripts].TRANSCRIPT, [Bridge
Transcripts].STARS, [Bridge Transcripts].WITH, [Bridge
Transcripts].KEYWORDS, [Bridge Transcripts].LOC, [Bridge
Transcripts].ID
FROM [Bridge Transcripts]

WHERE ([Bridge Transcripts].[TP] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![TP] Is Null)
AND ([Bridge Transcripts].[SPKR] = [Forms]![BTF]![SPKR] Or
[Forms]![BTF]![SPKR] Is Null)
AND ([Bridge Transcripts].[TRANSCRIPT] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![TRANSCRIPT] Is Null)
AND ([Bridge Transcripts].[STARS] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![STARS] Is Null)
AND ([Bridge Transcripts].[WITH] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![WITH] Is Null)
AND ([Bridge Transcripts].[KEYWORDS]= [Forms]![BTF]![TP] Or
[Forms]![BTF]![KEYWORDS] Is Null)
AND ([Bridge Transcripts].[LOC] = [Forms]![BTF]![TP] Or
[Forms]![BTF]![LOC] Is Null);

Any help greatly appreciated.

-Mary

Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure out
queries and am very unsure about how forms and reports work. In fact,
I'm not sure how, in your method, i'm now supposed to see the results.
the form itself tells me the number of hits that each search garners,
but the results themselves a're not actually displayed anywhere. any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will use it
to log the footage he's shot for a documentary about bridge players. he
was in sydney in august and says that the game is very popular down
there. do you play?

-mary

The idea was to use a continuous form to view the results. You can use the
Wizard to create a Tabular form, or you can do it yourself so as to
understand what's going on:

1. Create a new form, initially choosing your table as the RecordSource.

2. In form design view, open the Properties box (View menu.) Make sure its
title reads Form so you are looking at the properties of the Form, not those
of a text box.

3. On the Format tab of the Properties box, set Default View to Continuous
Form.

4. If you do not see Form Header above the Detail section, click Form
Header/Footer on the View menu.

5. Add the fields you want to see as search results, by dragging them from
the Field List onto the Detail section of your form. (Field List is on the
View menu if you don't see that dialog.)

6. Arrange the text boxes so they are side by side. You probably want to cut
the attached labels from the text boxes (select label, and Ctrl+X), paste
them into the form header, and move them above each column.

7. Now that the text boxes are all in one row horizontally, make the Detail
section as small as possible vertically (typically the height of one text
box.)

8. Test the form: it should show many rows at once.

Once you have that working, you can add the search boxes and command button
to the Form Header section, typically above the column labels. When you
click the command button, the form is effectively filtered to just the
records that match the search criteria.

If there are lots of other fields that cannot all be shown in the search
for, you might then like to add some code to the DblClick event of one of
the text boxes, so you can double-click the control to open another form for
editing that record. For example if the primary key is an AutoNumber field
named ID, you would end up with an event procedure like this:
Private Sub TP_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.ID) Then
strWhere = "[ID] = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure out
queries and am very unsure about how forms and reports work. In fact,
I'm not sure how, in your method, i'm now supposed to see the results.
the form itself tells me the number of hits that each search garners,
but the results themselves a're not actually displayed anywhere. any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will use it
to log the footage he's shot for a documentary about bridge players. he
was in sydney in august and says that the game is very popular down
there. do you play?

-mary

Hi, just pinching in with an additional question (first of all thanks for the
extended and helpfull info). How to do include a like question on this ?
I use it with names etc... so instead of having to write Benjamin that I
just write Be and that it shows me Ben, Benjamin, Bernard, Bert... etc...

Thanks

--
Novice


"Allen Browne" wrote:

The idea was to use a continuous form to view the results. You can use the
Wizard to create a Tabular form, or you can do it yourself so as to
understand what's going on:

1. Create a new form, initially choosing your table as the RecordSource.

2. In form design view, open the Properties box (View menu.) Make sure its
title reads Form so you are looking at the properties of the Form, not those
of a text box.

3. On the Format tab of the Properties box, set Default View to Continuous
Form.

4. If you do not see Form Header above the Detail section, click Form
Header/Footer on the View menu.

5. Add the fields you want to see as search results, by dragging them from
the Field List onto the Detail section of your form. (Field List is on the
View menu if you don't see that dialog.)

6. Arrange the text boxes so they are side by side. You probably want to cut
the attached labels from the text boxes (select label, and Ctrl+X), paste
them into the form header, and move them above each column.

7. Now that the text boxes are all in one row horizontally, make the Detail
section as small as possible vertically (typically the height of one text
box.)

8. Test the form: it should show many rows at once.

Once you have that working, you can add the search boxes and command button
to the Form Header section, typically above the column labels. When you
click the command button, the form is effectively filtered to just the
records that match the search criteria.

If there are lots of other fields that cannot all be shown in the search
for, you might then like to add some code to the DblClick event of one of
the text boxes, so you can double-click the control to open another form for
editing that record. For example if the primary key is an AutoNumber field
named ID, you would end up with an event procedure like this:
Private Sub TP_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.ID) Then
strWhere = "[ID] = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure out
queries and am very unsure about how forms and reports work. In fact,
I'm not sure how, in your method, i'm now supposed to see the results.
the form itself tells me the number of hits that each search garners,
but the results themselves a're not actually displayed anywhere. any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will use it
to log the footage he's shot for a documentary about bridge players. he
was in sydney in august and says that the game is very popular down
there. do you play?

-mary

Use the Like operator, with the star as a wildcard.

I assume this is being applied to a Text field, so it will need the extra
quotes as delimiters. Example:
strWhere = "[FirstName] Like """ & Me.[txtFindFirstName] & "*"""
or, if you want to find the name anywhere in the FirstName field:
strWhere = "[FirstName] Like ""*" & Me.[txtFindFirstName] & "*"""

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ben" wrote in message
...
Hi, just pinching in with an additional question (first of all thanks for
the
extended and helpfull info). How to do include a like question on this ?
I use it with names etc... so instead of having to write Benjamin that I
just write Be and that it shows me Ben, Benjamin, Bernard, Bert... etc...

Thanks

--
Novice


"Allen Browne" wrote:

The idea was to use a continuous form to view the results. You can use
the
Wizard to create a Tabular form, or you can do it yourself so as to
understand what's going on:

1. Create a new form, initially choosing your table as the RecordSource.

2. In form design view, open the Properties box (View menu.) Make sure
its
title reads Form so you are looking at the properties of the Form, not
those
of a text box.

3. On the Format tab of the Properties box, set Default View to
Continuous
Form.

4. If you do not see Form Header above the Detail section, click Form
Header/Footer on the View menu.

5. Add the fields you want to see as search results, by dragging them
from
the Field List onto the Detail section of your form. (Field List is on
the
View menu if you don't see that dialog.)

6. Arrange the text boxes so they are side by side. You probably want to
cut
the attached labels from the text boxes (select label, and Ctrl+X), paste
them into the form header, and move them above each column.

7. Now that the text boxes are all in one row horizontally, make the
Detail
section as small as possible vertically (typically the height of one text
box.)

8. Test the form: it should show many rows at once.

Once you have that working, you can add the search boxes and command
button
to the Form Header section, typically above the column labels. When you
click the command button, the form is effectively filtered to just the
records that match the search criteria.

If there are lots of other fields that cannot all be shown in the search
for, you might then like to add some code to the DblClick event of one of
the text boxes, so you can double-click the control to open another form
for
editing that record. For example if the primary key is an AutoNumber
field
named ID, you would end up with an event procedure like this:
Private Sub TP_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.ID) Then
strWhere = "[ID] = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If
End Sub


wrote in message
ups.com...
Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure out
queries and am very unsure about how forms and reports work. In fact,
I'm not sure how, in your method, i'm now supposed to see the results.
the form itself tells me the number of hits that each search garners,
but the results themselves a're not actually displayed anywhere. any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will use it
to log the footage he's shot for a documentary about bridge players. he
was in sydney in august and says that the game is very popular down
there. do you play?

Thanks, that worked wonderfully !

--
Novice


"Allen Browne" wrote:

Use the Like operator, with the star as a wildcard.

I assume this is being applied to a Text field, so it will need the extra
quotes as delimiters. Example:
strWhere = "[FirstName] Like """ & Me.[txtFindFirstName] & "*"""
or, if you want to find the name anywhere in the FirstName field:
strWhere = "[FirstName] Like ""*" & Me.[txtFindFirstName] & "*"""

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ben" wrote in message
...
Hi, just pinching in with an additional question (first of all thanks for
the
extended and helpfull info). How to do include a like question on this ?
I use it with names etc... so instead of having to write Benjamin that I
just write Be and that it shows me Ben, Benjamin, Bernard, Bert... etc...

Thanks

--
Novice


"Allen Browne" wrote:

The idea was to use a continuous form to view the results. You can use
the
Wizard to create a Tabular form, or you can do it yourself so as to
understand what's going on:

1. Create a new form, initially choosing your table as the RecordSource.

2. In form design view, open the Properties box (View menu.) Make sure
its
title reads Form so you are looking at the properties of the Form, not
those
of a text box.

3. On the Format tab of the Properties box, set Default View to
Continuous
Form.

4. If you do not see Form Header above the Detail section, click Form
Header/Footer on the View menu.

5. Add the fields you want to see as search results, by dragging them
from
the Field List onto the Detail section of your form. (Field List is on
the
View menu if you don't see that dialog.)

6. Arrange the text boxes so they are side by side. You probably want to
cut
the attached labels from the text boxes (select label, and Ctrl+X), paste
them into the form header, and move them above each column.

7. Now that the text boxes are all in one row horizontally, make the
Detail
section as small as possible vertically (typically the height of one text
box.)

8. Test the form: it should show many rows at once.

Once you have that working, you can add the search boxes and command
button
to the Form Header section, typically above the column labels. When you
click the command button, the form is effectively filtered to just the
records that match the search criteria.

If there are lots of other fields that cannot all be shown in the search
for, you might then like to add some code to the DblClick event of one of
the text boxes, so you can double-click the control to open another form
for
editing that record. For example if the primary key is an AutoNumber
field
named ID, you would end up with an event procedure like this:
Private Sub TP_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.ID) Then
strWhere = "[ID] = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If
End Sub


wrote in message
ups.com...
Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure out
queries and am very unsure about how forms and reports work. In fact,
I'm not sure how, in your method, i'm now supposed to see the results.
the form itself tells me the number of hits that each search garners,
but the results themselves a're not actually displayed anywhere. any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will use it
to log the footage he's shot for a documentary about bridge players. he
was in sydney in august and says that the game is very popular down
there. do you play?

You're a nice person, Allen.

One last question: I'd love it if the search would start when I press
"enter" in any of the fields, basically so I don't have to tab all the
way over to the search button if i'm not filling anything into the
remaining fields.

I've tried setting up an event that occurs On Key Press for the various
fields, but i'm not having much luck with it. Am I barking up the wrong
tree?

Thanks,

Mary


Ben wrote:
Thanks, that worked wonderfully !

--
Novice


"Allen Browne" wrote:

Use the Like operator, with the star as a wildcard.

I assume this is being applied to a Text field, so it will need the extra
quotes as delimiters. Example:
strWhere = "[FirstName] Like """ & Me.[txtFindFirstName] & "*"""
or, if you want to find the name anywhere in the FirstName field:
strWhere = "[FirstName] Like ""*" & Me.[txtFindFirstName] & "*"""

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ben" wrote in message
...
Hi, just pinching in with an additional question (first of all thanks for
the
extended and helpfull info). How to do include a like question on this ?
I use it with names etc... so instead of having to write Benjamin that I
just write Be and that it shows me Ben, Benjamin, Bernard, Bert... etc...

Thanks

--
Novice


"Allen Browne" wrote:

The idea was to use a continuous form to view the results. You can use
the
Wizard to create a Tabular form, or you can do it yourself so as to
understand what's going on:

1. Create a new form, initially choosing your table as the RecordSource.

2. In form design view, open the Properties box (View menu.) Make sure
its
title reads Form so you are looking at the properties of the Form, not
those
of a text box.

3. On the Format tab of the Properties box, set Default View to
Continuous
Form.

4. If you do not see Form Header above the Detail section, click Form
Header/Footer on the View menu.

5. Add the fields you want to see as search results, by dragging them
from
the Field List onto the Detail section of your form. (Field List is on
the
View menu if you don't see that dialog.)

6. Arrange the text boxes so they are side by side. You probably want to
cut
the attached labels from the text boxes (select label, and Ctrl+X), paste
them into the form header, and move them above each column.

7. Now that the text boxes are all in one row horizontally, make the
Detail
section as small as possible vertically (typically the height of one text
box.)

8. Test the form: it should show many rows at once.

Once you have that working, you can add the search boxes and command
button
to the Form Header section, typically above the column labels. When you
click the command button, the form is effectively filtered to just the
records that match the search criteria.

If there are lots of other fields that cannot all be shown in the search
for, you might then like to add some code to the DblClick event of one of
the text boxes, so you can double-click the control to open another form
for
editing that record. For example if the primary key is an AutoNumber
field
named ID, you would end up with an event procedure like this:
Private Sub TP_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.ID) Then
strWhere = "[ID] = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If
End Sub


wrote in message
ups.com...
Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure out
queries and am very unsure about how forms and reports work. In fact,
I'm not sure how, in your method, i'm now supposed to see the results.
the form itself tells me the number of hits that each search garners,
but the results themselves a're not actually displayed anywhere. any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will use it
to log the footage he's shot for a documentary about bridge players. he
was in sydney in august and says that the game is very popular down
there. do you play?

If you set the Default property of the search button to Yes, the search will
fire when you press Enter.

Alternatively, you could dispense with the search button, and move the code
into the the AfterUpdate event of the first search box. Then call that from
all the other search boxes as well. The only disadvantage of that approach
would be the time and network traffic required to execute the search any
time you type into any of the boxes, particularly where you wanted to enter
multiple values in different boxes but it is firing after every one. You
would end up with:

Private Sub txtTP_AfterUpdate()
Dim strSql As String
Dim lngLen As Long
'all the code in here.
End Sub

Private Sub txtFindSPKR_AfterUpdate()
Call txtTP_AfterUpdate
End Sub

Private Sub txtFindTranscript_AfterUpdate()
Call txtTP_AfterUpdate
End Sub

and so on.

And, no, I never did learn to play Bridge. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
ups.com...
You're a nice person, Allen.

One last question: I'd love it if the search would start when I press
"enter" in any of the fields, basically so I don't have to tab all the
way over to the search button if i'm not filling anything into the
remaining fields.

I've tried setting up an event that occurs On Key Press for the various
fields, but i'm not having much luck with it. Am I barking up the wrong
tree?

Thanks,

Mary


Ben wrote:
Thanks, that worked wonderfully !

--
Novice


"Allen Browne" wrote:

Use the Like operator, with the star as a wildcard.

I assume this is being applied to a Text field, so it will need the
extra
quotes as delimiters. Example:
strWhere = "[FirstName] Like """ & Me.[txtFindFirstName] & "*"""
or, if you want to find the name anywhere in the FirstName field:
strWhere = "[FirstName] Like ""*" & Me.[txtFindFirstName] & "*"""

"Ben" wrote in message
...
Hi, just pinching in with an additional question (first of all thanks
for
the
extended and helpfull info). How to do include a like question on
this ?
I use it with names etc... so instead of having to write Benjamin
that I
just write Be and that it shows me Ben, Benjamin, Bernard, Bert...
etc...

Thanks

--
Novice


"Allen Browne" wrote:

The idea was to use a continuous form to view the results. You can
use
the
Wizard to create a Tabular form, or you can do it yourself so as to
understand what's going on:

1. Create a new form, initially choosing your table as the
RecordSource.

2. In form design view, open the Properties box (View menu.) Make
sure
its
title reads Form so you are looking at the properties of the Form,
not
those
of a text box.

3. On the Format tab of the Properties box, set Default View to
Continuous
Form.

4. If you do not see Form Header above the Detail section, click
Form
Header/Footer on the View menu.

5. Add the fields you want to see as search results, by dragging
them
from
the Field List onto the Detail section of your form. (Field List is
on
the
View menu if you don't see that dialog.)

6. Arrange the text boxes so they are side by side. You probably
want to
cut
the attached labels from the text boxes (select label, and Ctrl+X),
paste
them into the form header, and move them above each column.

7. Now that the text boxes are all in one row horizontally, make the
Detail
section as small as possible vertically (typically the height of one
text
box.)

8. Test the form: it should show many rows at once.

Once you have that working, you can add the search boxes and command
button
to the Form Header section, typically above the column labels. When
you
click the command button, the form is effectively filtered to just
the
records that match the search criteria.

If there are lots of other fields that cannot all be shown in the
search
for, you might then like to add some code to the DblClick event of
one of
the text boxes, so you can double-click the control to open another
form
for
editing that record. For example if the primary key is an AutoNumber
field
named ID, you would end up with an event procedure like this:
Private Sub TP_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.ID) Then
strWhere = "[ID] = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If
End Sub


wrote in message
ups.com...
Thanks for your help, Allen.

The form is giving me great results in a variety of searches. I'm
no
coder, but I even managed to get wildcard searches involved.

I've spent all my time with Access so far just trying to figure
out
queries and am very unsure about how forms and reports work. In
fact,
I'm not sure how, in your method, i'm now supposed to see the
results.
the form itself tells me the number of hits that each search
garners,
but the results themselves a're not actually displayed anywhere.
any
direction you can give would be appreciated.

by the way, i'm building this database for my husband, who will
use it
to log the footage he's shot for a documentary about bridge
players. he
was in sydney in august and says that the game is very popular
down
there. do you play?