Results 1 to 7 of 7
  1. #1
    tufted24 is offline Novice
    Windows 11 Office 365
    Join Date
    Dec 2022
    Posts
    4

    Copy date from Contact date field to Review date field

    I have created a form with a “Contact Date” field that I would like to be able to update “Review Date” field automatically but adding 30 days to the “Review Date” at the time of form completing.
    initially I used a calculated field to do this but then discovered that the “Review Date” could not be changed.


    I think that there is an Expression After Update solution but am too inexperienced to add the VBA code to do this.
    Anyone able to help with this please?
    Thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You need to use the form before insert event, not after update event - (after is too late (record already posted) and update means it will change every time you change anything on the form, whilst insert only occurs the once)

    the code is simple

    me.ReviewDate=me.ContactDate+30

    have to question why you need to store this, easy enough to use a query to determine it as and when required

    Also why 30 days? if the contact date is a Thursday or Friday, in 30 days time the review date will be a Saturday or Sunday. Unless you are a 7 days a week operation, would have thought 28 days would be more appropriate. If you leave it as 30 days, Monday you have double the number of reviews to do from those not done on Saturday/Sunday, whilst Tuesday you will have no reviews

  3. #3
    tufted24 is offline Novice
    Windows 11 Office 365
    Join Date
    Dec 2022
    Posts
    4
    Quote Originally Posted by CJ_London View Post
    You need to use the form before insert event, not after update event - (after is too late (record already posted) and update means it will change every time you change anything on the form, whilst insert only occurs the once)

    the code is simple

    me.ReviewDate=me.ContactDate+30

    have to question why you need to store this, easy enough to use a query to determine it as and when required

    Also why 30 days? if the contact date is a Thursday or Friday, in 30 days time the review date will be a Saturday or Sunday. Unless you are a 7 days a week operation, would have thought 28 days would be more appropriate. If you leave it as 30 days, Monday you have double the number of reviews to do from those not done on Saturday/Sunday, whilst Tuesday you will have no reviews
    Thanks very much. Yes, I have definitively over complicated this by using an extra field on the form. As advised, I am quite new to Access and still learning. Your Query idea seems much better.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You probably still a reviewedondate field so you know the record has been reviewed. If records are due for regular review, this would be in a separate table

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    You could also try to use the after update event of the ContactDate. The ReviewDate will be set for a new entry (unless you use a default value) and when you update the ContactDate.
    Groeten,

    Peter

  6. #6
    tufted24 is offline Novice
    Windows 11 Office 365
    Join Date
    Dec 2022
    Posts
    4
    Quote Originally Posted by CJ_London View Post
    You probably still a reviewedondate field so you know the record has been reviewed. If records are due for regular review, this would be in a separate table
    Thanks again. More useful advice.

  7. #7
    tufted24 is offline Novice
    Windows 11 Office 365
    Join Date
    Dec 2022
    Posts
    4
    Thanks Peter.

    Decided to use a Query instead

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-20-2018, 01:33 PM
  2. Replies: 1
    Last Post: 04-29-2016, 04:03 AM
  3. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  4. Replies: 20
    Last Post: 01-13-2015, 02:23 PM
  5. Replies: 2
    Last Post: 05-22-2014, 06:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums