Change subform filter after lookup?
in [Forms]
|
From: Dennis on 22 Mar 2010 15:40 I don't use the Aplpy Filter button. I want to do everything in VBA. I use the ".Filter = " and then the ".FilterOn = True". But I do these from the parent form. As I said, it works fine on the initial lookup, but then once the subform is opened to the records from the initial Filter statement, I cannot load a filter over that one and requery. If I CLOSE the subform, THEN it works, but now while the subform is OPEN. But when I reopen the subform, it becomes stand-alone, which is not what I want at all. Thanks! "Daryl S" wrote: > Dennis - > > No, I should have indicated these were two different things to try. I would > make sure the filter was turned on after providing the filter criteria. I > assume the filter criteria you are writing is good for the recordsource of > the subform, since that data is not related to the parent form. You > shouldn't need to requery if you are just applying a filter that is > consistent with the records in the subform. > > When you run your code, does the filter not work? What happens if you > toggle the Apply Filter button when the focus is in the subform - can you see > the filter being applied and removed? If so, the syntax of the filter string > is probably fine, and you may want to try the ApplyFilter of the DoCmd... > > -- > Daryl S > > > "Dennis" wrote: > > > Daryl, I did the filterOn = True. But do you have to have that set to true > > BEFORE changing the filter text? > > > > marshall, the reQuery was ineffective. I've done this successfully in times > > past, so I KNOW it's possible. But this thread was started because I had no > > freakin' clue as to why it wouldn't work. So I guess I'm still kind-of at a > > loss as to what's not working properly. Any other ideas? (Oh, I just > > remembered - the subform is not actually tied to the parent form. It's a > > fully independent query that drives it, and I set an initial filter when I > > open the parent.) > > > > Thanks guys! > > > > Dennis > > > > > > > > "Marshall Barton" wrote: > > > > > Dennis wrote: > > > > > > >In Access2003, I have a subform that I set a filter for on initial lookup. It > > > >works fine. Then, I want to "re-filter" the data (for example, to limit the > > > >records displayed to those matching a certain Job Number for a given Tech), > > > >but the subform doesn't seem to requery. > > > > > > > >I've tried the following to no effect: > > > > > > > >Me.HoursDetail.Form.Filter = FilterString > > > > > > > >where HoursDetail is the name of the subform control on the parent form, and > > > >FilterString is the text of the filter. If I close and reopen the subform as > > > >stand-alone, the new filter gets applied properly, but of course that defeats > > > >the purpose of having a subform. > > > > > > > >I have also tried to use: > > > > > > > >Me.HoursDetail.Requery > > > > > > > >afterward, but also to no effect > > > > > > > > > Did you forget to use: > > > Me.HoursDetail.Form.FilterOn = True > > > > > > -- > > > Marsh > > > MVP [MS Access] > > > . > > >
From: Douglas J. Steele on 22 Mar 2010 15:45 What's the exact code you're using to set the Filter and FilterOn properties? -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:69318370-3B14-4025-BACB-BA53B32B0886(a)microsoft.com... >I don't use the Aplpy Filter button. I want to do everything in VBA. I use > the ".Filter = " and then the ".FilterOn = True". But I do these from the > parent form. As I said, it works fine on the initial lookup, but then once > the subform is opened to the records from the initial Filter statement, I > cannot load a filter over that one and requery. If I CLOSE the subform, > THEN > it works, but now while the subform is OPEN. But when I reopen the > subform, > it becomes stand-alone, which is not what I want at all. > > Thanks! > > "Daryl S" wrote: > >> Dennis - >> >> No, I should have indicated these were two different things to try. I >> would >> make sure the filter was turned on after providing the filter criteria. >> I >> assume the filter criteria you are writing is good for the recordsource >> of >> the subform, since that data is not related to the parent form. You >> shouldn't need to requery if you are just applying a filter that is >> consistent with the records in the subform. >> >> When you run your code, does the filter not work? What happens if you >> toggle the Apply Filter button when the focus is in the subform - can you >> see >> the filter being applied and removed? If so, the syntax of the filter >> string >> is probably fine, and you may want to try the ApplyFilter of the DoCmd... >> >> -- >> Daryl S >> >> >> "Dennis" wrote: >> >> > Daryl, I did the filterOn = True. But do you have to have that set to >> > true >> > BEFORE changing the filter text? >> > >> > marshall, the reQuery was ineffective. I've done this successfully in >> > times >> > past, so I KNOW it's possible. But this thread was started because I >> > had no >> > freakin' clue as to why it wouldn't work. So I guess I'm still kind-of >> > at a >> > loss as to what's not working properly. Any other ideas? (Oh, I just >> > remembered - the subform is not actually tied to the parent form. It's >> > a >> > fully independent query that drives it, and I set an initial filter >> > when I >> > open the parent.) >> > >> > Thanks guys! >> > >> > Dennis >> > >> > >> > >> > "Marshall Barton" wrote: >> > >> > > Dennis wrote: >> > > >> > > >In Access2003, I have a subform that I set a filter for on initial >> > > >lookup. It >> > > >works fine. Then, I want to "re-filter" the data (for example, to >> > > >limit the >> > > >records displayed to those matching a certain Job Number for a given >> > > >Tech), >> > > >but the subform doesn't seem to requery. >> > > > >> > > >I've tried the following to no effect: >> > > > >> > > >Me.HoursDetail.Form.Filter = FilterString >> > > > >> > > >where HoursDetail is the name of the subform control on the parent >> > > >form, and >> > > >FilterString is the text of the filter. If I close and reopen the >> > > >subform as >> > > >stand-alone, the new filter gets applied properly, but of course >> > > >that defeats >> > > >the purpose of having a subform. >> > > > >> > > >I have also tried to use: >> > > > >> > > >Me.HoursDetail.Requery >> > > > >> > > >afterward, but also to no effect >> > > >> > > >> > > Did you forget to use: >> > > Me.HoursDetail.Form.FilterOn = True >> > > >> > > -- >> > > Marsh >> > > MVP [MS Access] >> > > . >> > >
From: Marshall Barton on 22 Mar 2010 16:34 Dennis wrote: >I did the filterOn = True. But do you have to have that set to true >BEFORE changing the filter text? > >The reQuery was ineffective. I've done this successfully in times >past, so I KNOW it's possible. But this thread was started because I had no >freakin' clue as to why it wouldn't work. So I guess I'm still kind-of at a >loss as to what's not working properly. Any other ideas? (Oh, I just >remembered - the subform is not actually tied to the parent form. It's a >fully independent query that drives it, and I set an initial filter when I >open the parent.) Note that FilterOn is normally used after setting the Filter Property. That is not a rule, but the behavior may be a little different if it's before. (Think about not changing the filter and just turning it on and off, or a situation where FilterOn is already True and you just set it to a different condition.) A Requery is automatic after changing the Filter and FilterOn is True so it's never needed in those situations. I suspect your problem is because you have something else going on in your code, so please post a Copy/Pates of the code and we'll look at it. -- Marsh MVP [MS Access]
From: Daryl S on 23 Mar 2010 10:13 Dennis - Can you post your code? Maybe we can spot the issue there... -- Daryl S "Dennis" wrote: > I don't use the Aplpy Filter button. I want to do everything in VBA. I use > the ".Filter = " and then the ".FilterOn = True". But I do these from the > parent form. As I said, it works fine on the initial lookup, but then once > the subform is opened to the records from the initial Filter statement, I > cannot load a filter over that one and requery. If I CLOSE the subform, THEN > it works, but now while the subform is OPEN. But when I reopen the subform, > it becomes stand-alone, which is not what I want at all. > > Thanks! > > "Daryl S" wrote: > > > Dennis - > > > > No, I should have indicated these were two different things to try. I would > > make sure the filter was turned on after providing the filter criteria. I > > assume the filter criteria you are writing is good for the recordsource of > > the subform, since that data is not related to the parent form. You > > shouldn't need to requery if you are just applying a filter that is > > consistent with the records in the subform. > > > > When you run your code, does the filter not work? What happens if you > > toggle the Apply Filter button when the focus is in the subform - can you see > > the filter being applied and removed? If so, the syntax of the filter string > > is probably fine, and you may want to try the ApplyFilter of the DoCmd... > > > > -- > > Daryl S > > > > > > "Dennis" wrote: > > > > > Daryl, I did the filterOn = True. But do you have to have that set to true > > > BEFORE changing the filter text? > > > > > > marshall, the reQuery was ineffective. I've done this successfully in times > > > past, so I KNOW it's possible. But this thread was started because I had no > > > freakin' clue as to why it wouldn't work. So I guess I'm still kind-of at a > > > loss as to what's not working properly. Any other ideas? (Oh, I just > > > remembered - the subform is not actually tied to the parent form. It's a > > > fully independent query that drives it, and I set an initial filter when I > > > open the parent.) > > > > > > Thanks guys! > > > > > > Dennis > > > > > > > > > > > > "Marshall Barton" wrote: > > > > > > > Dennis wrote: > > > > > > > > >In Access2003, I have a subform that I set a filter for on initial lookup. It > > > > >works fine. Then, I want to "re-filter" the data (for example, to limit the > > > > >records displayed to those matching a certain Job Number for a given Tech), > > > > >but the subform doesn't seem to requery. > > > > > > > > > >I've tried the following to no effect: > > > > > > > > > >Me.HoursDetail.Form.Filter = FilterString > > > > > > > > > >where HoursDetail is the name of the subform control on the parent form, and > > > > >FilterString is the text of the filter. If I close and reopen the subform as > > > > >stand-alone, the new filter gets applied properly, but of course that defeats > > > > >the purpose of having a subform. > > > > > > > > > >I have also tried to use: > > > > > > > > > >Me.HoursDetail.Requery > > > > > > > > > >afterward, but also to no effect > > > > > > > > > > > > Did you forget to use: > > > > Me.HoursDetail.Form.FilterOn = True > > > > > > > > -- > > > > Marsh > > > > MVP [MS Access] > > > > . > > > >
From: Dennis on 23 Mar 2010 10:28 Here's the exact code I'm using: Me.HoursDetailNEW.Form.FilterOn = False Me.HoursDetailNEW.Form.Filter = FilterString me.hoursdetailNEW.Form.FilterOn = True Now mind you, the form HoursDetailNEW is the parent, and the subform is bound by a drop-down's selected value. Then if the user pulls up a separate form, they can select additional filtering by JobNumber, StartDate and EndDate, or any combination of those. I recreate the filter text (FilterString) to match the following example's syntax: Tech = 'TA021609160214984001' and JobID = '4312' and (Date GE #3/1/2010# and Date LE #3/22/2010#) I know I could use BETWEEN for the dates, but I'm building the filter string dynamically, and don't know whether or not both dates are being filled in for selection criteria ahead of time. (Oh, I guess I could test that before building the string couldn't I? Funny how just talking about it can help sometimes.) Thanks for trying to help me out on this everyone; I really appreciate it!! Dennis PS: I suppose as a fallback, I could unbind the parent/child relationship of the two forms and do an ADO lookup in the subform, then set "Me.Recordset" = the ADO.Recordset. But to me, that's a kludge... "Douglas J. Steele" wrote: > What's the exact code you're using to set the Filter and FilterOn > properties? > > -- > Doug Steele, Microsoft Access MVP > http://www.AccessMVP.com/DJSteele > (no e-mails, please!) > > "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message > news:69318370-3B14-4025-BACB-BA53B32B0886(a)microsoft.com... > >I don't use the Aplpy Filter button. I want to do everything in VBA. I use > > the ".Filter = " and then the ".FilterOn = True". But I do these from the > > parent form. As I said, it works fine on the initial lookup, but then once > > the subform is opened to the records from the initial Filter statement, I > > cannot load a filter over that one and requery. If I CLOSE the subform, > > THEN > > it works, but now while the subform is OPEN. But when I reopen the > > subform, > > it becomes stand-alone, which is not what I want at all. > > > > Thanks! > > > > "Daryl S" wrote: > > > >> Dennis - > >> > >> No, I should have indicated these were two different things to try. I > >> would > >> make sure the filter was turned on after providing the filter criteria. > >> I > >> assume the filter criteria you are writing is good for the recordsource > >> of > >> the subform, since that data is not related to the parent form. You > >> shouldn't need to requery if you are just applying a filter that is > >> consistent with the records in the subform. > >> > >> When you run your code, does the filter not work? What happens if you > >> toggle the Apply Filter button when the focus is in the subform - can you > >> see > >> the filter being applied and removed? If so, the syntax of the filter > >> string > >> is probably fine, and you may want to try the ApplyFilter of the DoCmd... > >> > >> -- > >> Daryl S > >> > >> > >> "Dennis" wrote: > >> > >> > Daryl, I did the filterOn = True. But do you have to have that set to > >> > true > >> > BEFORE changing the filter text? > >> > > >> > marshall, the reQuery was ineffective. I've done this successfully in > >> > times > >> > past, so I KNOW it's possible. But this thread was started because I > >> > had no > >> > freakin' clue as to why it wouldn't work. So I guess I'm still kind-of > >> > at a > >> > loss as to what's not working properly. Any other ideas? (Oh, I just > >> > remembered - the subform is not actually tied to the parent form. It's > >> > a > >> > fully independent query that drives it, and I set an initial filter > >> > when I > >> > open the parent.) > >> > > >> > Thanks guys! > >> > > >> > Dennis > >> > > >> > > >> > > >> > "Marshall Barton" wrote: > >> > > >> > > Dennis wrote: > >> > > > >> > > >In Access2003, I have a subform that I set a filter for on initial > >> > > >lookup. It > >> > > >works fine. Then, I want to "re-filter" the data (for example, to > >> > > >limit the > >> > > >records displayed to those matching a certain Job Number for a given > >> > > >Tech), > >> > > >but the subform doesn't seem to requery. > >> > > > > >> > > >I've tried the following to no effect: > >> > > > > >> > > >Me.HoursDetail.Form.Filter = FilterString > >> > > > > >> > > >where HoursDetail is the name of the subform control on the parent > >> > > >form, and > >> > > >FilterString is the text of the filter. If I close and reopen the > >> > > >subform as > >> > > >stand-alone, the new filter gets applied properly, but of course > >> > > >that defeats > >> > > >the purpose of having a subform. > >> > > > > >> > > >I have also tried to use: > >> > > > > >> > > >Me.HoursDetail.Requery > >> > > > > >> > > >afterward, but also to no effect > >> > > > >> > > > >> > > Did you forget to use: > >> > > Me.HoursDetail.Form.FilterOn = True > >> > > > >> > > -- > >> > > Marsh > >> > > MVP [MS Access] > >> > > . > >> > > > > > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Cancel Add Mode with Required Fields Next: Enter Parmeter Dialog Box |