Get a Max value from a continous form's (subset) records

[efandango]

I have a Mainform and a Subform: (linked not by Master/Child), but by Filter
on the subform using: Run_No=Forms!frm_Sticky_Waypoints!Run_No


(Main Form) frm_Sticky_Waypoints
(SubForm) frm_Sticky_Waypoints_subform (continous)


Each subset contains a field called OrderSeq, this is a number relating to
the (human) record order.

I have two text boxes in the footer that are supposed to find the max and
Min value of OrderSeq for any given subset.

This is the code in the 'High' control:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=[Forms]![frm_Sticky_Waypoints]![Run_No])

But when I run the form, it gives me the Max figure of the overall main
recordset, and not the subset that is currently displayed.

How can I fix this problem?

 

[Tom van Stiphout]

On Sat, 18 Jul 2009 16:28:01 -0700, efandango

I would write that as:
=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])
Alternatively put a textbox in the footer of the subform with a
controlsource of:
=Max(OrderSeq)

-Tom.
Microsoft Access MVP

 

[efandango]

Tom,

I tried both of those suggestions, and each came up with a completely blank
box...?

I had to put a " into your last char on:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No]")

and

Access added [ ] to your '=Max(OrderSeq)' to give '=Max([OrderSeq])'

apart from those changes, I have them exactly as you suggested, but they
just both give a blank, not even #error or the wrong figures, just blank...

On Sat, 18 Jul 2009 16:28:01 -0700, efandango

I would write that as:
=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])
Alternatively put a textbox in the footer of the subform with a
controlsource of:
=Max(OrderSeq)

-Tom.
Microsoft Access MVP

I have a Mainform and a Subform: (linked not by Master/Child), but by Filter
on the subform using: Run_No=Forms!frm_Sticky_Waypoints!Run_No


(Main Form) frm_Sticky_Waypoints
(SubForm) frm_Sticky_Waypoints_subform (continous)


Each subset contains a field called OrderSeq, this is a number relating to
the (human) record order.

I have two text boxes in the footer that are supposed to find the max and
Min value of OrderSeq for any given subset.

This is the code in the 'High' control:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=[Forms]![frm_Sticky_Waypoints]![Run_No])

But when I run the form, it gives me the Max figure of the overall main
recordset, and not the subset that is currently displayed.

How can I fix this problem?

 

[Tom van Stiphout]

On Sun, 19 Jul 2009 01:15:01 -0700, efandango

No, that last doublequote in DMax does not belong. Can you email me a
zipped copy of your database, stripped to the bare essentials, then I
will take another look. My .no.spam trap should be easy to avoid.

-Tom.
Microsoft Access MVP

Tom,

I tried both of those suggestions, and each came up with a completely blank
box...?

I had to put a " into your last char on:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No]")

and

Access added [ ] to your '=Max(OrderSeq)' to give '=Max([OrderSeq])'

apart from those changes, I have them exactly as you suggested, but they
just both give a blank, not even #error or the wrong figures, just blank...

On Sat, 18 Jul 2009 16:28:01 -0700, efandango

I would write that as:
=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])
Alternatively put a textbox in the footer of the subform with a
controlsource of:
=Max(OrderSeq)

-Tom.
Microsoft Access MVP

I have a Mainform and a Subform: (linked not by Master/Child), but by Filter
on the subform using: Run_No=Forms!frm_Sticky_Waypoints!Run_No


(Main Form) frm_Sticky_Waypoints
(SubForm) frm_Sticky_Waypoints_subform (continous)


Each subset contains a field called OrderSeq, this is a number relating to
the (human) record order.

I have two text boxes in the footer that are supposed to find the max and
Min value of OrderSeq for any given subset.

This is the code in the 'High' control:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=[Forms]![frm_Sticky_Waypoints]![Run_No])

But when I run the form, it gives me the Max figure of the overall main
recordset, and not the subset that is currently displayed.

How can I fix this problem?

 

[efandango]

Tom,

Thanks for replying with your solution (which I have pasted below); but the
strange thing is that if I run the form on its own, it works fine; but if I
open it using a button on my main form, the 2nd text box with
'=Max([OrderSeq])' displays no data; just a blank box, also if the main form
is simply open, I get this problem, The frm_Sticky_Waypoints form will only
work properly if it alone is open. Any idea why this behaviour would occur?


(Your solution for the standalone form)

There were two errors:
• When using Max or Sum, you have to use the Fieldname (of the underlying
query), not the Controlname.
• DMax takes three strings as arguments. I hadn’t noticed that the 3rd one
was not a string.
=DMax("[OrderSeq]","tbl_Waypoints",â€[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])

On Sun, 19 Jul 2009 01:15:01 -0700, efandango

No, that last doublequote in DMax does not belong. Can you email me a
zipped copy of your database, stripped to the bare essentials, then I
will take another look. My .no.spam trap should be easy to avoid.

-Tom.
Microsoft Access MVP

Tom,

I tried both of those suggestions, and each came up with a completely blank
box...?

I had to put a " into your last char on:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No]")

and

Access added [ ] to your '=Max(OrderSeq)' to give '=Max([OrderSeq])'

apart from those changes, I have them exactly as you suggested, but they
just both give a blank, not even #error or the wrong figures, just blank...

On Sat, 18 Jul 2009 16:28:01 -0700, efandango

I would write that as:
=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])
Alternatively put a textbox in the footer of the subform with a
controlsource of:
=Max(OrderSeq)

-Tom.
Microsoft Access MVP


I have a Mainform and a Subform: (linked not by Master/Child), but by Filter
on the subform using: Run_No=Forms!frm_Sticky_Waypoints!Run_No


(Main Form) frm_Sticky_Waypoints
(SubForm) frm_Sticky_Waypoints_subform (continous)


Each subset contains a field called OrderSeq, this is a number relating to
the (human) record order.

I have two text boxes in the footer that are supposed to find the max and
Min value of OrderSeq for any given subset.

This is the code in the 'High' control:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=[Forms]![frm_Sticky_Waypoints]![Run_No])

But when I run the form, it gives me the Max figure of the overall main
recordset, and not the subset that is currently displayed.

How can I fix this problem?

 

[efandango]

Tom,

Thanks for replying with your solution (which I have pasted below); but the
strange thing is that if I run the form on its own, it works fine; but if I
open it using a button on my main form, the 2nd text box with
'=Max([OrderSeq])' displays no data; just a blank box, also if the main form
is simply open, I get this problem, The frm_Sticky_Waypoints form will only
work properly if it alone is open. Any idea why this behaviour would occur?


(Your solution for the standalone form)

There were two errors:
• When using Max or Sum, you have to use the Fieldname (of the underlying
query), not the Controlname.
• DMax takes three strings as arguments. I hadn’t noticed that the 3rd one
was not a string.
=DMax("[OrderSeq]","tbl_Waypoints",â€[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])

On Sun, 19 Jul 2009 01:15:01 -0700, efandango

No, that last doublequote in DMax does not belong. Can you email me a
zipped copy of your database, stripped to the bare essentials, then I
will take another look. My .no.spam trap should be easy to avoid.

-Tom.
Microsoft Access MVP

Tom,

I tried both of those suggestions, and each came up with a completely blank
box...?

I had to put a " into your last char on:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No]")

and

Access added [ ] to your '=Max(OrderSeq)' to give '=Max([OrderSeq])'

apart from those changes, I have them exactly as you suggested, but they
just both give a blank, not even #error or the wrong figures, just blank...

On Sat, 18 Jul 2009 16:28:01 -0700, efandango

I would write that as:
=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=" &
[Forms]![frm_Sticky_Waypoints]![Run_No])
Alternatively put a textbox in the footer of the subform with a
controlsource of:
=Max(OrderSeq)

-Tom.
Microsoft Access MVP


I have a Mainform and a Subform: (linked not by Master/Child), but by Filter
on the subform using: Run_No=Forms!frm_Sticky_Waypoints!Run_No


(Main Form) frm_Sticky_Waypoints
(SubForm) frm_Sticky_Waypoints_subform (continous)


Each subset contains a field called OrderSeq, this is a number relating to
the (human) record order.

I have two text boxes in the footer that are supposed to find the max and
Min value of OrderSeq for any given subset.

This is the code in the 'High' control:

=DMax("[OrderSeq]","tbl_Waypoints",[Run_No]=[Forms]![frm_Sticky_Waypoints]![Run_No])

But when I run the form, it gives me the Max figure of the overall main
recordset, and not the subset that is currently displayed.

How can I fix this problem?