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
|
|||
|
|||
How to filter data in a subreport
I have a main report displaying records. I open the report from a form.
Each record has a number of "child" records. I display those in a a subreport. Now I want to to have a radio-button on my form to allow the user to filter some records. But I have trouble applying the filter to my subreport. Is there a way to do that? |
#2
|
|||
|
|||
How to filter data in a subreport
The simplest way to filter a subreport is to use the Master/Child Link
Fields (properties of the subreport control.) For example, say Form1 has a on option group named Frame1, and if option value 2 is chosen, you want to filter the subreport to only the records where AmountDue is zero. You put a text box on the main report, and give it these properties: Control Source =-1 Name txtTrue Then put a text box in the subreport, with properties: Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1) Name txtFilterFlag Now let's assume the LinkMasterFields/LinkChildFields already have the ID field in them. You add these 2 new text box names to the properties, like this: Link Master Fields [ID], [txtTrue] Link Child Fields [ID], [txtFilterFlag] The text box on the main report is always -1, which in Access is the value for True. If the frame on the form is set to any value other than 2, txtFilterFlag also returns True for everything, so it's not filtered. If the frame is value 2, then the expression will be true only in the records when the amount due is filtered, so they are the only ones that will show up, and so the subreport is filtered. Another way to approach this is to set up the subreport's query to its criteria read the frame on the control and return only true records. Finally, if neither of those approaches work, you can programmatically write the SQL property of the QueryDef that the subreport reads from, before opening the report. -- 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. "RSunday" wrote in message ... I have a main report displaying records. I open the report from a form. Each record has a number of "child" records. I display those in a a subreport. Now I want to to have a radio-button on my form to allow the user to filter some records. But I have trouble applying the filter to my subreport. Is there a way to do that? |
#3
|
|||
|
|||
How to filter data in a subreport
Thank you for the suggestions.
I have tried to set the filter properties on my subreport from VBA - but I have trouble adressing the report properties correctly - I have tried to do it in the "on open" event on the subreport - but that doesn't work - then I tried it from the "on open" of the main report - but here I also have trouble. Any comments to this approach? "Allen Browne" wrote: The simplest way to filter a subreport is to use the Master/Child Link Fields (properties of the subreport control.) For example, say Form1 has a on option group named Frame1, and if option value 2 is chosen, you want to filter the subreport to only the records where AmountDue is zero. You put a text box on the main report, and give it these properties: Control Source =-1 Name txtTrue Then put a text box in the subreport, with properties: Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1) Name txtFilterFlag Now let's assume the LinkMasterFields/LinkChildFields already have the ID field in them. You add these 2 new text box names to the properties, like this: Link Master Fields [ID], [txtTrue] Link Child Fields [ID], [txtFilterFlag] The text box on the main report is always -1, which in Access is the value for True. If the frame on the form is set to any value other than 2, txtFilterFlag also returns True for everything, so it's not filtered. If the frame is value 2, then the expression will be true only in the records when the amount due is filtered, so they are the only ones that will show up, and so the subreport is filtered. Another way to approach this is to set up the subreport's query to its criteria read the frame on the control and return only true records. Finally, if neither of those approaches work, you can programmatically write the SQL property of the QueryDef that the subreport reads from, before opening the report. -- 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. "RSunday" wrote in message ... I have a main report displaying records. I open the report from a form. Each record has a number of "child" records. I display those in a a subreport. Now I want to to have a radio-button on my form to allow the user to filter some records. But I have trouble applying the filter to my subreport. Is there a way to do that? |
#4
|
|||
|
|||
How to filter data in a subreport
Yep: I agree with your conclusions: that's an exercise in frustration.
Hence the suggested alternatives. -- 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. "RSunday" wrote in message ... Thank you for the suggestions. I have tried to set the filter properties on my subreport from VBA - but I have trouble adressing the report properties correctly - I have tried to do it in the "on open" event on the subreport - but that doesn't work - then I tried it from the "on open" of the main report - but here I also have trouble. Any comments to this approach? "Allen Browne" wrote: The simplest way to filter a subreport is to use the Master/Child Link Fields (properties of the subreport control.) For example, say Form1 has a on option group named Frame1, and if option value 2 is chosen, you want to filter the subreport to only the records where AmountDue is zero. You put a text box on the main report, and give it these properties: Control Source =-1 Name txtTrue Then put a text box in the subreport, with properties: Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1) Name txtFilterFlag Now let's assume the LinkMasterFields/LinkChildFields already have the ID field in them. You add these 2 new text box names to the properties, like this: Link Master Fields [ID], [txtTrue] Link Child Fields [ID], [txtFilterFlag] The text box on the main report is always -1, which in Access is the value for True. If the frame on the form is set to any value other than 2, txtFilterFlag also returns True for everything, so it's not filtered. If the frame is value 2, then the expression will be true only in the records when the amount due is filtered, so they are the only ones that will show up, and so the subreport is filtered. Another way to approach this is to set up the subreport's query to its criteria read the frame on the control and return only true records. Finally, if neither of those approaches work, you can programmatically write the SQL property of the QueryDef that the subreport reads from, before opening the report. -- 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. "RSunday" wrote in message ... I have a main report displaying records. I open the report from a form. Each record has a number of "child" records. I display those in a a subreport. Now I want to to have a radio-button on my form to allow the user to filter some records. But I have trouble applying the filter to my subreport. Is there a way to do that? |
#5
|
|||
|
|||
How to filter data in a subreport
Now I tried to add extra parent-child fields. First as text boxes - but
seemed to have trouble to link a text box to another text box. So now I just added two calculated fields to the Record Sources of the main and sub report. I even show the values of these calculated fields on my reports and can see that they are as I want them to be. But when I add them to my Link Child Fields and Link Master Fields - then I dont get any data in my sub report. So I believe I may have a data type issue - somehow my two calculated fields are not of the same type or something. Any suggestions? "Allen Browne" wrote: Yep: I agree with your conclusions: that's an exercise in frustration. Hence the suggested alternatives. -- 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. "RSunday" wrote in message ... Thank you for the suggestions. I have tried to set the filter properties on my subreport from VBA - but I have trouble adressing the report properties correctly - I have tried to do it in the "on open" event on the subreport - but that doesn't work - then I tried it from the "on open" of the main report - but here I also have trouble. Any comments to this approach? "Allen Browne" wrote: The simplest way to filter a subreport is to use the Master/Child Link Fields (properties of the subreport control.) For example, say Form1 has a on option group named Frame1, and if option value 2 is chosen, you want to filter the subreport to only the records where AmountDue is zero. You put a text box on the main report, and give it these properties: Control Source =-1 Name txtTrue Then put a text box in the subreport, with properties: Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1) Name txtFilterFlag Now let's assume the LinkMasterFields/LinkChildFields already have the ID field in them. You add these 2 new text box names to the properties, like this: Link Master Fields [ID], [txtTrue] Link Child Fields [ID], [txtFilterFlag] The text box on the main report is always -1, which in Access is the value for True. If the frame on the form is set to any value other than 2, txtFilterFlag also returns True for everything, so it's not filtered. If the frame is value 2, then the expression will be true only in the records when the amount due is filtered, so they are the only ones that will show up, and so the subreport is filtered. Another way to approach this is to set up the subreport's query to its criteria read the frame on the control and return only true records. Finally, if neither of those approaches work, you can programmatically write the SQL property of the QueryDef that the subreport reads from, before opening the report. -- 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. "RSunday" wrote in message ... I have a main report displaying records. I open the report from a form. Each record has a number of "child" records. I display those in a a subreport. Now I want to to have a radio-button on my form to allow the user to filter some records. But I have trouble applying the filter to my subreport. Is there a way to do that? |
#6
|
|||
|
|||
How to filter data in a subreport
Now I got it to work - based on the calculated fields in the record sources.
Somehow I had added two text boxes - with conflicting names of some kind - and I was unable to get to it and delete it. I tried to "tab" through the elements of the report - but couldn't get to the control with the conflicting name... But I then took my backup and made the changes again in the reports - and now it works!!! "RSunday" wrote: Now I tried to add extra parent-child fields. First as text boxes - but seemed to have trouble to link a text box to another text box. So now I just added two calculated fields to the Record Sources of the main and sub report. I even show the values of these calculated fields on my reports and can see that they are as I want them to be. But when I add them to my Link Child Fields and Link Master Fields - then I dont get any data in my sub report. So I believe I may have a data type issue - somehow my two calculated fields are not of the same type or something. Any suggestions? "Allen Browne" wrote: Yep: I agree with your conclusions: that's an exercise in frustration. Hence the suggested alternatives. -- 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. "RSunday" wrote in message ... Thank you for the suggestions. I have tried to set the filter properties on my subreport from VBA - but I have trouble adressing the report properties correctly - I have tried to do it in the "on open" event on the subreport - but that doesn't work - then I tried it from the "on open" of the main report - but here I also have trouble. Any comments to this approach? "Allen Browne" wrote: The simplest way to filter a subreport is to use the Master/Child Link Fields (properties of the subreport control.) For example, say Form1 has a on option group named Frame1, and if option value 2 is chosen, you want to filter the subreport to only the records where AmountDue is zero. You put a text box on the main report, and give it these properties: Control Source =-1 Name txtTrue Then put a text box in the subreport, with properties: Control Source =IIf([Forms].[Form1].[Frame1]=2, [AmountDue]=0, -1) Name txtFilterFlag Now let's assume the LinkMasterFields/LinkChildFields already have the ID field in them. You add these 2 new text box names to the properties, like this: Link Master Fields [ID], [txtTrue] Link Child Fields [ID], [txtFilterFlag] The text box on the main report is always -1, which in Access is the value for True. If the frame on the form is set to any value other than 2, txtFilterFlag also returns True for everything, so it's not filtered. If the frame is value 2, then the expression will be true only in the records when the amount due is filtered, so they are the only ones that will show up, and so the subreport is filtered. Another way to approach this is to set up the subreport's query to its criteria read the frame on the control and return only true records. Finally, if neither of those approaches work, you can programmatically write the SQL property of the QueryDef that the subreport reads from, before opening the report. -- 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. "RSunday" wrote in message ... I have a main report displaying records. I open the report from a form. Each record has a number of "child" records. I display those in a a subreport. Now I want to to have a radio-button on my form to allow the user to filter some records. But I have trouble applying the filter to my subreport. Is there a way to do that? |
Thread Tools | |
Display Modes | |
|
|