How do I assign a default date value to one control based on the date of another??

[msignor]

I have two date controls in my main form. I would like the default value of the second date control to reflect 7 days prior to the first control. I know what event I should put the code in I just don't know what to put. Does anybody have any ideas?

[NeoPa]

Try :

Private Sub txtDate1_AfterUpdate(Cancel As Integer)
    With Me
        .txtDate2.DefaultValue = IIf(IsDate(.txtDate1, _
                                     DateAdd("d", -7, CDate(.txtDate1)), _
                                     Null)
    End With
End Sub

[msignor]

It wouldn't compile but I figured out what I needed by tinkering around. My two controls are examCompDate (the first date) and examEffDate (the second). I didn't think it would be this simple. However, without your code I wouldn't have thought to do this. Thank You!!

Private Sub examCompDate_AfterUpdate()
[examEffDate].Value = [examCompDate].Value - 7
End Sub

[NeoPa]

Of course it won't compile without the proper control names in there. You didn't share that information in your question, so I had to put in something in their places to make it obvious what you needed to do.

Your code is thoroughly wrong though, as it doesn't allow for there being no date in the first control (we now know to be called [examCompDate]) or non-date data being entered into it and, although adding day values to dates can be done using integer arithmetic, that is not the recommended way for good reason. Also, your question asked for how to set the default value of the other control, which your code also doesn't manage properly.

Unless you now need something different from what you originally asked for, what you need is :

Private Sub examCompDate_AfterUpdate(Cancel As Integer)
    With Me
        .examEffDate.DefaultValue = IIf(IsDate(.examCompDate), _
                                        DateAdd("d", -7, CDate(.examCompDate)), _
                                        Null)
    End With
End Sub

... which is what I said in post #2 but which you didn't properly understand.

[msignor]

Oh I knew to replace the control names. I'm sorry I probably should've said so in my last post. They're both date/time data types so no worries about putting in anything other than the required info. It turns out I did actually just want it to set the value and not the default value(although now I have it set for both) :). I have the format on both controls set to short date. I didn't think of the null value so I tried it and all the second date does is return a null value so no worries there. Thanks again!!

[NeoPa]

In light of that I was able to determine that I had actually posted suggested code with an error. It was a missing closing parenthesis for the IsDate() call (which I've now fixed retrospectively). Apologies for that.

It sounds like you have already considered most of the issues, but let me just bring up a few considerations for you anyway, in case you missed any.

1. Are both of the controls bound (IE. Connected to fields from the data source of the form)?
   1. If they are both bound then you need to be careful about assigning a value to [examEffDate] in case one already exists. Overwriting a value indiscriminately makes little sense unless you always want the value to reflect seven days prior to [examCompDate]. I hope this isn't the case because then you're getting into issues of normalisation (and in this case non-conformant design to the concept - See Database Normalisation and Table structures). To assign a value only when the control doesn't already contain a value requires slightly changed code, but at least makes sense within a well-designed database.
   2. If [examCompDate] is unbound, then assuming that the value of it within your procedure is a valid date is not reliable unless you have configured the control to ensure this is the case (The Format property has absolutely no effect on this of course, but other properties can do - particularly, but not exclusively, ValidationRule).
   3. If [examEffDate] is unbound then there should be no issues.
   From the fact that you state :
   

   MSignor:
   They are both Date/Time types

   I would surmise they are actually both bound (as controls are not typeable in that way so you must be referring instead to the bound fields). The linked article explains why that's not too good an idea, but I'll leave you to explore the details of that.
2. The only other point really is the advisability of using standard arithmetic on date values. It works - There's no question of that. Is it a reliable way to think though? A habit of using that will leave you exposed to writing code that is both less readable (If you don't know already you will come to experience how important an issue is the clear readability and sense of code.) and potentially less reliable. It may well be that allowing ordinary numeric values is precluded somewhere in your design, but can you guarantee you will never be in a situation where none slip through in other areas of your code when you follow a similar approach with dates? Frankly, the clincher for me is the readability and maintainability of your code, so I always feel it's worth using the date/Time specific functions.

Ultimately you make your own choices. I simply want to bring some ideas to your attention for your awareness which I hope prove useful. Good luck.

[msignor]

You were right. They're both bound. I got it to compile by removing the "Cancel As Integer" and then I realized after it didn't change the value (in the current record) on the second date control because it was still set to defaultvalue which is what I originally asked for and am still going to use now along with being able to alter the value if necessary. If at the very least I will use your code as to not confuse someone else down the road. Thanks for all the time you put into this thread. This was my first thread on here but next time I will make sure to be more specific. :)

[NeoPa]

MSignor:
This was my first thread on here but next time I will make sure to be more specific. :)

If that's the only thing you learn from this experience then it's a gold mine. You can get so much more out of this site with that understanding :-)