Auto populate dates that can be overwitten? (1 Viewer)

[CupKat]

Access noob here
I have a form for worker timesheet information, with a continuous subform for entering days worked in a specific period. The subform has a period start date and a period end date, both currently manually entered. Most, but not all of the time, the period start will be a Monday and the period end will be the following Sunday. I would like for period end to automatically set to 6 days following the period start, but to be able to manually overwrite this (for some workers it's 1st of the month until the last day of the month).
Even better would be if, once the period start has been set on one row, it not only fills in period end to be 6 days later, but also sets the next row to be the start of the following week (again with the option to overwrite this, ideally). Is this possible, and if so, how would I go about it? I have played around a little bit with DateAdd() but not found a way to make this work yet. Any ideas would be most appreciated.

 

[MajP]

on the start period after update event

If isNull(me.EndPeriod) then me.endPeriod = me.startPeriod + 6

For the consecutive rows I would make a small function to find the max endPeriod date for that worker. It might be something like

Public GetMaxEnd(EmployeeID as long) as date
  GetMaxEnd = nz(dMax("EndPeriod","tblWithDates", "EmployeeID = " & employeeID),Date())
end date

Then in the on current event

me.startPeriod.defaultvalue = GetMaxEnd(me.Parent.employeeID) + 1

I assume this is the form with a subform and the employeeID is on the mainform

 

[MajP]

FYI dates are not stored like you think. They are a number with an integer part and the decimal part. The integer part is the days since 1/31/1899
So todays date is actually
44959
and tomorrow is
44960
so you can simply add a number of days to a date. Do not need date add for that.

Time is a fraction of a day so todays date time (8:05 AM)
44959.3374421296

.25 of a day is 6 am
.333 is 8am
.337 is 8:05 am
.5 is 12 pm
.75 is 6 pm

So adding months, years, minutes, second, hours is easier with dateadd.

 

[MajP]

If you do both of these and the start has a default value, you may never update the start. So the original code for the endperiod would have to be also called from the end period enter, but now you have to check that the start period is not null

So in both the startperiod after update and endperiod on enter call

If isNull(me.EndPeriod) and NOT isnull(me.StartPeriod) then me.endPeriod = me.startPeriod + 6

The one issue with setting these default and start values is you end up creating a record if the user moves to a new record. So I would also add validation code in the forms before update to ensure all other fields are filled in and allow the user to cancel if not. The case where they moved to a new record, but did not mean to create one.

 

[Pat Hartman]

For Jet/ACE, the origin date is 12/30/1899. It might be 12/31 for SQL Server. It really doesn't matter though. It is the concept that matters.

Rather than setting the default value in the current event, I would use the BeforeInsert event and actually populate the field. The BeforeInsert event runs as soon as someone types the first character in any field and dirties the form with a NEW record. You could use the dirty event for this instead but then you would need to use an If to determine if this was a new record or an existing record so using the correct event avoids that issue. The code in the BeforeInsert event then becomes:

Me.startPeriod = GetMaxEnd(me.Parent.employeeID)

 

[MajP]

I demoed this to see what works best.
1. The before insert has problems in that it requires the user to enter something in some field. It Would work if there was other fields to enter data in and you had to enter first (ex. charge code. I have to enter a charge code before I can enter my dates). But if the first field you come to is the start period field then this does not do anything. This also does nothing when the user comes back and updates the start period.
2. But concur the default property and on current is definitely not the way to go. You want the user to do something on the new record like physically move into it with mouse or tab.
3. I put this in the onEnter and afterupdate of the start and on enter of the endperiod as a failsafe.
4. I added a field to the main table to determine if this person reports monthly or weekly.
5. Added some helper functions

Public Function GetMaxEnd(EmpID As Long) As Date
  GetMaxEnd = Nz(DMax("EndPeriodDate", "tblEmployeeDates", "EMPID_FK = " & EmpID), 0)
End Function
Public Function GetMondayInWeek(dtmdate As Date) As Date
  GetMondayInWeek = dtmdate - (Weekday(dtmdate, vbMonday) - 1)
End Function
Public Function GetEndOfMonth(dtmdate As Date) As Date
  GetEndOfMonth = DateSerial(Year(dtmdate), Month(dtmdate) + 1, 0)
End Function
Public Function GetFirstOfMonth(dtmdate As Date) As Date
  GetFirstOfMonth = DateSerial(Year(dtmdate), Month(dtmdate), 1)
End Function
Public Function GetFirstOfNextMonth(dtmdate As Date) As Date
  GetFirstOfNextMonth = DateSerial(Year(dtmdate), Month(dtmdate) + 1, 1)
End Function

Main form code

Private Sub EndPeriodDate_Enter()
  SetEnd
End Sub
Private Sub StartPeriodDate_AfterUpdate()
  SetEnd
End Sub

Private Sub StartPeriodDate_Enter()
  SetStart
  SetEnd
End Sub

Private Sub SetStart()
Dim StartDate As Date
  Dim MaxEnd As Date

  MaxEnd = GetMaxEnd(Me.Parent.EmpID)
  'First record then MaxEnd returns 0
  If MaxEnd = 0 Then
    Select Case Me.Parent.scheduleType
    Case "Weekly"
      StartDate = GetMondayInWeek(Date)
    Case "Monthly"
      StartDate = GetFirstOfMonth(Date)
    End Select
  Else
  'All other records
  Select Case Me.Parent.scheduleType
    Case "Weekly"
      StartDate = MaxEnd + 1
    Case "Monthly"
      StartDate = GetFirstOfNextMonth(MaxEnd)
    End Select
  End If
  Me.StartPeriodDate = StartDate
End Sub

Private Sub SetEnd()
  If IsDate(Me.StartPeriodDate) Then
    Select Case Me.Parent.scheduleType
        Case "Weekly"
                If IsNull(Me.EndPeriodDate) Or Me.EndPeriodDate < Me.StartPeriodDate Then Me.EndPeriodDate = Me.StartPeriodDate + 6
        Case "Monthly"
                 If IsNull(Me.EndPeriodDate) Or Me.EndPeriodDate < Me.StartPeriodDate Then Me.EndPeriodDate = GetEndOfMonth(Me.StartPeriodDate)
    End Select
  End If
End Sub

6. Code handles the first reporting period where there is no previous entry. Defaults to first monday of the current week or first day of the month
7. Handles updates to the startperiod after entry.

 

[Pat Hartman]

I know the BeforeInsert event doesn't run until ONE character is typed. I believe I mentioned that. I'm pretty sure that there are other fields that need entry so you just adjust the tab order so the date isn't first.

 

[MajP]

And thus my solution is preferred as I show.

 

[Pat Hartman]

And thus my solution is preferred as I show.

You are entitled to your opinion

 

[CupKat]

Thank you both for taking the time to help! It's really interesting to see different ways of approaching the problem and the thinking behind it!
I am going to have to play around with it a bit to see what's going to work best for my setup.