How to add auto date based on date entered in another box 2

[suddu]

Hi Everybody. Thanks for all the wonderful and most helpful forum for access.
Here is my problem and I hope somebody will be able to help me.

I have a form and a subform, there is a field anmed Date Enrolled on the subform and then there are 3 more date fields like threemonthappointment and ninemonthappointment and oneyearappointment. All these three fields should be able to populate themselves based on the entry in Date Enrolled field on the form.

Here is the kicker , all these was working fine when the date enrolled field was on the main form and rest of the fields were on the subform. Then I was using in the default value for all three boxes the query which worked fine. It was Forms!Demographics![Date Enrolled]+90 for 3 months and so on. And I was able to get my values. But when the structure was modified to put the Date Enrolled field on the subform and modifying the expresion accordingly it didnot work . I have also tried various other suggestions on this forum but may be I am not able to apply them correctly or do not fit what i need.

Any and all the help will be greatly appreciated.

 

[THWatson]

suddu
Have you tried the DateAdd function?

On the AfterUpdate event for your [Date Enrolled] control put something like...
Dim dteTemp As Date
dteTemp = DateAdd("m", 3, [Date Enrolled])
Me.threemonthappointment = dteTemp

If that works for your 3 month control, then expand the formula to include...
Dim dteTemp2 as Date
Dim dteTemp3 as Date
dteTemp2 = DateAdd("m", 9, [Date Enrolled])
dteTemp3 = DateAdd("yyyy", 1, [Date Enrolled])
Me.ninemonthappointment = dteTemp2
Me.oneyearappointment = dteTemp3

Tom

 

[willir]

suddu

A better function would be the DateAdd...

dtToday = DateAdd("m", 3, dtToday)

Type of thing.

DateDiff and DateSerial are also important when working with dates.

Something to consider...
What if the booking date falls on a weekend?

You can use the WEEKDAY function to check this and tweak the booking date accordingly. For example

Select CASE WEEKDAY(me.3MonthAppointment)
   Case 1
   'Sunday
   me.3MonthAppointment = dateadd("d", 1, me.3MonthAppointment)

   Case 7
   'Saturday
   me.3MonthAppointment = dateadd("d", 2, me.3MonthAppointment)

End Select

Richard

 

[suddu]

Dear Watson, tried your suggestions but it didnot produce any results. I included the code as provided on the after update event. it reads as:

Private Sub Date_Enrolled_AfterUpdate()

Dim dteTemp As Date
dteTemp = DateAdd("m", 3, [Date Enrolled])
Me.threemonthappointment = dteTemp

End Sub

Any help will be greatly apreciated

 

[THWatson]

suddu
Okay...please clarify for me...

Are all of the fields on the subform?
[Date Enrolled], threemonthappointment, ninemonthappointment, oneyearappointment...all on the subform?

Also, are these the exact names of the controls?

Tom

 

[suddu]

yes now they are and yes how they appear.

 

[THWatson]

suddu
Good. Now, one more thing...

When you go into the subform and enter a date in [Date Enrolled] what happens next? Do you tab to another field? Do you go to the next record?

The code I gave you is correct. But I need to know where to put the code.

Tom

 

[CajunCenturion]

I would take heed of the suggestion made by those above with respect to using the DateAdd function. The reason is that adding 30 days and adding 1 month are not the same thing.

Jan 1 + 30 Days = Jan 31
Jan 1 + 1 Month = Feb 1

willir's comments about checking for weekdays is also quite good, especially if weekends are not valid days for appointments.

That being said, I'd like to attack the problem from the other side - that being the actual ControlSource for the various date controls and the actual formula being used.
Originally, you used the expression Forms!Demographics![Date Enrolled]+90 to add 90 days (not necessarily 3 months). What are the full expressions that you're using now? What is the name of the SubForm control as it appears on the MainForm? That can be found on the property pages for the SubForm, Other Tab, Name value.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[suddu]

I do not do tab out of that field instead i have a button to add the appointment date to outlook and that waht i click when the date shows up. Which it has not so far. Let me also explain to u what the date enrolled field is like its a date\time field with a combobox feature which on the mouse down event opens a calender and then it selects the date to be entered into that field, now i was looking to auto populate the othe three fields the moment the date is selected and enetered in the date Enrolled field.

Thanks

 

[THWatson]

suddu
Okay, that's a different picture from what I had before.

Do I have it correct now, as follows...
[Date Enrolled], threemonthappointment, ninemonthappointment and oneyearappointment are all Date/Time fields. You place a value in your combo box, and then click a command button.

If that is correct, then put this code on your command button...
Dim dteTemp As Date
Dim dteTemp2 As Date
Dim dteTemp3 As Date
Dim dteTemp4 As Date
dteTemp = Me.YourComboBoxName
Me.Date_Enrolled = dteTemp
Me.threemonthappointment = DateAdd("m", 3, dteTemp)
Me.ninemonthappointment = DateAdd("m", 9, dteTemp)
Me.oneyearappointment = DateAdd("yyyy", 1, dteTemp)

Some further things...
1. Where is the command button? On the main form or on the subform? If on the main form, we may have to reference the date controls by adding the subform name.
2. Cajun Centurion is right about checking to make sure that the "name" of the conrols is as you show it - the name, not the control source.
3. Will the code have to be modified to take into consideration weekends?

Tom

 

[suddu]

Hi Cajun,
I ahve also tried this:

=[Forms]![Demographics]![Visits]![DateEnrolled]+90 but it doesn't work now and neither does Tom's remedy . I am desperate for the solution. Please help

 

[CajunCenturion]

Is [Visits] the name of the control which contains the subform?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[suddu]

Thanks Dear Watson,

1. The names are correct as i see it.
2. The command button is not for pulling the date function but to add the dates to outlook oncethey show up in their respective fields.
3.The command button is also on the subform named Visits which is on the main form named Demographics.
4.And no i donot care about weekends.

Thanks

 

[suddu]

I donot know what the control is for the form I created the form seperatly and puuled it into the Demographics forms, So i understand that becomes a subform and when I click on properties on the main form I get Subform/subreports: Visits. Also the name and control source both show the same name as Visits.

Thanks

 

[CajunCenturion]

A subform control does not have a Control Source property. It does have a Source Object property. Is that what you mean?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[suddu]

Hi Cajun, i am sorry that is what i mean.

 

[CajunCenturion]

And I'm assuming that =[Forms]![Demographics]![Visits]![DateEnrolled]+90 is the Control Source for the textbox ninemonthappointment on the Visits subform.

Have you tried doing a
[Demographics]![Visits]![ninemonthappointment].Requery
as the first thing inside the Click event handler for the button pressed to add the appointments?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[suddu]

Its not the control source but the default value for the threemonthappointment and ~+270 for the ninemonthappointment. That is how i achieved it before when the field was on two different forms and I dinot need the requery macro as the value always defaulted to the value in the DateEnrolled + any increment.

 

[CajunCenturion]

:-D +- 270 - Nice catch.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[suddu]

Dear Watson, I am still looking for it to work but has not worked so far.
thanks