Check Microsoft Access Form Values before Save

Question

I have an Access Form - lets call it "Add Labor" (Access 2007) that saves data into a table.

The table has two columns in particular called "Start Date" and "End Date" (This table stores tasks)

There is also another table called FiscalYears which includes Start and End Dates for Fiscal Years, which is structured as follows

FyID FYear StartDate EndDate

Example Data:

FYId FYear StartDate EndDate
-----------------------------
 1   2010   10/1/2009 9/30/2010
 2   2011   10/1/2010 9/30/2011

So in My Add Labor Form if someone enters labor that span across two fiscal years I need to enter two labor entries. Here is an example

If a user selects Labor Start Date = 6/30/2009 And End Date 10/2/2010 , it spans two fiscal years

So in my Labor Table I should enter two things

LaborID StartDate EndDate
-----------------------------
1        6/30/2009  9/30/2010
2        10/1/2010  10/2/2010

Basically I need to do a check before I save the record and add two records if they span Fiscal years, right now I'm just blindly doing Save Record on the form (inbuilt), but I guess I need to add some VBA. I've hardly ever used Access so this may be simple(hopefully). I am thinking instead of the event which just calls Save Record, I need it to add custom VBA.

Answer 1

Say you have an unbound form for adding the dates, you can say:

Dim rsFY As DAO.Recordset
Dim rsAL As DAO.Recordset
Dim db As Database
Dim sSQL As String

Set db = CurrentDb

''Select all years from the fiscal years table    
sSQL = "SELECT FYear, StartDate, EndDate " _
  & "FROM FiscalYears WHERE StartDate>=#" & Format(Me.StartDate, "yyyy/mm/dd") _
  & "# Or EndDate <=#" & Format(Me.Enddate, "yyyy/mm/dd") _
  & "# ORDER BY FYear"

Set rsFY = db.OpenRecordset(sSQL)
Set rsAL = db.OpenRecordset("AddLabor") ''table

''Populate recordset
rsFY.MoveLast
rsFY.MoveFirst

Do While Not rsFY.EOF

    ''Add records for each year selected
    rsAL.AddNew
    If rsFY.AbsolutePosition = 0 Then
        rsAL!StartDate = Format(Me.StartDate, "yyyy/mm/dd")
    Else
        rsAL!StartDate = rsFY!StartDate
    End If

    If rsFY.AbsolutePosition + 1 = rsFY.RecordCount Then
        rsAL!Enddate = Format(Me.Enddate, "yyyy/mm/dd")
    Else
        rsAL!Enddate = rsFY!Enddate
    End If

    rsAL.Update

    rsFY.MoveNext
Loop

If the code was running in a main form with a subform showing the Addlabor table, you could update the subform to show the new records like so:

 Me.Addlabor_subform.Requery

Answer 2

Why do you need a FiscalYears table? If your organization's fiscal years always start on Oct. 1 and end on Sept. 30, you can use a function to determine the fiscal year for a given date.

Public Function Fy(ByVal pDate As Date) As Integer
    Dim intYear As Integer
    Dim intReturn As Integer
    intYear = Year(pDate)
    If pDate > DateSerial(intYear, 9, 30) Then
        intReturn = intYear + 1
    Else
        intReturn = intYear
    End If
    Fy = intReturn
End Function

And simple functions to return the Start and End dates for a given year.

Public Function FyStart(ByVal pYear As Integer) As Date
    FyStart = DateSerial(pYear - 1, 10, 1)
End Function

Public Function FyEnd(ByVal pYear As Integer) As Date
    FyEnd = DateSerial(pYear, 9, 30)
End Function

You can then determine how many fiscal years are included in a given date range by:

Fy(EndDate) - Fy(StartDate)

But I may be totally off base because you said "Start Date = 6/30/2009 And End Date 10/2/2010" spans two years. However, this expression returns 2 (3 years):

Fy(#10/2/2010#) - Fy(#6/30/2009#)