possible to pull dates from a sub-form?

[Guest]

i have a main form with a text field that is for "last appt. date", another
form for appt. history. on my appt. history form is a sub-form where i can
enter the client's entire appt history.

my question, is it possible to make the "last appt. date" text field to look
at the appt. history sub-form and pull the very last appt date and show it on
the main form in the text field?

 

[Al Camp]

Martin,
In the footer of your subform create a calculated text control with a
ControlSource of...
= Max(ApptDate)
Name that field MaxApptDate for example.

On your main form, place an unbound calculated TextControl with a
ControlSource of...
= frmYourSubName.Form!MaxApptDate
Name that field MainMaxStartDate.

This will display the MaxStartDate value from the subform on the main
form, and update whenever the subform appts change.
You'll need to do a Refresh on the AfterUpdate event of ApptDate in the
subform to force a recalc.

 

[Guest]

I appreciate your help...the first part is working.

I renamed the date field to "servicedate". I created a text control
"MaxApptDate" in the footer of the subform with the controlsource of
=Max[(servicedate)]. This field now shows the final and most current date.

The problem is with the second part on the main form.

I created the unbound text control and created the ControlSource as

=Forms![Call Listing Subform]!MaxApptDate

But when I open the main form I just have #Name? showing in the field...HELP
if you can...Thankyou in advance.

MARTIN

 

[Douglas J Steele]

I'm assuming that [Call Listing Subform] is the name of the form that you're
using as a subform on your main form, and that you're trying to access it
from that main form.

In actual fact, if it's being used as a subform [Call Listing Subform] isn't
recognized by Access as being open. To refer to something on it while it's
being used as a subform, you have to refer to it as:

Forms![NameOfMainForm]![NameOfSubformContainer].Form!MaxApptDate

Note that [NameOfSubformContainer] may or may not be [Call Listing Subform].
It all depends on how you created the subform. If you dragged [Call Listing
Subform] onto the main form, then the container is probably named [Call
Listing Subform]. If you created the subform by selecting the subform
container from the tool box, drawing it onto the main form then supplying
the necessary information (either manually or using the wizard), then the
container's probably named something like Child0 (where 0 will vary).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I appreciate your help...the first part is working.

I renamed the date field to "servicedate". I created a text control
"MaxApptDate" in the footer of the subform with the controlsource of
=Max[(servicedate)]. This field now shows the final and most current date.

The problem is with the second part on the main form.

I created the unbound text control and created the ControlSource as

=Forms![Call Listing Subform]!MaxApptDate

But when I open the main form I just have #Name? showing in the field...HELP
if you can...Thankyou in advance.

MARTIN

Martin,
In the footer of your subform create a calculated text control with a
ControlSource of...
= Max(ApptDate)
Name that field MaxApptDate for example.

On your main form, place an unbound calculated TextControl with a
ControlSource of...
= frmYourSubName.Form!MaxApptDate
Name that field MainMaxStartDate.

This will display the MaxStartDate value from the subform on the main
form, and update whenever the subform appts change.
You'll need to do a Refresh on the AfterUpdate event of ApptDate in the
subform to force a recalc.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions