embed a query into a form that has the query criteria

I have created a unbound form that contains nine unbound text boxes (five of
which are combo boxes) which have a default value of "*". The purpose of
which is to create a "search" form for users that lets them enter criteria to
search the records in the database.

The query that I created calls for the information entered into these boxes
as its criteria to return information. (Example "Like
[Forms]![frmSearch]![Entity]")

Two issues: first when all fields are left at "*" all records are returned,
as they should be. However, when any information is entered all records are
still returned and are not filtered by what was entered.

Second: I have embedded the query into the form so that the serach
perameters are on the top half and the query results are in the bottom half
so, supposedly, the results will be shown without leaving the form (in case
they want to serach again or enter more criteria to further filter the
results. However, when I click my "Search" comand botton to run the query,
it opens the results in a new window as opposed to showing them below the
search perameters.

Any suggestions?

Ember,

What are the details of the query? What are the details of the macro or
VBA procedure that you have running on the Click event of the 'Search'
button?

--
Steve Schapel, Microsoft Access MVP

Ember wrote:
I have created a unbound form that contains nine unbound text boxes (five of
which are combo boxes) which have a default value of "*". The purpose of
which is to create a "search" form for users that lets them enter criteria to
search the records in the database.

The query that I created calls for the information entered into these boxes
as its criteria to return information. (Example "Like
[Forms]![frmSearch]![Entity]")

Two issues: first when all fields are left at "*" all records are returned,
as they should be. However, when any information is entered all records are
still returned and are not filtered by what was entered.

Second: I have embedded the query into the form so that the serach
perameters are on the top half and the query results are in the bottom half
so, supposedly, the results will be shown without leaving the form (in case
they want to serach again or enter more criteria to further filter the
results. However, when I click my "Search" comand botton to run the query,
it opens the results in a new window as opposed to showing them below the
search perameters.

Any suggestions?

For the search button:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String

stDocName = "qrySearch"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

Not sure what you would like to know about the query.

Ember

"Steve Schapel" wrote:

Ember,

What are the details of the query? What are the details of the macro or
VBA procedure that you have running on the Click event of the 'Search'
button?

--
Steve Schapel, Microsoft Access MVP

Ember wrote:
I have created a unbound form that contains nine unbound text boxes (five of
which are combo boxes) which have a default value of "*". The purpose of
which is to create a "search" form for users that lets them enter criteria to
search the records in the database.

The query that I created calls for the information entered into these boxes
as its criteria to return information. (Example "Like
[Forms]![frmSearch]![Entity]")

Two issues: first when all fields are left at "*" all records are returned,
as they should be. However, when any information is entered all records are
still returned and are not filtered by what was entered.

Second: I have embedded the query into the form so that the serach
perameters are on the top half and the query results are in the bottom half
so, supposedly, the results will be shown without leaving the form (in case
they want to serach again or enter more criteria to further filter the
results. However, when I click my "Search" comand botton to run the query,
it opens the results in a new window as opposed to showing them below the
search perameters.

Any suggestions?

Ember,

Thanks. When you say the query is "embedded" into the form, I assumne
this means you have a subform which is based on the query, is that
right? So, it is not applicable to use the OpenQuery method here. Most
likely, the only code you need here on the Search button's Click event
would be like this...
Me.NameOfYourSubform.Requery

As for the query details, please go to design view of the query, select
SQL from the View menu, and then copy/paste the whole lot into your
reply post. Thanks.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:
For the search button:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String

stDocName = "qrySearch"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

Not sure what you would like to know about the query.

Ember

Yes, I have a subform that is based on my query (qrySearch). I will change
the code to be what you have suggested and see what that does.

As for the query itself, here is the code.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date]
FROM tblFiles
WHERE (((tblFiles.[File ID]) Like [Forms]![frmSearch]![File ID] And
(tblFiles.[File ID]) Like "*") AND ((tblFiles.Entity) Like
[Forms]![frmSearch]![Entity] And (tblFiles.Entity) Like "*") AND
((tblFiles.Location) Like [Forms]![frmSearch]![Location] And
(tblFiles.Location) Like "*") AND ((tblFiles.[Record Series]) Like
[Forms]![frmSearch]![Record Series] And (tblFiles.[Record Series]) Like "*")
AND ((tblFiles.[Document Type]) Like [Forms]![frmSearch]![Document Type] And
(tblFiles.[Document Type]) Like "*") AND ((tblFiles.[File Name]) Like
[Forms]![frmSearch]![File Name] And (tblFiles.[File Name]) Like "*") AND
((tblFiles.[File Description]) Like [Forms]![frmSearch]![File Description]
And (tblFiles.[File Description]) Like "*") AND ((tblFiles.[Entered By]) Like
[Forms]![frmSearch]![Entered By] And (tblFiles.[Entered By]) Like "*") AND
((tblFiles.[Creation Date]) Like [Forms]![frmSearch]![Creation Date] And
(tblFiles.[Creation Date]) Like "*"));

Ember

"Steve Schapel" wrote:

Ember,

Thanks. When you say the query is "embedded" into the form, I assumne
this means you have a subform which is based on the query, is that
right? So, it is not applicable to use the OpenQuery method here. Most
likely, the only code you need here on the Search button's Click event
would be like this...
Me.NameOfYourSubform.Requery

As for the query details, please go to design view of the query, select
SQL from the View menu, and then copy/paste the whole lot into your
reply post. Thanks.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:
For the search button:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String

stDocName = "qrySearch"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

Not sure what you would like to know about the query.

Ember

Ember,

It looks like you have entered something like this into the criteria in
the query...
Like [Forms]![frmSearch]![File ID] And Like "*"

This is not right... it doesn't really make sense. Change it to...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID]="*"

You will notice after you close and save the query, if you open it again
in design view you will see that Access has re-organised the criteria to
suit its own purposes - that's ok.

Another way is to forget the "*" thing in the criteria boxes on the form
- at actually serves no purpose to do this. You can just leave them
blank, and then the criteria in the query can be either like this...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID] Is Null
.... or like this...
Like Nz([Forms]![frmSearch]![File ID],"*")

Try that and let us know.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:
Yes, I have a subform that is based on my query (qrySearch). I will change
the code to be what you have suggested and see what that does.

As for the query itself, here is the code.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date]
FROM tblFiles
WHERE (((tblFiles.[File ID]) Like [Forms]![frmSearch]![File ID] And
(tblFiles.[File ID]) Like "*") AND ((tblFiles.Entity) Like
[Forms]![frmSearch]![Entity] And (tblFiles.Entity) Like "*") AND
((tblFiles.Location) Like [Forms]![frmSearch]![Location] And
(tblFiles.Location) Like "*") AND ((tblFiles.[Record Series]) Like
[Forms]![frmSearch]![Record Series] And (tblFiles.[Record Series]) Like "*")
AND ((tblFiles.[Document Type]) Like [Forms]![frmSearch]![Document Type] And
(tblFiles.[Document Type]) Like "*") AND ((tblFiles.[File Name]) Like
[Forms]![frmSearch]![File Name] And (tblFiles.[File Name]) Like "*") AND
((tblFiles.[File Description]) Like [Forms]![frmSearch]![File Description]
And (tblFiles.[File Description]) Like "*") AND ((tblFiles.[Entered By]) Like
[Forms]![frmSearch]![Entered By] And (tblFiles.[Entered By]) Like "*") AND
((tblFiles.[Creation Date]) Like [Forms]![frmSearch]![Creation Date] And
(tblFiles.[Creation Date]) Like "*"));

Ember

I did that (and saw what the query did in the criteria fields) and I modified
the Search command button. When I "run" it now I get "Complie Error: Label
not defined" and it takes me to the code:

Private Sub cmdSearch_Click()
frmSearch.frmSearch SubForm.Requery
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End SubOkay

And the Private Sub line is highlighted in yellow.

Ember

"Steve Schapel" wrote:

Ember,

It looks like you have entered something like this into the criteria in
the query...
Like [Forms]![frmSearch]![File ID] And Like "*"

This is not right... it doesn't really make sense. Change it to...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID]="*"

You will notice after you close and save the query, if you open it again
in design view you will see that Access has re-organised the criteria to
suit its own purposes - that's ok.

Another way is to forget the "*" thing in the criteria boxes on the form
- at actually serves no purpose to do this. You can just leave them
blank, and then the criteria in the query can be either like this...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID] Is Null
.... or like this...
Like Nz([Forms]![frmSearch]![File ID],"*")

Try that and let us know.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:
Yes, I have a subform that is based on my query (qrySearch). I will change
the code to be what you have suggested and see what that does.

As for the query itself, here is the code.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date]
FROM tblFiles
WHERE (((tblFiles.[File ID]) Like [Forms]![frmSearch]![File ID] And
(tblFiles.[File ID]) Like "*") AND ((tblFiles.Entity) Like
[Forms]![frmSearch]![Entity] And (tblFiles.Entity) Like "*") AND
((tblFiles.Location) Like [Forms]![frmSearch]![Location] And
(tblFiles.Location) Like "*") AND ((tblFiles.[Record Series]) Like
[Forms]![frmSearch]![Record Series] And (tblFiles.[Record Series]) Like "*")
AND ((tblFiles.[Document Type]) Like [Forms]![frmSearch]![Document Type] And
(tblFiles.[Document Type]) Like "*") AND ((tblFiles.[File Name]) Like
[Forms]![frmSearch]![File Name] And (tblFiles.[File Name]) Like "*") AND
((tblFiles.[File Description]) Like [Forms]![frmSearch]![File Description]
And (tblFiles.[File Description]) Like "*") AND ((tblFiles.[Entered By]) Like
[Forms]![frmSearch]![Entered By] And (tblFiles.[Entered By]) Like "*") AND
((tblFiles.[Creation Date]) Like [Forms]![frmSearch]![Creation Date] And
(tblFiles.[Creation Date]) Like "*"));

Ember

Ember,

As per my earlier reply, do it like this...
Me.NameOfYourSubform.Requery
(replacing with the actual name of your subform control of course).

I have no idea what
frmSearch.frmSearch SubForm.Requery
means, or where it cam from. Also, I don't know about
End SubOkay
Should lust be End Sub I think.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:
I did that (and saw what the query did in the criteria fields) and I modified
the Search command button. When I "run" it now I get "Complie Error: Label
not defined" and it takes me to the code:

Private Sub cmdSearch_Click()
frmSearch.frmSearch SubForm.Requery
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End SubOkay

And the Private Sub line is highlighted in yellow.

Ember

"Steve Schapel" wrote:


Ember,

It looks like you have entered something like this into the criteria in
the query...
Like [Forms]![frmSearch]![File ID] And Like "*"

This is not right... it doesn't really make sense. Change it to...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID]="*"

You will notice after you close and save the query, if you open it again
in design view you will see that Access has re-organised the criteria to
suit its own purposes - that's ok.

Another way is to forget the "*" thing in the criteria boxes on the form
- at actually serves no purpose to do this. You can just leave them
blank, and then the criteria in the query can be either like this...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID] Is Null
.... or like this...
Like Nz([Forms]![frmSearch]![File ID],"*")

Try that and let us know.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:

Yes, I have a subform that is based on my query (qrySearch). I will change
the code to be what you have suggested and see what that does.

As for the query itself, here is the code.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date]
FROM tblFiles
WHERE (((tblFiles.[File ID]) Like [Forms]![frmSearch]![File ID] And
(tblFiles.[File ID]) Like "*") AND ((tblFiles.Entity) Like
[Forms]![frmSearch]![Entity] And (tblFiles.Entity) Like "*") AND
((tblFiles.Location) Like [Forms]![frmSearch]![Location] And
(tblFiles.Location) Like "*") AND ((tblFiles.[Record Series]) Like
[Forms]![frmSearch]![Record Series] And (tblFiles.[Record Series]) Like "*")
AND ((tblFiles.[Document Type]) Like [Forms]![frmSearch]![Document Type] And
(tblFiles.[Document Type]) Like "*") AND ((tblFiles.[File Name]) Like
[Forms]![frmSearch]![File Name] And (tblFiles.[File Name]) Like "*") AND
((tblFiles.[File Description]) Like [Forms]![frmSearch]![File Description]
And (tblFiles.[File Description]) Like "*") AND ((tblFiles.[Entered By]) Like
[Forms]![frmSearch]![Entered By] And (tblFiles.[Entered By]) Like "*") AND
((tblFiles.[Creation Date]) Like [Forms]![frmSearch]![Creation Date] And
(tblFiles.[Creation Date]) Like "*"));

Ember

The frmSearch.frmSearch SubForm.Requery was my attempt to insert your
suggestion of Me.NameOfYourSubform.Requery into the comand button. Did I not
complete that statement correctly?

Ember

"Steve Schapel" wrote:

Ember,

As per my earlier reply, do it like this...
Me.NameOfYourSubform.Requery
(replacing with the actual name of your subform control of course).

I have no idea what
frmSearch.frmSearch SubForm.Requery
means, or where it cam from. Also, I don't know about
End SubOkay
Should lust be End Sub I think.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:
I did that (and saw what the query did in the criteria fields) and I modified
the Search command button. When I "run" it now I get "Complie Error: Label
not defined" and it takes me to the code:

Private Sub cmdSearch_Click()
frmSearch.frmSearch SubForm.Requery
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End SubOkay

And the Private Sub line is highlighted in yellow.

Ember

"Steve Schapel" wrote:


Ember,

It looks like you have entered something like this into the criteria in
the query...
Like [Forms]![frmSearch]![File ID] And Like "*"

This is not right... it doesn't really make sense. Change it to...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID]="*"

You will notice after you close and save the query, if you open it again
in design view you will see that Access has re-organised the criteria to
suit its own purposes - that's ok.

Another way is to forget the "*" thing in the criteria boxes on the form
- at actually serves no purpose to do this. You can just leave them
blank, and then the criteria in the query can be either like this...
[Forms]![frmSearch]![File ID] Or [Forms]![frmSearch]![File ID] Is Null
.... or like this...
Like Nz([Forms]![frmSearch]![File ID],"*")

Try that and let us know.

--
Steve Schapel, Microsoft Access MVP


Ember wrote:

Yes, I have a subform that is based on my query (qrySearch). I will change
the code to be what you have suggested and see what that does.

As for the query itself, here is the code.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date]
FROM tblFiles
WHERE (((tblFiles.[File ID]) Like [Forms]![frmSearch]![File ID] And
(tblFiles.[File ID]) Like "*") AND ((tblFiles.Entity) Like
[Forms]![frmSearch]![Entity] And (tblFiles.Entity) Like "*") AND
((tblFiles.Location) Like [Forms]![frmSearch]![Location] And
(tblFiles.Location) Like "*") AND ((tblFiles.[Record Series]) Like
[Forms]![frmSearch]![Record Series] And (tblFiles.[Record Series]) Like "*")
AND ((tblFiles.[Document Type]) Like [Forms]![frmSearch]![Document Type] And
(tblFiles.[Document Type]) Like "*") AND ((tblFiles.[File Name]) Like
[Forms]![frmSearch]![File Name] And (tblFiles.[File Name]) Like "*") AND
((tblFiles.[File Description]) Like [Forms]![frmSearch]![File Description]
And (tblFiles.[File Description]) Like "*") AND ((tblFiles.[Entered By]) Like
[Forms]![frmSearch]![Entered By] And (tblFiles.[Entered By]) Like "*") AND
((tblFiles.[Creation Date]) Like [Forms]![frmSearch]![Creation Date] And
(tblFiles.[Creation Date]) Like "*"));

Ember

Ember,

What's the name of the subform control? frmSearch SubForm? If so, I
would guess this would do it...
Me.frmSearch_SubForm.Requery

--
Steve Schapel, Microsoft Access MVP

Ember wrote:
The frmSearch.frmSearch SubForm.Requery was my attempt to insert your
suggestion of Me.NameOfYourSubform.Requery into the comand button. Did I not
complete that statement correctly?

Ember