Adding 5 working days to a start date

[Santiagoa]

If I set up a task table with an Date_assigned and a number of days to complete the task I calculate the end_date field by using the code below I found in this forum

How ever when I enter the Date_assigned and update the record, nothing happens until I manually enter a value in the DaysToComplete field. I want to keep DaysToComplete Constant (5 Days) so I tried to set the attribute in the table with 5 as the default but this does not work. Is there any way I can get the form to calculate the end date without having to enter the DaysToComplete value manually in the form? Thank you very much for your help

Public Function CountDays(startDate As Date, NoOfDays As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = NoOfDays
    tmpDate = startDate
 
    i = 0
    Do Until i = NoOfDays
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

Private Sub StartDate_AfterUpdate()
    If Not IsNull(Me.NoOfDays) Then
        Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
    End If
End Sub

Private Sub NoOfDays_AfterUpdate()
    If Not IsNull(Me.StartDate) Then
        Me.EndDate = Me.StartDate + CountDays(Me.StartDate, Me.NoOfDays)
    End If
End Sub
 

[NeoPa]

So, you just want to ignore the Me!NoOfDays field completely and use the value 5 in its stead in all situations?

[NeoPa]

If that is the case then you need to use this procedure :

Private Sub StartDate_AfterUpdate()
    If IsNull(Me!StartDate) Then Exit Sub
    Me!EndDate = Me!StartDate + CountDays(Me!StartDate, 5)
End Sub

You can lose the whole NoOfDays control as it's no longer required.

[Santiagoa]

If that is the case then you need to use this procedure :

Private Sub StartDate_AfterUpdate()
    If IsNull(Me!StartDate) Then Exit Sub
    Me!EndDate = Me!StartDate + CountDays(Me!StartDate, 5)
End Sub

You can lose the whole NoOfDays control as it's no longer required.

Thank you very much. This is exactly what I want to do. Unfurtanetly I tried it and I didn't work.

Here is the code I am using.

Public Function CountDays(Date_Assigned As Date, DaysToComplete As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = DaysToComplete
    tmpDate = Date_Assigned
 
    i = 0
    Do Until i = DaysToComplete
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

And this is the code for the Date_Assigned Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me.Date_Assigned) Then Exit Sub
        Me.Turnaround = Me.Date_Assigned + CountDays(Me.Date_Assigned, 5)
    End If
End Sub

[Santiagoa]

Thank you very much. This is exactly what I want to do. Unfurtanetly I tried it and I didn't work.

Here is the code I am using.

Public Function CountDays(Date_Assigned As Date, DaysToComplete As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = DaysToComplete
    tmpDate = Date_Assigned
 
    i = 0
    Do Until i = DaysToComplete
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

And this is the code for the Date_Assigned Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me.Date_Assigned) Then Exit Sub
        Me.Turnaround = Me.Date_Assigned + CountDays(Me.Date_Assigned, 5)
    End If
End Sub

I removed NoOfDays from the task table and renamed EndDate to Turnaround

[Santiagoa]

Do I need to modify the function by removing DaysToComplete?

Sorry I am trying to tweak the code but I don't really understand what it's doing.

here is what I have again

Public Function CountDays(Date_Assigned As Date, DaysToComplete As Integer) As Integer
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim i As Integer
    tmpNo = DaysToComplete
    tmpDate = Date_Assigned
 
    i = 0
    Do Until i = DaysToComplete
        If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
            tmpNo = tmpNo + 1
        Else
            i = i + 1
        End If
        tmpDate = tmpDate + 1
    Loop
 
    CountDays = tmpNo
 
End Function

For the Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me!Date_Assigned) Then Exit Sub
        Me!Turnaround = Me!Date_Assigned + CountDays(Me!Date_Assigned, 5)
End Sub

Sorry Don't mean to bug you with all these questions but I am bit stuck.

[NeoPa]

Do I need to modify the function by removing DaysToComplete?

No!
Your code should be :

Public Function CountDays(Date_Assigned As Date, _
                          DaysToComplete As Integer) As Integer
'Function to return no of days forward to match no of working days passed
Dim tmpDate As Date
Dim i As Integer
    CountDays = DaysToComplete
    tmpDate = Date_Assigned
 
    For i = 1 To DaysToComplete
        If Weekday(tmpDate, vbSaturday) < 3 Then
            CountDays = CountDays + 1
            i = i - 1
        End If
        tmpDate = tmpDate + 1
    Next i
End Function

For the Control

Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me!Date_Assigned) Then Exit Sub
    Me!Turnaround = Me!Date_Assigned + _
                    CountDays(Me!Date_Assigned, 5)
End Sub

[Santiagoa]

I tried the code above exactly as posted but the turnaround box does not update when a date is entered in the Date_Assigned box and the form is refreshed. Thanks for your help

[NeoPa]

Will have to look at this tomorrow. Sorry - I'm out tonight.
Expect rational response then ;)

[Santiagoa]

Will have to look at this tomorrow. Sorry - I'm out tonight.
Expect rational response then ;)

Thank you very much for your help. Have a good night!

[NeoPa]

I tried the code above exactly as posted but the turnaround box does not update when a date is entered in the Date_Assigned box and the form is refreshed. Thanks for your help

What do you mean by "...and the form is refreshed."?
This code should update the Turnaround control immediately the Date_Assigned control has a date entered.

[Santiagoa]

What do you mean by "...and the form is refreshed."?
This code should update the Turnaround control immediately the Date_Assigned control has a date entered.

The date assigned control uses a calendar to assigned the date. I added a refresh data button see if this might help.

[Santiagoa]

What do you mean by "...and the form is refreshed."?
This code should update the Turnaround control immediately the Date_Assigned control has a date entered.

The date_assigned control uses a calendar to assign the date. When I remove the calendar control the code works fine. How can I use the calendar and still get the code to populate the Turnaround control?

[NeoPa]

The date assigned control uses a calendar to assigned the date. I added a refresh data button see if this might help.

You should understand that introducing something new into the mixture, without informing anyone, is likely to cause confusion. It's hard enough to think about what's happening your end without unnotified changes going on.

I need you to explain now, what happened without the refresh data button. I expect that it didn't work, but I need that expressed clearly rather than my trying to read between the lines.

[NeoPa]

The date_assigned control uses a calendar to assign the date. When I remove the calendar control the code works fine. How can I use the calendar and still get the code to populate the Turnaround control?

Sorry, I didn't catch this post earlier (It's still not a good idea to play around with the situation until the earlier problem is resolved). When you say your first sentence above, what do you mean exactly? Does it still use the Date_Assigned control or is the Calendar control used with a different name?
Try to explain exactly what you did to implement the Calendar control into the system.

[Santiagoa]

Sorry, I should have mentioned the calendar control from the onset. Is it possible to achieve the same functionality using the calendar control?

[Santiagoa]

Sorry, I didn't catch this post earlier (It's still not a good idea to play around with the situation until the earlier problem is resolved). When you say your first sentence above, what do you mean exactly? Does it still use the Date_Assigned control or is the Calendar control used with a different name?
Try to explain exactly what you did to implement the Calendar control into the system.

This is how I impemented the calendar control that populates the Date_Assigned control.

Option Compare Database
Option Explicit
Dim Originator As ComboBox
 
Private Sub Calendar6_Click()
Originator.Value = Calendar6.Value
Originator.SetFocus
Calendar6.Visible = False
Set Originator = Nothing
End Sub

Private Sub Date_Assigned_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = Date_Assigned
Calendar6.Visible = True
Calendar6.SetFocus
If Not IsNull(Originator) Then
Calendar6.Value = Originator.Value
Else
Calendar6.Value = Date
End If
End Sub

[NeoPa]

I like your code. I've tweaked it a little for readability and brevity.
However, I couldn't find anything wrong with it except that I thought the Date_Assigned control was a TextBox rather than a ComboBox as defined in your code.

Option Compare Database
Option Explicit
Dim Originator As ComboBox
 
Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me!Date_Assigned) Then Exit Sub
    Me!Turnaround = Me!Date_Assigned + _
                    CountDays(Me!Date_Assigned, 5)
End Sub
 
Private Sub Date_Assigned_MouseDown(Button As Integer,
                                    Shift As Integer,
                                    X As Single,
                                    Y As Single)
    Set Originator = Date_Assigned
    With Calendar6
        .Visible = True
        Call .SetFocus
        .Value = Nz(Originator,Date)
    End With
End Sub
 
Private Sub Calendar6_Click()
    Originator.Value = Calendar6.Value
    Originator.SetFocus
    Calendar6.Visible = False
    Set Originator = Nothing
End Sub
 
Private Function CountDays(Date_Assigned As Date, _
                          DaysToComplete As Integer) As Integer
'Function to return no of days forward to match no of working days passed
Dim tmpDate As Date
Dim i As Integer
    CountDays = DaysToComplete
    tmpDate = Date_Assigned
 
    For i = 1 To DaysToComplete
        If Weekday(tmpDate, vbSaturday) < 3 Then
            CountDays = CountDays + 1
            i = i - 1
        End If
        tmpDate = tmpDate + 1
    Next i
End Function

Let me know if the ComboBox thing is the problem.

[NeoPa]

If that isn't your problem try putting a breakpoint on the line :

If IsNull(Me!Date_Assigned) Then Exit Sub

This should be triggered when the field is updated. Let me know what happens here when you try to select a date from Calendar6.

[Santiagoa]

If that isn't your problem try putting a breakpoint on the line :

If IsNull(Me!Date_Assigned) Then Exit Sub

This should be triggered when the field is updated. Let me know what happens here when you try to select a date from Calendar6.

No, that was not the problem. The control was Date_Assigned is defined as a Combo Box so when the user tries to open the list the calendar opens instead and the user is able to select a date using the calendar.

I still can get the turnaround control to pupulate when I select a date from the calendar.

[NeoPa]

If that isn't your problem try putting a breakpoint on the line :

If IsNull(Me!Date_Assigned) Then Exit Sub

This should be triggered when the field is updated. Let me know what happens here when you try to select a date from Calendar6.

See post #19 (copied here) for the next step.

[Santiagoa]

See post #19 (copied here) for the next step.

I added a break poin in the suggested line but still the turnaround is not populated after the the Date_assigned Combo box is updated using the calendar.

[NeoPa]

Is the breakpoint in the code triggered?

[Santiagoa]

Is the breakpoint in the code triggered?

No, the breakpoint in the code is not triggered

[NeoPa]

In that case (which does surprise me), what do you get if you change the first line of Calendar6_Click() from Originator.Value = Calendar6.Value to Me!Date_Assigned = Me!Calendar6?
This is just a test. Assigning a value to a control is supposed to trigger the AfterUpdate event procedure. I want to see if Access has been confused by referring to it indirectly via an object variable (Originator).

[MMcCarthy]

In that case (which does surprise me), what do you get if you change the first line of Calendar6_Click() from Originator.Value = Calendar6.Value to Me!Date_Assigned = Me!Calendar6?
This is just a test. Assigning a value to a control is supposed to trigger the AfterUpdate event procedure. I want to see if Access has been confused by referring to it indirectly via an object variable (Originator).

If Date_Assigned is a combobox what is the row source of this control and is the Limit to List property set to Yes?

[Santiagoa]

In that case (which does surprise me), what do you get if you change the first line of Calendar6_Click() from Originator.Value = Calendar6.Value to Me!Date_Assigned = Me!Calendar6?
This is just a test. Assigning a value to a control is supposed to trigger the AfterUpdate event procedure. I want to see if Access has been confused by referring to it indirectly via an object variable (Originator).

Not sure if this is what you meant? I changed the bolded line but to what you suggested but nothing happened.

Option Compare Database
Option Explicit
Dim Originator As ComboBox
 
Private Sub Date_Assigned_AfterUpdate()
    If IsNull(Me!Date_Assigned) Then Exit Sub
    Me!Turnaround = Me!Date_Assigned + _
                    CountDays(Me!Date_Assigned, 5)
End Sub
 
Private Sub Date_Assigned_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set Originator = Date_Assigned
    With Calendar6
        .Visible = True
        Call .SetFocus
        .Value = Nz(Originator, Date)
    End With
End Sub
 
Private Sub Calendar6_Click()
    Me!Date_Assigned = Me!Calendar6
    Originator.SetFocus
    Calendar6.Visible = False
    Set Originator = Nothing
End Sub
 
Private Function CountDays(Date_Assigned As Date, _
                          DaysToComplete As Integer) As Integer
'Function to return no of days forward to match no of working days passed
Dim tmpDate As Date
Dim i As Integer
    CountDays = DaysToComplete
    tmpDate = Date_Assigned
 
    For i = 1 To DaysToComplete
        If Weekday(tmpDate, vbSaturday) < 3 Then
            CountDays = CountDays + 1
            i = i - 1
        End If
        tmpDate = tmpDate + 1
    Next i
End Function
 
Private Sub Date_Fulfilled_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = Date_Fulfilled
Calendar6.Visible = True
Calendar6.SetFocus
If Not IsNull(Originator) Then
Calendar6.Value = Originator.Value
Else
Calendar6.Value = Date
End If
End Sub
 
Private Sub Date_Received_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = Date_Received
Calendar6.Visible = True
Calendar6.SetFocus
If Not IsNull(Originator) Then
Calendar6.Value = Originator.Value
Else
Calendar6.Value = Date
End If
End Sub

[NeoPa]

You got that right.
But I don't understand why the Date_Assigned_AfterUpdate() procedure is not being activated :confused:
Can you try one last thing for me before we give up on doing it properly and bodge the code to effect the required result.
Can you put a breakpoint on the first line of the Date_Assigned_AfterUpdate() procedure (If IsNull(Me!Date_Assigned) Then Exit Sub).
Now try running through the test (with the current code for the moment) and tell us if that breakpoint is triggered (If so it will stop the code and highlight that line in yellow).
Does that make sense?

[Santiagoa]

You got that right.
But I don't understand why the Date_Assigned_AfterUpdate() procedure is not being activated :confused:
Can you try one last thing for me before we give up on doing it properly and bodge the code to effect the required result.
Can you put a breakpoint on the first line of the Date_Assigned_AfterUpdate() procedure (If IsNull(Me!Date_Assigned) Then Exit Sub).
Now try running through the test (with the current code for the moment) and tell us if that breakpoint is triggered (If so it will stop the code and highlight that line in yellow).
Does that make sense?

I added the breakpoint at the point you indicated but nothing happens, the control opens the calendar and allows me to select a date which populates the Date_Received text box but does not populate the turnaround.

[NeoPa]

The code below completely replaces the code you showed in post #27.
Try it out and see if it fixes your problems.

Option Compare Database
Option Explicit
Private Originator As ComboBox
 
Private Sub Calendar6_Click()
    With Originator
        .Value = Me!Calendar6
        Call .SetFocus
        Me!Calendar6.Visible = False
        If .Name = "Date_Assigned" And Not IsNull(.Value) Then _
            Me!Turnaround = .Value + CountDays(.Value, 5)
    End With
    Set Originator = Nothing
End Sub
 
Private Sub Date_Assigned_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call EnableCal(cboDate:=Me!Date_Assigned)
End Sub
 
Private Sub Date_Fulfilled_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call EnableCal(cboDate:=Me!Date_Fulfilled)
End Sub
 
Private Sub Date_Received_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call EnableCal(cboDate:=Me!Date_Received)
End Sub
 
Private Sub EnableCal(cboDate As ComboBox)
    Set Originator = cboDate
    With Me!Calendar6
        .Visible = True
        Call .SetFocus
        .Value = Nz(Originator, Date)
    End With
End Sub
 
'Function to return no of days forward to match no of working days passed
Private Function CountDays(ByVal StartDate As Date, _
                           ByVal DaysToComplete As Integer) As Integer
    Dim intX As Integer
 
    CountDays = DaysToComplete
    For intX = 1 To DaysToComplete
        If Weekday(StartDate, vbSaturday) < 3 Then
            CountDays = CountDays + 1
            intX = intX - 1
        End If
        StartDate = StartDate + 1
    Next intX
End Function

It appears from your testing that the AfterUpdate event of the Date_Assigned control does not get triggered simply if the item is updated, but only if it's updated via the form itself. This is completely different from how the similar events work in Excel.
Please let us know how you get on with this.