embed a query into a form that has the query criteria

[Ember]

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?

 

[Steve Schapel]

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?

 

[Ember]

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,

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

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?

 

[Steve Schapel]

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.

 

[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]

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.

 

[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

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

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]

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

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

:

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

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]

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

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

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

:

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

 

[Steve Schapel]

Ember,

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

 

[Ember]

What does the "Me." represent?

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]

It looks like the cmdSearch command button is on the main form. Right?
So the Me. is a keyword that refers to the current object (i.e. the
main form), and the subform control frmSearch_SubForm is a property of
the main form.

So, did you try it? Any luck? If not... what is the name of the
subform control?

 

[Ember]

Yes, the Search command button is on the "main form" frmSearch. The name of
the subform is frmSearch_Subform, and this is the form that I want to show
the query (qrySearch) results that correspond with the nine available
criteria's selected on frmSearch.

I did as you suggested (once with just "Me." and once with "Search") and I
am still getting "Complie Error: Label Not Defined" and it takes me to the
cmdSearch code:

Private Sub cmdSearch_Click()
Search.frmSearch_subform.Requery
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

With the Private Sub line highlighted in yellow.

Thanks
Ember

 

[Steve Schapel]

Ember,

The syntax for the requery line in your code is like this...
Me.frmSearch_subform.Requery

What is the purpose of the Exit Sub line in the code? As far as I can
see, it should be removed.

I believe the error is relating to this invalid line of code...
Resume Exit_cmdSearch_Click

Just try it like this...

Private Sub cmdSearch_Click()
Me.frmSearch_subform.Requery
End Sub

 

[Ember]

Did, thanks. Now I don't get an error message, but the subform does not
update to show the query results.

Ember

(After all this I am going to owe you a beer or something!)

 

[Steve Schapel]

Ember,

Ok, let's have another look at the SQL view of the query.

What happens when you click the button? Nothing at all? So the subform
always shows all records? Or no records?

And that query is definitely set as the Record Source of the subform, right?

How about if you have the form open, and enter some criteria in the
criteria boxes on the form, and then try to open the query datasheet
directly... does the query datasheet show the expected results?

 

[Ember]

When I click the button nothing happens. The subform shows no data at all.
I tried having the query open and then selecting criteria from the form, but
no data appears there either. The query is the record source for the subform.

I have tried a few other things and now I keep getting locked up when trying
to view the query in SQL view. Everything else in the database is still
accessible.

Ember

 

[Steve Schapel]

Ember,

I don't understand about the "locked up" bit - I have never experienced
anything like that. However, I think there is a limit to the length of
the SQL of a query, and maybe you have exceeded that? If you close the
database and then open it again, what happens with the query... can you
see it in design view? Does it still cause a problem if you then try to
switch from design view to SQL?

Having the query open, and then selecting criteria from the form, won't
work. You need to select the criteria first, and then open the query.
If it shows no records, there is something wrong with the query
criteria. I gave you 3 alternative ways of doing the criteria in the
query, and I don't know which one you chose.

Next step I would try, as an experiment, is to re-create the query from
scratch, but just put criteria (referencing one of the form controls) in
one of the fields, and see how that goes.

 

[Ember]

Okay, I deleted the query and remade it, this time adding
(Like[Forms]![frmSearch]![Field Name]) one at a time. I also took out the
"default" value of "*" out of my search criteria. It is working now. The
subform is refreshing to show all records that contain the selected criteria.

However, it only works for the "Or" sequence and not the "And". The purpose
of the Search form is to allow users to filter the records to match the
criteria. Example: If they seleted "ABC Company" under "Entity" and
"Central Filing System" the return should show only those records for ABA
Company that are in the Central Filing System.

As of now I can get the "Or" sequence to work, but that results in showing
any record for ABC Comapny OR any record stored in the Central Filing System.
Two very different results.

Ember

 

[Ember]

One more comment...If I select specific criteria right from the query itself
it works. It appears the hang-up is when I use the form to select the
criteria.

Ember