Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-22-2022, 09:02 PM
kcaron kcaron is offline How to tell if a legacy control changed Windows 10 How to tell if a legacy control changed Office 2021
Novice
How to tell if a legacy control changed
 
Join Date: Oct 2022
Posts: 3
kcaron is on a distinguished road
Default How to tell if a legacy control changed

I am creating a Promissory Note form using legacy controls so I can calculate monthing payments based on loan origination, first payment date, final payment date and rate.

I initialize these fields in the Document_New sub.
The monthly payment is calulated when a loan/note amount is entered.
All this is working fine

I would like to recalulate if any of the dates change with the Exit macro:

loan origination:
If > first payment, then adjust first payment date to the beginning of the next


month and adjustthe final payment date for number of periods. (monthly payment
is not changed since Loan Amoritization just shifts left/later)

first payment date:
If changed, recalulate final payment date (again monthly payment is not affected)

final payment date:
If changed, recalculate number of payment periods, and recalculate monthly
payments.

I am trying to find a way to tell if the dates changed when leaving the field.

I tried global declarations of values, but found out lifetime of these variables is only valid while a sub/function is active.

I am looking at static variables to save previous values. Is there a better way?

Thanks,
Ken
Reply With Quote
  #2  
Old 10-22-2022, 11:36 PM
macropod's Avatar
macropod macropod is offline How to tell if a legacy control changed Windows 10 How to tell if a legacy control changed Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

All these calculations can be done using field coding and having the 'calculate on exit' property set for each of the input formfields. See the Financial Calulations topic in my Microsoft Word Field Maths Tutorial, which is in a 'Sticky' thread at the top of this page: https://www.msofficeforums.com/word/...-tutorial.html

And, to see how to do just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, which is in a 'Sticky' thread at the top of this page: https://www.msofficeforums.com/word/...-tutorial.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 10-23-2022, 11:29 AM
kcaron kcaron is offline How to tell if a legacy control changed Windows 10 How to tell if a legacy control changed Office 2021
Novice
How to tell if a legacy control changed
 
Join Date: Oct 2022
Posts: 3
kcaron is on a distinguished road
Default

Thanks macropod

This is good information! I have downloaded both documents. Lots of good reference material, but I can't find anything on being able to determine if the field changed or how to change several other fields because this one changed. I think I could see a way to calculate the payment with field texts, but what happens if the user overrides the field that is being calculated?

My initial goal was to populate the dates when this document is created from a template (.dotm or .dotx), all based on the current date, unless that date has a valid date (like when the document is revisited in a follow on edit session.) Word fields are great when doing calculations and format manipulations. I don't see how to use that to set other fields based on the the value of this. I know I can reference a value and use if-then-else to calculate and manipulate the value, but then it is not an enterable field on the form. My initial dates are "suggestions".

IE: If the 1st payment date is changed, I could use a word field to display a last payment date, but don't see how to change the legacy field value for the last payment date.
Reply With Quote
  #4  
Old 10-23-2022, 01:59 PM
macropod's Avatar
macropod macropod is offline How to tell if a legacy control changed Windows 10 How to tell if a legacy control changed Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by kcaron View Post
This is good information! I have downloaded both documents. Lots of good reference material, but I can't find anything on being able to determine if the field changed or how to change several other fields because this one changed.
The functionality you seek is built into Word itself. And, as I clearly said:
Quote:
Originally Posted by macropod View Post
All these calculations can be done using field coding and having the 'calculate on exit' property set for each of the input formfields
Quote:
Originally Posted by kcaron View Post
what happens if the user overrides the field that is being calculated?
In a document with 'filling in forms' protection applied - which is required when using formfields - what you describe simply isn't possible unless one breaks the document into Sections and leaves Section containing the formula fields unprotected. In Sections of a document with 'filling in forms' protection applied, only the form fields (not any formula fields or other fields) are accessible for selection or user input.

For a fairly simple demonstration of formfields in action, see: https://www.msofficeforums.com/171152-post2.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 10-25-2022, 09:21 PM
kcaron kcaron is offline How to tell if a legacy control changed Windows 10 How to tell if a legacy control changed Office 2021
Novice
How to tell if a legacy control changed
 
Join Date: Oct 2022
Posts: 3
kcaron is on a distinguished road
Default Clarification

I'm a bit confused between field codes {F9, a-F9, & c-F9} and field controls.

I've tried field code several times and rarely get them to work. I tried to calculate the end of the loan term by using the VBA function DateAdd("M", StartDate, nper), where StartDate and nper are numeric Text Form Fields. Can field codes reference these? I tried something like:
{ =DATAADD("m", {REF StartDate }, {REF nper} ) \@ "MM/dd/yyyy" }

But get
!Syntax Error, (

Sooo, instead I did everthing with Legacy Text Form Fields and the Exit field.
What I was initially asking, is there a way in the exit sub to tell if the formfield was actually changed?

My hack was to create Static variables then compare that static var value to the formfield value:
ActiveDocument.FormFields("StartDate").Result
if different, I update other FormFields, then before leaving, copy the current formfield value into the static variable.

This is mostly working but somewhere the static vars are re-initialized (Using the debug.print command)

(My previous message was trying to use field codes instead of formfields exit sub
Ken
Reply With Quote
  #6  
Old 10-26-2022, 05:00 AM
macropod's Avatar
macropod macropod is offline How to tell if a legacy control changed Windows 10 How to tell if a legacy control changed Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You are confusing VBA with field coding - the two are entirely different. DateAdd is not a valid field code function. I have already given you links showing how to calculate dates in just about every conceivable way.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Legacy Form SYE Word 4 07-26-2019 05:16 AM
How can I set up tab to go back and forth between legacy fields and content control fields in a Word Ikajed Word 1 10-13-2017 06:06 PM
How to tell if a legacy control changed legacy Form Field sunrise06 Word 9 05-02-2015 06:48 PM
How to tell if a legacy control changed Comparing dates in legacy control text box JoeE Word VBA 2 05-08-2013 11:01 PM
Can't open .pst legacy files RetiredEngineer Outlook 1 11-14-2009 01:06 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft