Hi Phil,
No problem ...
In the scenario I think you are using: You enter a ReviewDate on your form
and want the other control (FollowUpDate, maybe?) to calculate a date 6
months forward. You will need to use a bit of VBA code in two events.
(Don't panic - this is pretty easy.) Here are the steps:
The first event that you will add code to is the AfterUpdate event of the
ReviewDate control. The reason you add code here is so that if the
ReviewDate control is updated, the FollowUpDate control will be
recalculated. Here are the steps:
1. Right click on the ReviewDate control and select Properties.
2. When the Properties sheet opens, click the tab labeled Event.
3. In the grid below, locate the row labeled, After Update. It should be
blank. Click anywhere in the white space to the right of the label and you
will see a downward-pointing arrow appear, indicating that this is also a
ComboBox. Click the arrow and select "Event Procedure".
4. Then, notice that there is an ellipsis or three little dots (...) to the
right of the ComboBox. Click the ellipsis and you will open a code window.
You will see that Access has given you a space for entering some code in
this event - it will look something like the following:
Private Sub ReviewDate_AfterUpdate(Cancel As Integer)
End Sub
5. After the "Private Sub ReviewDate_AfterUpdate(Cancel As Integer)" line,
insert the following:
Me!FollowUpDate = DateAdd("m", 6, Me!ReviewDate)
6. Click the Save icon and close the Microsoft Visual Basic code window.
The second event you will add code to is the Form's On Current event.
This event will cause the FollowUpDate to be calculated if you scroll
through various records *without making a change* to ReviewDate.
1. Still looking at the Properties sheet, notice that there is a combo box
at the top of the window. This combo box contains a list of all of the
objects for which you can see properties.
2. Click the downward-pointing arrow at the right edge of this combo box
and find the item in the list which says "Form".
3. You will now have opened the Properties sheet for your form.
4. Click the "Event" tab and look for the On Current event. It should be
the first event listed. Click anywhere in the white space to the right of
the label and you will see a downward-pointing arrow appear. Click the
arrow and select "Event Procedure". Then, click the ellipsis ( ... ) at
the right edge of the On Current property.
5. A code window will open that looks like the following:
Private Sub Form_Current()
End Sub
6. After the line which says "Private Sub Form_Current()", insert the
following:
Me!FollowUpDate = DateAdd("m", 6, Me!ReviewDate)
7. Click the Save button.
Let us know how this works ...
--
Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX
Big Phil said:
Dear Cheryl, Please bear with me, I have tried to enter the formula in a
form but all i get in the form field is a error,where actualy should I enter
the formula. Thanks Phil
