Auto populate textbox with date according to date in other textbox

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
142
I have a form with a button that puts the beginning and ending date in two textboxes. The start date is named Start_Date_Corp and the stop date is named Stop_Date_Corp. I would like to have another textbox that looks at the Start date and populates with a date that is 24 months prior to that date. Below is the VBA I have but I cannot make it work correctly. When I push the 2013 the first two boxes are correct - Start_Date_Corp 1/1/2013 -- Stop_Date_Corp 12/21/2013. The start date textbox I have for 24 months prior displays 1/1/1898. How can I make this box come up with the correct date? Or maybe just have that textbox look at the start date and then update using a function on the form?


Below is my VBA

Function Set_Metrics_24_month_Corp()

Dim Start_Date_Corp As Date
Dim Stop_Date_Corp As Date

Corp_Metric_2Yr_Stop = DateSerial(Year(Stop_Date_Corp), Month(Stop_Date_Corp) + 1, 0) + TimeSerial(23, 59, 59)
Corp_Metric_2Yr_Start = DateSerial(Year(Start_Date_Corp), Month(Start_Date_Corp) - 23, 1)

Forms![frm_Main]![NavigationSubform].Form![Corp_Metric_2Yr_Start].Value = Corp_Metric_2Yr_Start
Forms![frm_Main]![NavigationSubform].Form![Corp_Metric_2Yr_Stop].Value = Corp_Metric_2Yr_Stop

End Function
 

kbrummert

New Member
Joined
Jan 7, 2018
Messages
27
Do you have both fields formatted as mm/dd/yyyy? I played with this and it looks like it may be reading the source box wrong due to different formatting. Otherwise, it worked fine.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,393
Office Version
365
Platform
Windows
Use DateAdd to add the required months forward/backwards.

For example to get the date 24 months prior to the date in Start_Date_Corp use this.
Code:
DateAdd("m", -24, Start_Date_Corp.Value)
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
142
I changed the format to mm/dd/yyyyy and still came up with the year dates of 1898. The formatting was Short Date and that does not work either. Not sure why this does not work.
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
142
Not sure where to put the DateAdd at. I put it in the Textbox using Expression and it did not work.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,393
Office Version
365
Platform
Windows
I'm not sure where to put it either because I can't quite figure out what the posted code is meant to do.

When do you want the other textbox to be populated based on what's entered in Start_Date_Corp?
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
142
After the Start date box has been updated. I tried refreshing the page but it was not updating. I have buttons for each year that populate the start and stop dates. After the dates are populated I would like the 24 month box to populates
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,393
Office Version
365
Platform
Windows
You should look into using the AfterUpdate event of the Start_Date_Corp textbox, or add the code to the buttons that populate the start/stop dates.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,782
One form is on a navigation form and the other is not? AFAIK, you can only load one form at a time from a navigation form, which ought to mean one of yours isn't open when this code runs. Not clear if more than one form is involved since you don't indicate what Corp_Metric_2Yr_Stop or Corp_Metric_2Yr_Start are. Variables? Form controls? If the former, are they declared somewhere and still available to this procedure? Or are you not using Option Explicit for all your modules? If controls, I'm surprised this would work as written without the form reference (Me. if code is on the same form as the control, or the complete Forms! reference if not, assuming the referenced form is open). You need to say where the code is located and provide more info about the number of forms involved and those variables (or whatever they are). Or step through the code and check that the values are present and correct. You might be subtracting/adding date values from some unexpected value. The only other thing I can think of is that your regional settings for Dates are coming into play.
 

Forum statistics

Threads
1,085,715
Messages
5,385,419
Members
401,945
Latest member
Paul82

Some videos you may like

This Week's Hot Topics

Top