 |
|
| Computers Forum Index » Computer - Databases - MS Access » Requerying Subform Recordsource... |
|
Page 1 of 1 |
|
| Author |
Message |
| Randy Yates... |
 Posted: Wed Oct 28, 2009 3:40 am |
|
|
|
Guest
|
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.
--
Randy Yates % "So now it's getting late,
Digital Signal Labs % and those who hesitate
mailto://yates at (no spam) ieee.org % got no one..."
http://www.digitalsignallabs.com % 'Waterfall', *Face The Music*, ELO |
|
|
| Back to top |
|
|
|
| pietlinden at (no spam) hotmail.com... |
| Posted: Wed Oct 28, 2009 3:40 am |
|
|
|
Guest
|
On Oct 27, 6:40 pm, Randy Yates <ya... at (no spam) ieee.org> wrote:
Quote: 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.
--
Randy Yates % "So now it's getting late,
Digital Signal Labs % and those who hesitate
mailto://ya... at (no spam) ieee.org % got no one..."http://www.digitalsignallabs.com% 'Waterfall', *Face The Music*, ELO
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. |
|
|
| Back to top |
|
|
|
| Dirk Goldgar... |
| Posted: Wed Oct 28, 2009 4:00 am |
|
|
|
Guest
|
"Randy Yates" <yates at (no spam) ieee.org> wrote in message
news:m3skd4o16s.fsf at (no spam) ieee.org...
Quote: 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) |
|
|
| Back to top |
|
|
|
| Salad... |
| Posted: Wed Oct 28, 2009 4:09 am |
|
|
|
Guest
|
Randy Yates wrote:
Quote: 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. |
|
|
| Back to top |
|
|
|
| Roger... |
| Posted: Wed Oct 28, 2009 9:43 am |
|
|
|
Guest
|
On Oct 27, 5:40 pm, Randy Yates <ya... at (no spam) ieee.org> wrote:
Quote: 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.
--
Randy Yates % "So now it's getting late,
Digital Signal Labs % and those who hesitate
mailto://ya... at (no spam) ieee.org % got no one..."http://www.digitalsignallabs.com% 'Waterfall', *Face The Music*, ELO
in the onCurrent event,
me.subform.form.recordSource = "select top.... where ..." |
|
|
| Back to top |
|
|
|
| Randy Yates... |
| Posted: Thu Oct 29, 2009 5:08 am |
|
|
|
Guest
|
"pietlinden at (no spam) hotmail.com" <pietlinden at (no spam) hotmail.com> writes:
Quote: On Oct 27, 6:40Â pm, Randy Yates <ya... at (no spam) ieee.org> wrote:
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.
--
Randy Yates            % "So now it's getting late,
Digital Signal Labs        %   and those who hesitate
mailto://ya... at (no spam) ieee.org      %   got no one..."http://www.digitalsignallabs.com% 'Waterfall', *Face The Music*, ELO
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.
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://yates at (no spam) ieee.org %
http://www.digitalsignallabs.com % 'Living' Thing', *A New World Record*, ELO |
|
|
| Back to top |
|
|
|
| David W. Fenton... |
| Posted: Thu Oct 29, 2009 5:15 am |
|
|
|
Guest
|
"pietlinden at (no spam) hotmail.com" <pietlinden at (no spam) hotmail.com> wrote in
news:c17f0bf0-74ba-40a1-8d5e-bc254321a550 at (no spam) v36g2000yqv.googlegroups.co
m:
Quote: 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/ |
|
|
| Back to top |
|
|
|
| Dirk Goldgar... |
| Posted: Thu Oct 29, 2009 9:25 am |
|
|
|
Guest
|
"David W. Fenton" <XXXusenet at (no spam) dfenton.com.invalid> wrote in message
news:Xns9CB2EE64CF8A3f99a49ed1d0c49c5bbb2 at (no spam) 74.209.136.91...
Quote: "pietlinden at (no spam) hotmail.com" <pietlinden at (no spam) hotmail.com> wrote in
news:c17f0bf0-74ba-40a1-8d5e-bc254321a550 at (no spam) 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.
--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
(please reply to the newsgroup) |
|
|
| Back to top |
|
|
|
| Randy Yates... |
| Posted: Thu Oct 29, 2009 4:51 pm |
|
|
|
Guest
|
"Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> writes:
Quote: "David W. Fenton" <XXXusenet at (no spam) dfenton.com.invalid> wrote in message
news:Xns9CB2EE64CF8A3f99a49ed1d0c49c5bbb2 at (no spam) 74.209.136.91...
"pietlinden at (no spam) hotmail.com" <pietlinden at (no spam) hotmail.com> wrote in
news:c17f0bf0-74ba-40a1-8d5e-bc254321a550 at (no spam) 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://yates at (no spam) ieee.org %
http://www.digitalsignallabs.com % 'Shangri-La', *A New World Record*, ELO |
|
|
| Back to top |
|
|
|
| David W. Fenton... |
| Posted: Fri Oct 30, 2009 5:15 am |
|
|
|
Guest
|
"Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> wrote in
news:DB029D17-D8A6-4675-8D80-B1B81D15BB26 at (no spam) microsoft.com:
Quote: "David W. Fenton" <XXXusenet at (no spam) dfenton.com.invalid> wrote in message
news:Xns9CB2EE64CF8A3f99a49ed1d0c49c5bbb2 at (no spam) 74.209.136.91...
"pietlinden at (no spam) hotmail.com" <pietlinden at (no spam) hotmail.com> wrote in
news:c17f0bf0-74ba-40a1-8d5e-bc254321a550 at (no spam) 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.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ |
|
|
| Back to top |
|
|
|
| Dirk Goldgar... |
| Posted: Fri Oct 30, 2009 5:15 am |
|
|
|
Guest
|
"David W. Fenton" <XXXusenet at (no spam) dfenton.com.invalid> wrote in message
news:Xns9CB3E278B5771f99a49ed1d0c49c5bbb2 at (no spam) 74.209.136.100...
Quote: "Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> wrote in
news:DB029D17-D8A6-4675-8D80-B1B81D15BB26 at (no spam) 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.
--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
(please reply to the newsgroup) |
|
|
| Back to top |
|
|
|
| Randy Yates... |
| Posted: Fri Oct 30, 2009 11:37 pm |
|
|
|
Guest
|
"Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> writes:
Quote: "David W. Fenton" <XXXusenet at (no spam) dfenton.com.invalid> wrote in message
news:Xns9CB3E278B5771f99a49ed1d0c49c5bbb2 at (no spam) 74.209.136.100...
"Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> wrote in
news:DB029D17-D8A6-4675-8D80-B1B81D15BB26 at (no spam) 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://yates at (no spam) ieee.org % the coin will fall."
http://www.digitalsignallabs.com % 'Big Wheels', *Out of the Blue*, ELO |
|
|
| Back to top |
|
|
|
| David W. Fenton... |
| Posted: Sat Oct 31, 2009 12:36 am |
|
|
|
Guest
|
"Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> wrote in
news:2A0DC097-1D53-4F63-90F1-E80A5ED77291 at (no spam) microsoft.com:
Quote: "David W. Fenton" <XXXusenet at (no spam) dfenton.com.invalid> wrote in message
news:Xns9CB3E278B5771f99a49ed1d0c49c5bbb2 at (no spam) 74.209.136.100...
"Dirk Goldgar" <dg at (no spam) NOdataSPAMgnostics.com.invalid> wrote in
news:DB029D17-D8A6-4675-8D80-B1B81D15BB26 at (no spam) 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.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sat Dec 05, 2009 12:17 pm
|
|