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
|
|||
|
|||
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 |
#2
|
|||
|
|||
Trouble with a multiple parameter search
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 |
#3
|
|||
|
|||
Trouble with a multiple parameter search
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 |
#4
|
|||
|
|||
Trouble with a multiple parameter search
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 |
#5
|
|||
|
|||
Trouble with a multiple parameter search
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 |
#6
|
|||
|
|||
Trouble with a multiple parameter search
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? |
#7
|
|||
|
|||
Trouble with a multiple parameter search
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? |
#8
|
|||
|
|||
Trouble with a multiple parameter search
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? |
#9
|
|||
|
|||
Trouble with a multiple parameter search
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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple Field Search Query | Kerry | Running & Setting Up Queries | 16 | July 1st, 2006 06:09 AM |
[ Parameter Queries ] - Multiple Values for One Parameter | Robbie Baquiran | Running & Setting Up Queries | 8 | December 9th, 2005 02:23 AM |
how can i search for text in multiple pages?? | shannsett | Publisher | 5 | November 27th, 2005 01:15 PM |
Parameter Query - Multiple Selection - Quick Question | Sean | Running & Setting Up Queries | 1 | October 31st, 2005 07:55 PM |