Auto Populate 1 year after input date

[NLillith]

Okay, so I already looked at the other thread when someone had asked something very similar and the answer was not exactly what I was looking for, so I'm going to ask this:

I have a form where a date is put in by the user who is entering the data. It's called Backup Date. On the same form, I have another date field called "date due back" what I want the "date due back" to do is I want it to auto populate one year after the Backup Date.

I'd rather do this in code form so that I can get it to run properly, but when I was looking at the answer in another thread called "need to auto-populate for form fields based on the same form"

The code did not seem like it would work for what I am looking for, so I am posting this.

 

[boblarson]

If the result is always a year after the date that was entered in Backup Date then you should NOT be storing this data in the table. You use a QUERY to calculate it when you need it. You can use a query in 99% of the places you would use a table so it should not be a problem to do it there. You can create a base query that has that calculation in it and use that.

 

[NLillith]

If the result is always a year after the date that was entered in Backup Date then you should NOT be storing this data in the table. You use a QUERY to calculate it when you need it. You can use a query in 99% of the places you would use a table so it should not be a problem to do it there. You can create a base query that has that calculation in it and use that.

Bob,

I appreciate the fast response, though the date due back has to be in the table that the form connects to for record keeping purposes, so I need it to do what I specified earlier. This database was pre-built by the person who wanted it done this way and it is one of those things that they are very particular and want it done a certain way.

This person is insisting on the current amount of tables being there and only one query.

 

[boblarson]

I hope they do understand that what they are asking for is not best practice and it also puts the data at risk of not being correct. If the date gets entered and the value is entered for the year later then someone somehow changes the original date via a query or the table directly then the data would be incorrect as the second field would not have been updated.

But that is their problem. You can put this in the after update event of the control which has the original date input:

Me.ControlNameWithYearLater = DateAdd("yyyy", 1, Me.ControlNameWithOriginalDateHere)

 

[NLillith]

Bob,

Thank you for the source code, I appreciate it.

 

[NLillith]

Okay, so I was able to get it to populate to do what it is that I wanted it to do, which was auto populate 1 year after the date, but now, the person I am coding this for informed me that the "end of the year" backup tapes are kept indefinitely.

So in the VB script, I'm looking to have an IF statement to exclude certain data.

For example:

IF "Backup_date_text = End of the Year"
Then "Date_due_back = null or "indefinite" and I'm not entirely sure how to work it in code form.

I'm good with C++ and Java, but give me VB and I'm completely lost.

Where should I start with this IF statement?

 

[NLillith]

So I got a bit of a start on it (with some help) and now I'm getting a 2158 error.

Here is the original code that works:

Private Sub Date_Due_Back_Click()
Me.Date_Due_Back = DateAdd("yyyy", 1, Me.Backup_Date)
End Sub

Here's what someone who helped me come up with:

Private Sub Date_Due_Back_Click()
Me.Date_Due_Back.Text = IIf(InStr(1, Me.Backup_Date_Text.Text, "end of the year", vbTextCompare) <> 0, "Never", DateAdd("YYYY", 1, Me.Backup_Date.Text))
End Sub

 

[boblarson]

Do NOT use the .TEXT propertly. Just use

[FONT=Calibri][SIZE=3]Me.Date_Due_Back = IIf(InStr(1, Me.Backup_Date_Text, "end of the year", vbTextCompare) <> 0, "Never", DateAdd("YYYY", 1, Me.Backup_Date))[/SIZE][/FONT]

Use the default VALUE property instead of .TEXT. About the only time you use .TEXT is if you are using the On Change event of the particular control because it has to have the focus when using that and the .VALUE property does not. The .Value doesn't need to be specified because it is the default.

 

[NLillith]

Thank you for fixing it Bob.