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,362
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,362
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,362
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,767
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,082,167
Messages
5,363,532
Members
400,747
Latest member
monty_gl

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top