Help! Creating a macro

[Guest]

I am trying to create a table to include fields that are date/time specific
and each time the date is changed to a month that may be within the next
quarter I want the date to automatically go to that quarter and an
explanation to be required for the change. For example, I have a milestone
that is set for Q1, the date within that quarter is 12/16/06; I need to
change the date to reflect a Q3 date, such as 4/2/07 and I want Access to
force me to give an explanation of why I changed the date and additionally I
want Access to shade or highlight the old date. Is there a way to do this
with a macro?

 

[Guest]

You can do it not from a table but in a form. You can use either a macro or
event procedure.

 

[Guest]

Ok, so how do I do that??? Thanks!
--
Bae

You can do it not from a table but in a form. You can use either a macro or
event procedure.

 

[Guest]

Back up your database.
In design view add an unbound textbox to your form named TEMPDate. Set the
Visible property to No.
Click on your date field. For the On Dirty property enter Macro1.QuarterSet
and create a Macro1 with Macro Name as QuarterSet. Use SetValue as the
action. In the Item enter [Forms]![YourFormName]![TEMPDate] and in
Expression enter [Forms]![YourFormName]![YourDateField ] as this will place
your date in the unbound textbox.
For the After Update property enter Macro1.QuarterChange and in Macro1 add a
Macro Name as QuarterChange.
Enter in the Condition the following –
Format([Forms]![YourFormName]![YourDateField],"yyyyq")<>Format(CVDate([Form]![ YourFormName]![TEMPDate]),"yyyyq")
Use GoToControl as the action. In the Control Name enter your field name
where you want to record the reason for the change. Set the Validation Rule
property to Like “?*†so that entry is required. In the Validation Text
property enter your message.

The action of all this is to store the date that is in the record and
compare it to the update. If it is a different quarter then change the focus
to the field to record the reason.