If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
embed a query into a form that has the query criteria
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? |
#3
|
|||
|
|||
embed a query into a form that has the query criteria
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? |
#4
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
#5
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
#6
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
#7
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
#8
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
#9
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
#10
|
|||
|
|||
embed a query into a form that has the query criteria
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Customers- Contracts form problem | GL | Using Forms | 10 | February 7th, 2006 04:05 PM |
using a form with combo box to input criteria | Christina | Using Forms | 16 | February 3rd, 2006 08:47 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Form to Enter Report Criteria works, but Query won't run from Macr | BARKAROO | Using Forms | 1 | October 16th, 2004 08:13 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |