Change calculated fields

Question

Hello,

I have a project form with a few calculated dates with an initial setup (start date, date 2 = start date + 2 weeks, date 3 = start date + 4 weeks, etc..). I would like to be able to change the dates manually and recalculate al the next dates (say changing date 2 , means date 3 etc.. will also be changed, but start date stays as it was), but I also want to be able to only change one date at the time without changing all the others!

Is there a way to do this in VBA? Thanks very kindly in advance.

Regards,

Bernard

Answer 1

Hi Bernard,

If you don't want the other controls to auto-calculate, you'll have to use "unbound" controls and assign the date you want in them one at a time. When you want to update the dates, you can freely update only the ones you need to change.

Just my 2 cents...

Answer 2

To do this at all, the date controls must be unbound, not calculated controls, so they can be updated independently.  They can, and probably would, have default values set.

If you wanted to *always* update all the "downstream" dates whenever an upstream date was changed, it would be simple.  You'd use the AfterUpdate event of each date field to set the new values of all dates that are downstream of it.  You could either do this directly, updating each control, or you could have each date text box's AfterUpdate event update the date control that follows it in sequence, and then call that following date control's AfterUpdate event.

However, if you *also* want to be able to update just one date without updating the downstream dates, you have to give the user some way to indicate that that's what they want, instead of the default behavior -- or else you would have to make single-date updating the default behavior, and have some way to indicate that the downstream dates should be updated. 

This is essentially a user-interface problem, not a technical problem, and I'm not sure what would be the best way to set that up. Maybe you'd have a check box to indicate whether date changes should cascade down or not.  Then your code in the AfterUpdate event of each date control would test that check box before proceeding to update the ones downstream.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Hi theDBguy,

Thanks for your answer. I can do that, but I also need the dates to automatically recalculate if I don't want to change manually. The manual update would be an exception.

So if I change for instance (manually) date 2, than I would date 3, date 4, etc.. to automatically recalculate after update. If I change date 3 (manually), I want date 4, date 5, etc.. to automatically recalculate.

Regards,

Bernard

Answer 4

Hi,

As long as you can code the logic behind the auto-updates you want to happen, then it shouldn't be a problem using VBA for it. It's when the rules become complicated that you might need some human intervention to make things happen properly.

So, if you use unbound controls, you can simply use the AfterUpdate event of each one to make the changes to the succeeding controls automatic.

Cheers!

Answer 5

If you wish to change or insert the values of Date 2, Date 3 etc independently then they must be columns (fields) in the table, not calculated fields.  In your data entry form, in the AfterUpdate event procedure of the control bound to the Date 1 column assign a value to Date 2 and call the AfterUpdate event procedure of the Date 2 control, subject to user confirmation, e.g.

    Const MESSAGE_TEXT = "Automatically update Date 2?"

    If MsgBox(MESSAGE_TEXT, vbYesNo, "Confirm" = vbYes Then
        Me.[Date 2] = DateAdd("ww",2,Me.[Date 1])
        Date_2_AfterUpdate
    End If

In the AfterUpdate event procedure of the control bound to the Date 2 column assign a value to Date 3 subject to user confirmation:

    Const MESSAGE_TEXT = "Automatically update Date 3?"

    If MsgBox(MESSAGE_TEXT, vbYesNo, "Confirm" = vbYes Then
        Me.[Date 3] = DateAdd("ww",2,Me.[Date 2])
        Date_3_AfterUpdate
    End If

    ' and so on for other dates bar the last.

---

Ken Sheridan, Stafford, England

Answer 6

Thanks Dirk,

I was also thinking in that direction.

Regards,

Bernard

Answer 7

If you wish to change or insert the values of Date 2, Date 3 etc independently then they must be columns (fields) in the table, not calculated fields. 

Ken, I was thinking these were header controls used for filtering, and so would be unbound; but you're right, if they are detail fields then they need to be bound to fields in the table.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

Thank You kindly, I think with your information I can figure it out. I will let you know if I have succeded.

Regards,

Bernard

Answer 9

Hi Bernard,

Good luck!

Answer 10

Is there a way to do this in VBA? Thanks very kindly in advance.

Hi BernVer,

The way I would handle this, is to have a unbound form with the 4 date-controls. These can be prefilled if necessary, and changed individually.

On the form you can have a couple of command buttons (or some other menu thing) that can set the dates in a predefined way:

- n weeks increment per date from startdate
- n weeks increment from the previous date
- ...
- ...

To supply a value for "n", there could be an additional control on the form.

Imb.

Finally a Submit-button to store the dates in the record.

Answer 11

Thanks!

Regards,

Bernard

Answer 12

Thank you and thank all of you for your help.

Regards,

Bernard

Answer 13

Ken, I was thinking these were header controls used for filtering, and so would be unbound; but you're right, if they are detail fields then they need to be bound to fields in the table.

I was assuming each row represents a project and each date represents a scheduled stage in the project.  This is not a good design for a relational database table, of course, but that's another story.

---

Ken Sheridan, Stafford, England

Answer 14

Just to let you know. I have succeded with the construction of the form. Using the after_update_event of the date-control and vbYesNo message to let the user choose between updating all the following fields or only the particular field. I also have four classes (class 1/2 and class 3/4) wich use different periods between the subsequent stages. So in the after_update_event of the control on the form I use an If..Then construction to call the subs with the calculation from a seperate module (either class 1/2 or class 3/4).

Thanks again for your support, it helped me tremendously.

Regards,

Bernard

Answer 15

Hi Bernard,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.