Requerying Subform Recordsource
Randy Yates
the filter established by the Link Master/Child fields is applied.
However, the query I use in the recordsource in my subform needs to
incorporate the filter inside the query. This is because I'm
using a TOP predicate and the TOP must operate on the filtered
data, not the entire recordset.
So I need a way to rerun the subform query everytime the main form
generates a new Link Master/Child value (e.g., when the main form's
navigation button is used to advance to the next record).
I cannot find such a method. If someone knows a good way to do this,
I'd appreciate a hint.
--
Randy Yates % "So now it's getting late,
Digital Signal Labs % and those who hesitate
mailto://ya...@ieee.org % got no one..."
http://www.digitalsignallabs.com % 'Waterfall', *Face The Music*, ELO
pietl...@hotmail.com
You need to put code to requery the subform in the OnCurrent() event
of your form.
Something like
Me!Subform1.Requery
where "Subform1" is the name of the subform you want to requery.
Dirk Goldgar
news:m3skd4o...@ieee.org...
> Usually a subform's recordsource grabs ALL records it needs and then
> the filter established by the Link Master/Child fields is applied.
>
> However, the query I use in the recordsource in my subform needs to
> incorporate the filter inside the query. This is because I'm
> using a TOP predicate and the TOP must operate on the filtered
> data, not the entire recordset.
>
> So I need a way to rerun the subform query everytime the main form
> generates a new Link Master/Child value (e.g., when the main form's
> navigation button is used to advance to the next record).
>
> I cannot find such a method. If someone knows a good way to do this,
> I'd appreciate a hint.
You can use the main form's Current event to requery the subform:
'----- start of example code -----
Private Sub Form_Current()
Me.sfMySubform.Requery
End Sub
'----- end of example code -----
Assuming the subform's RecordSource query refers to the linking control on
the main form as a criterion, you can leave the Link Master Fields and Link
Child Fields properties of the subform control blank. However, if the
subform is to be updatable, you would need to use code in the subform's
BeforeInsert or Dirty event to pick up the appropriate value for the foreign
key field from the parent form.
--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
(please reply to the newsgroup)
Salad
> Usually a subform's recordsource grabs ALL records it needs and then
> the filter established by the Link Master/Child fields is applied.
>
> However, the query I use in the recordsource in my subform needs to
> incorporate the filter inside the query. This is because I'm
> using a TOP predicate and the TOP must operate on the filtered
> data, not the entire recordset.
>
> So I need a way to rerun the subform query everytime the main form
> generates a new Link Master/Child value (e.g., when the main form's
> navigation button is used to advance to the next record).
>
> I cannot find such a method. If someone knows a good way to do this,
> I'd appreciate a hint.
One can do something like this format
Forms!MainForm!Subform.Form.Filter = "blah blah"
Forms!MainForm!Subform.Form.FilterOn = True
Change Filter to OrderBy and you can sort as well.
Roger
in the onCurrent event,
me.subform.form.recordSource = "select top.... where ..."
Randy Yates
Thanks all. OnCurrent is the event to use - my how much I've forgotten!
--
Randy Yates % "Rollin' and riding and slippin' and
Digital Signal Labs % sliding, it's magic."
mailto://ya...@ieee.org %
http://www.digitalsignallabs.com % 'Living' Thing', *A New World Record*, ELO
David W. Fenton
news:c17f0bf0-74ba-40a1...@v36g2000yqv.googlegroups.co
m:
> You need to put code to requery the subform in the OnCurrent()
> event of your form.
>
> Something like
> Me!Subform1.Requery
>
> where "Subform1" is the name of the subform you want to requery.
This is one of two such answers. I don't believe it is correct,
though it may actually work. I would say the correct answer is:
Me!Subform1.Form.Requery
You don't want to requery the subform control, but the actual form
embedded in the subform control.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dirk Goldgar
news:Xns9CB2EE64CF8A3f9...@74.209.136.91...
> "pietl...@hotmail.com" <pietl...@hotmail.com> wrote in
> news:c17f0bf0-74ba-40a1...@v36g2000yqv.googlegroups.co
> m:
>
>> You need to put code to requery the subform in the OnCurrent()
>> event of your form.
>>
>> Something like
>> Me!Subform1.Requery
>>
>> where "Subform1" is the name of the subform you want to requery.
>
> This is one of two such answers. I don't believe it is correct,
> though it may actually work. I would say the correct answer is:
>
> Me!Subform1.Form.Requery
>
> You don't want to requery the subform control, but the actual form
> embedded in the subform control.
Requerying the subform control requeries the form it displays. This is
documented behavior of the Requery method.
Randy Yates
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9CB2EE64CF8A3f9...@74.209.136.91...
>> "pietl...@hotmail.com" <pietl...@hotmail.com> wrote in
>> news:c17f0bf0-74ba-40a1...@v36g2000yqv.googlegroups.co
>> m:
>>
>>> You need to put code to requery the subform in the OnCurrent()
>>> event of your form.
>>>
>>> Something like
>>> Me!Subform1.Requery
>>>
>>> where "Subform1" is the name of the subform you want to requery.
>>
>> This is one of two such answers. I don't believe it is correct,
>> though it may actually work. I would say the correct answer is:
>>
>> Me!Subform1.Form.Requery
>>
>> You don't want to requery the subform control, but the actual form
>> embedded in the subform control.
>
>
> Requerying the subform control requeries the form it displays. This
> is documented behavior of the Requery method.
David, Dirk:
Both good to know/refresh my memory. Thank you.
--
Randy Yates % "My Shangri-la has gone away, fading like
Digital Signal Labs % the Beatles on 'Hey Jude'"
mailto://ya...@ieee.org %
http://www.digitalsignallabs.com % 'Shangri-La', *A New World Record*, ELO
David W. Fenton
news:DB029D17-D8A6-4675...@microsoft.com:
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9CB2EE64CF8A3f9...@74.209.136.91...
>> "pietl...@hotmail.com" <pietl...@hotmail.com> wrote in
>> news:c17f0bf0-74ba-40a1...@v36g2000yqv.googlegroups
>> .co m:
>>
>>> You need to put code to requery the subform in the OnCurrent()
>>> event of your form.
>>>
>>> Something like
>>> Me!Subform1.Requery
>>>
>>> where "Subform1" is the name of the subform you want to requery.
>>
>> This is one of two such answers. I don't believe it is correct,
>> though it may actually work. I would say the correct answer is:
>>
>> Me!Subform1.Form.Requery
>>
>> You don't want to requery the subform control, but the actual
>> form embedded in the subform control.
>
>
> Requerying the subform control requeries the form it displays.
> This is documented behavior of the Requery method.
Perhaps so. I would never do it that way, since I think it
constitutes misleading code. If you have to comment it to explain
it, it's not well-written.
Dirk Goldgar
> "Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in
> news:DB029D17-D8A6-4675...@microsoft.com:
>>
>> This is documented behavior of the Requery method.
>
> Perhaps so. I would never do it that way, since I think it
> constitutes misleading code. If you have to comment it to explain
> it, it's not well-written.
I don't think it's misleading in the least. Microsoft has defined in the
help file exactly what the Requery method does when applied to various
controls. This is not an obscure side effect, but one of the method's
explicit, primary behaviors. Requerying a subform control does what most
people would expect it to do, and so needs no comment to explain it.
Randy Yates
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9CB3E278B5771f9...@74.209.136.100...
>> "Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in
>> news:DB029D17-D8A6-4675...@microsoft.com:
>>>
>>> Requerying the subform control requeries the form it displays.
>>> This is documented behavior of the Requery method.
>>
>> Perhaps so. I would never do it that way, since I think it
>> constitutes misleading code. If you have to comment it to explain
>> it, it's not well-written.
>
>
> I don't think it's misleading in the least. Microsoft has defined in
> the help file exactly what the Requery method does when applied to
> various controls. This is not an obscure side effect, but one of the
> method's explicit, primary behaviors. Requerying a subform control
> does what most people would expect it to do, and so needs no comment
> to explain it.
I tend to prefer your method, Dirk, because a) it's simpler, and b) it
also works for other controls that are not based on a form, such as a
combobox. It's a requery that "thing", whatever the thing is. If that
requires requerying something underlying that "thing", then let the
control manage that.
--
Randy Yates % "...the answer lies within your soul
Digital Signal Labs % 'cause no one knows which side
mailto://ya...@ieee.org % the coin will fall."
http://www.digitalsignallabs.com % 'Big Wheels', *Out of the Blue*, ELO
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9CB3E278B5771f9...@74.209.136.100...
>> "Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in
>> news:DB029D17-D8A6-4675...@microsoft.com:
>>>
>>> Requerying the subform control requeries the form it displays.
>>> This is documented behavior of the Requery method.
>>
>> Perhaps so. I would never do it that way, since I think it
>> constitutes misleading code. If you have to comment it to explain
>> it, it's not well-written.
>
> I don't think it's misleading in the least. Microsoft has defined
> in the help file exactly what the Requery method does when applied
> to various controls. This is not an obscure side effect, but one
> of the method's explicit, primary behaviors. Requerying a subform
> control does what most people would expect it to do, and so needs
> no comment to explain it.
We will agree to disagree. A subform control, unlike a combo box or
a listbox, does not return a recordset, so it is not really the
object that you're requerying -- you are requerying the form
embedded in the subform control, and I believe that should be
specified in your code, not because it's required, but because it's
better practice to do exactly what you intend, just in case the
implementation that guesses what you want should someday develop a
bug.
To me, this is similar to explicitly coercing data types in your
code, or relying on VBA's wide versatility in implicitly coercing
data types. Depending on implicit VBA features works great right up
until the point where it doesn't, so if you know you're using a
shortcut that depends on implicit VBA behavior, I think you're
better of making explicit what you're assuming will always happen
correctly via VBA's implicit features.