DateDiff to populate date from one form to another

[JoeJr]

Hello Access Folk!

Current situation: I have two forms which are "frmDateCal" and "frmDatePicker".
In the "frmDateCal" I have (LastDate), (ReportType), and (NewDate).
In the "frmDatePicker" I have two dates pickers which are (StartDate) and (EndDate)

In the "frmDateCal" (lastdate) is manual entry and when the user selects 'Two' in the (ReportType) the "frmDatePicker" opens to select a date range. I am trying to figure out how to code it so that when the date ranges are selected in the "frmDatePicker" it takes the number of days in between those dates and add that to the (LastDate) + 365 to populate the (NewDate).


Here is the code that I have so far in the "frmDateCal"

Private Sub ReportType_AfterUpdate()


Select Case ReportType


  Case 1 'Form One selected
    
    Me.NewDate = DateAdd("d", 364, Me.LastDate)
    
  Case 2 'Form Two selected
  
  DoCmd.OpenForm "frmDatePicker"
  
  Case 3 'From Three selected
 
  Me.NewDate = DateAdd("d", 406, Me.LastDate)
  
  End Select


End Sub

I dont have anything yet in the "frmDatePicker" yet. I am not sure if that is were I would put the datediff code and then some how store the number of days between the two to be pulled into the code in the "frmdateCal" to populate the (NewDate)

Attached is the database that I am working on.

DatePicker.zip

Thank you

[June7]

Need to open frmDatePicker so code execution on first form is suspended. I use:

DoCmd.OpenForm "frmDatePicker", , , , , acDialog

Then options are for frmDatePicker to set value of a control on first form and close itself or first form pulls value from frmDatePicker and closes it. Example of latter:

'user prompt for entry of logout date
DoCmd.OpenForm "DialogGetDate", , , , , acDialog, "Logout"
If CurrentProject.AllForms("DialogGetDate").IsLoaded Then
    Form_SampleManagement.tbxDate = Form_DialogGetDate.tbxDateDialog
    DoCmd.Close acForm, "DialogGetDate", acSaveNo
End If

[JoeJr]

Need to open frmDatePicker so code execution on first form is suspended. I use:

DoCmd.OpenForm "frmDatePicker", , , , , acDialog

Then options are for frmDatePicker to set value of a control on first form and close itself or first form pulls value from frmDatePicker and closes it. Example of latter:

'user prompt for entry of logout date
DoCmd.OpenForm "DialogGetDate", , , , , acDialog, "Logout"
If CurrentProject.AllForms("DialogGetDate").IsLoaded Then
    Form_SampleManagement.tbxDate = Form_DialogGetDate.tbxDateDialog
    DoCmd.Close acForm, "DialogGetDate", acSaveNo
End If

Thank you for the fast response. Just to make sure I understand correctly. The supplied code that you provided, I would put the within the Case 2 selection under the afterUpdate Code for Report type?

[June7]

Yes. That's the only place where frmDatePicker is invoked and a value is needed from it.

[JoeJr]

Yes. That's the only place where frmDatePicker is invoked and a value is need from it.

Okay I updated the code and something is not working correctly. Here is my code based on what you supplied.

Private Sub ReportType_AfterUpdate()


Select Case ReportType


  Case 1 'Form One selected
    
    Me.NewDate = DateAdd("d", 364, Me.LastDate)
    
  Case 2 'Form Two selected
  
  DoCmd.OpenForm "frmDatePicker", , , , , acDialog, "Logout"
  If CurrentProject.AllForms("frmDatePicker").IsLoaded Then
  frmDateCal.NewDate = frmDatePicker.tbxDateDialog
  DoCmd.Close acForm, "frmDatePicker", acSaveNo
  End If


  Case 3 'From Three selected
 
  Me.NewDate = DateAdd("d", 406, Me.LastDate)
  
  End Select


End Sub

After looking at the code, I notice that its not pulling the number of days between the two selected dates from the Date picker form. When the Date picker form opens the user selects the start date and end date then when the form closes, the amount of days between the two selected dates should be added to the (LastDate) + 365.

example: X days pulled from "frmDatePicker" + "LastDate" + 365 = "NewDate".

[June7]

Code does not quite follow example syntax. Several options for referencing forms. If you look at objects listed in VBE, you will see prefixes like Form_, Report_.

Form_frmDateCal.NewDate

or

Forms!frmDateCal.NewDate

or

Me.NewDate

Me can be used as alias for form the code is behind.

[JoeJr]

Code does quite follow my syntax. Several options for referencing forms. If you look at objects listed in VBE, you will see prefixes like Form_, Report_.

Form_frmDateCal.NewDate

or

Forms!frmDateCal.NewDate

or

Me.NewDate

Me can be used as alias for form the code is behind.

Okay. Here is my take on it again but not sure if I have it correctly placed code wise. Again when the user selects 'Form Two' in the "FormType" within the Form_frmDateCal; the Form_frmDatePicker opens as a Dialog for the user to select a start and end date. Then the number of days between the two dates will be added to the lastDate within the Form_frmDateCal plus additional 365 days.

Private Sub ReportType_AfterUpdate()


Select Case ReportType


  Case 1 'Form One selected
    
    Me.NewDate = DateAdd("d", 364, Me.LastDate)
    
  Case 2 'Form Two selected
  
  DoCmd.OpenForm "frmDatePicker", , , , , acDialog, "Logout"
  Dim DValue As Integer
  DValue = DateDiff("d", frmDatePicker.StartDate, frmDatePicker)
  If CurrentProject.AllForms("frmDatePicker").IsLoaded Then
  frmDateCal.NewDate = DValue + frmDateCal.LastDate + 365
  DoCmd.Close acForm, "frmDatePicker", acSaveNo
  End If


  Case 3 'From Three selected
 
  Me.NewDate = DateAdd("d", 406, Me.LastDate)
  
  End Select


End Sub

[JoeJr]

Okay I got an error on my code. It is not liking the DValue section of code that I have.

[JoeJr]

I am thinking that when the frmDatePicker Dialog form opens and the user selects the start and end dates the DateDiff should be stored there some how and then pulled into the code under the frmDateCal user form to calculate the NewDate

[JoeJr]

Code does quite follow example syntax. Several options for referencing forms. If you look at objects listed in VBE, you will see prefixes like Form_, Report_.

Form_frmDateCal.NewDate

or

Forms!frmDateCal.NewDate

or

Me.NewDate

Me can be used as alias for form the code is behind.

I changed it to read as you stated but the New date is still not populating

   
  Case 2 'Form Two selected
  
  DoCmd.OpenForm "frmDatePicker", , , , , acDialog, "Logout"
  If CurrentProject.AllForms("frmDatePicker").IsLoaded Then
  Dim DValue As Integer
  DValue = DateDiff("d", Form_frmDatePicker.StartDate, Form_frmDatePicker.EndDate)
  Form_frmDateCal.NewDate = DValue + Form_frmDateCal.LastDate + 365
  DoCmd.Close acForm, "frmDatePicker", acSaveNo
  End If

[June7]

Missing EndDate reference for the second date in DateDiff. Just referencing form is not enough.

I left out "not" in my earlier post.

[JoeJr]

Missing EndDate reference for the second date in DateDiff. Just referencing form is not enough.

I left out "not" in my earlier post.

Thanks I noticed that and fixed and still no luck. After I select the StartDate and EndDate in the Form_frmDatePicker and hit the okay button, the form closes and the NewDate in the Form_frmDateCal is still not populating.

[JoeJr]

Should I have any code on the Form_frmDatePicker dialog to hold the DateDiff or should that code remain on the main form "Form_frmDateCal" ?

[June7]

Sorry, I forgot about code behind DialogGetDate that you will need to adapt in button Click event:

    If IsNull(Me.tbxDateDialog) Then
        MsgBox "Enter date."
    Else
        Me.Visible = False
    End If
Me.tbxDateDialog.SetFocus

I also have a Cancel button:

Private Sub btnCancel_Click()
Me.tbxDateDialog = Null
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

[JoeJr]

Do you have code behind frmDatePicker that closes itself? If so, remove it.

The only thing that is on the frmDatePicker is for the "Ok" button which when clicked it closes the form. Should I remove the button?