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)have 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_Date". (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.

[NeoPa]

David.

I applaud your intention to help, but am less sanguine about your posting this in spite of the strong advice that the intended solution is a very bad idea. Sometimes, to give someone what they ask for is not an act of kindness.

I'm not sure why you would do so after this has been clearly pointed out.

[zmbd]

There are a few times when storing calculated results can be justified - admittedly very few; however, for some things like discounts that may change over time or in my case where I do store certain correction factors due to legal/contractual obligations.

However, as missinglinq so clearly pointed out, this should not be the normative action.

As shown in the VBA code above, "Re Insp Date" field name must be coded as "Re_Insp_Date". (This is why many suggest you use no spacing in when naming database fields, tables, queries, forms, objects, etc)

Ok this is not an absolute requirement that the spaces be replaced... one need only enclose the names in brackets:

[Re Insp Date 90 Day] = [Re Insp Date] - 90

I do however strongly advise against using spaces in field names etc... as, IMHO, spaces needlessly complicate the coding and there are also other potential issues that can crop up :(

[NeoPa]

zmbd:
There are a few times when storing calculated results can be justified

Normalisation requires that values that can otherwise be calculated are not saved. If the ability to calculate a value is no longer there then saving that value contravenes no normalisation requirements. That would certainly be the case for time-relevant calculations such as you mention.