This code is driving me mad!!

[svensonpiep]

Please take a look at the following codes:

Private Sub date1_BeforeUpdate(Cancel As Integer)
If [date1] <> &quot;&quot; Then
[date2] = DateAdd(&quot;d&quot;, 15, date1)
Else: [date2] = Null
End If

End Sub


what I am trying to accomplish seems very simple.
If date1 is null or empty, then leave date2 blank as well.
Now, assume that date1 is blank, it leaves date2 blank.
This part works fine from the code above.
But if date1 has a date value, then add 15 days to it and show the new date in date2.

What is really annoying me is if date1 has a date let's say,
02/01/2002, the only way it adds 15 days is if you click perform some operations on date1.
If date1 is 02/01/2002 and you remove the 01 and put it back, or you remove 01 and replace it with another number, the date updates fine in date2.
What I really want is to see 15 days added to date1 and updated in date2 if date1 is not null.
I have used Before_update, after_update, onclick, on enter.
Nothing is working.
I hope I am clear in my explanation. I need help!!

 

[JohnLowell]

Try putting the code in the Form_Current event. Every time a row of data is put on the form, the Form_Current event runs.

 

[straydog]

Are you manually entering in the value for date1. If so then your code should work fine. In the After Update event. I'd also place the same in the On Change event as well. You could also do without the &quot;If&quot; statement and just use:

Private Sub Date1_AfterUpdate(Cancel As Integer)
Me.Date2.Value = DateAdd(&quot;d&quot;, 15, Date1)
End Sub

If the value for date1 is an already stored value in a table you can create an expression in your query and use that field as your Date2 field.

Like so:

Date2: DateAdd(&quot;d&quot;,15,[Date1])

hope this helps,
PDeeney

 

[svensonpiep]

thanks for the response!
i have decided to adopt johnlowell's recommendation.
straydog, the problem with your recommendation is that if date1 is blank and date2 has a date value, it doesn't get updated and more so, date2 should not have a date unless date1 has one. at least that is their requirement.
in this case, therefore, an if statement is needed to flush out any date values in the date2 when date1 is blank.
thanks again to you all.