Trying to set a date field to autofill based on a another date field

[klbrownie]

Hello,

I am trying to get a date field to autofill based on the date of another date field. I want the autofilled date to be 30 days after the other date. For example, if the user has entered 06/15 in the first date field, I want the second field to automatically populate with 7/15. I have tried everything I can think of to no avail! I can't imagine this is a difficult thing to do........

Anyone have any ideas?

[ADezii]

Assuming your 2 Date Fields are [Date1] and [Date2], in the AfterUpdate() Event of [Date1], place the following code. When finished, it should look like:

Private Sub Date1_AfterUpdate()
  'Is there a Value in [Date1] AND is it a Valid Date?
  If Not IsNull(Me![Date1]) And IsDate(Me![Date1]) Then
    Me![Date2] = DateAdd("d", 30, Me![Date1])
  End If
End Sub

[MMcCarthy]

In the after update event of datefield1 put the following code ...

Me!datefield2 = DateAdd("d", 30, Me!datefield1)

[MMcCarthy]

LOL! Snap

[klbrownie]

Thanks ADezii!

I was trying something close to that, but apparently ooooh soooo far off!!! :)

[NeoPa]

The answer depends on how you intend to use this field to my mind. If you're entering new data then it's probably better to set the .DelaultValue property of the control. If it's simply for display, then set the .ControlSource to :

=IIf([DateField1] Is Null,Null,DateAdd('d',30,[DateField1]))

[klbrownie]

@NeoPa
NeoPA, I see what you were talking about now. The date field is populating on the form, but it not in the table. How do get the date into the table as well?

[missinglinq]

You don't! Very seldom is there a valid reason to store a calculated value in a table! It wastes storage space, and running a calculation is almost sure to take less time than retrieving a value form the disk.

Instead, you merely re-calculate the value as needed, in a form, report or query.

Welcome to Bytes!

Linq ;0)>

[NeoPa]

What he just said :)

[davidmacias]

Not sure if this will help but I had a similar issue and this is what I did....

ACTION DESIRED: Have one date field trigger (calculate) future or past date values in other date fields in a form and (Most importantly)hav e those values save in the master database table and queries.

TRIGGER FIELD: Re Insp Date

AFFECTED CALCULATED FIELDS: Re Insp Date 90 Day (To be set to -90 from "Re Insp Date") Re Insp Date 60 Day (To be set to -60 from "Re Insp Date") Re Insp Date 30 Day (To be set to -30 from "Re Insp Date")

SOLUTION: Opened up the properties of what is to be the "trigger" date field went to 'All' tab Opened up a 'Code Builder' Event Procedure in the "After Update" property Entered the following VBA code

   Private Sub Re_Insp_Date_AfterUpdate()
   Re_Insp_Date_90_Day = Re_Insp_Date - 90
   Re_Insp_Date_60_Day = Re_Insp_Date - 60
   Re_Insp_Date_30_Day = Re_Insp_Date - 30
 
   End Sub

NOTE: If there are spaces in the name fields, an underscore has to replace the spaces. As shown in the VBA code above, "Re Insp Date" field name must be coded as "Re_Insp_Da te". (This is why many suggest you use no spacing in when naming database fields, tables, queries, forms, objects, etc)

Hopefully this helps someone else in the future.

Anyway, there it is.