Hi,

I've wrote this function to loop through a table of Holiday dates in
order to calculate a business day three days in the future that was not
a weekend nor a holiday.

When I tested it as a function in a query, it worked fine.

I then took the code to a sub on the AfterUpdateEvent and added the dim
db, dim rst, do...loop, etc.

When I set breakpoints, to check that the code is moving through the
recordset, the holidate never changes.  It's always starts and stays at
1/2/2006, which is not the first record in the Holiday table, but is
the 12th record.  In the immediate window if I type rstHoliday.movenext
and then ?holidate, I still get 1/2/2006.  (BTW Holidate is the primary
key of the Holiday table).

Would someone please take the time to look at this - is my loop in the
wrong place?

Thanks so much.

Here's the code:

Private Sub AsOfTradeDate_AfterUpdate()

'This sub is used to calculate the settlemet date using the default
'3 day settlement from as of trade date and takes into account
'weekends and holidays.  The holidays are from the Holiday Table which
must be kept up by
'the user each year.

       Dim db As Database
       Dim AsOfDate As Date
       Dim HoliDate As Date
       Dim WeekDayNumber
       Dim rstHoliday As DAO.Recordset
       Dim rstIndex As index

       Set db = CurrentDb()
       Set rstHoliday = db.OpenRecordset("Holidays")

   AsOfDate = Format(Me.AsOfTradeDate, "Short Date")
   WeekDayNumber = Weekday(Me.AsOfTradeDate)
   HoliDate = rstHoliday!HoliDate
   HoliDate = Format(HoliDate, "short Date")
   rstHoliday.MoveLast
   rstHoliday.MoveFirst

   Do While rstHoliday.EOF = False
       If HoliDate > AsOfDate And HoliDate < DateAdd("d", 6, AsOfDate)
Then
         Select Case WeekDayNumber
           'WeekDayNumber is 2 Monday
           Case 2
               If DateAdd("d", 1, AsOfDate) = HoliDate Then
                   Me.SettlementDate = DateAdd("d", 4, AsOfDate)
               ElseIf DateAdd("d", 2, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 4, AsOfDate)
               ElseIf DateAdd("d", 3, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 4, AsOfDate)
               Else
                     Me.SettlementDate = DateAdd("d", 3, AsOfDate)
               End If
           'WeekDayNumber is 3 Tuesday
           Case 3
               If DateAdd("d", 1, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 2, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 3, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               Else
                 Me.SettlementDate = DateAdd("d", 3, AsOfDate)
           End If

           'WeekDayNumber is 4 Wednesday
           Case 4
               If DateAdd("d", 1, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 2, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 5, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               Else
                     Me.SettlementDate = DateAdd("d", 5, AsOfDate)
               End If

           'WeekdayNumber is 5 Thursday
           Case 5
               If DateAdd("d", 1, AsOfDate) = HoliDate Then
                 Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 4, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 5, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               Else
                     Me.SettlementDate = DateAdd("d", 5, AsOfDate)
               End If

           'WeekdayNumber is 6 Friday
           Case 6
               If DateAdd("d", 3, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 4, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               ElseIf DateAdd("d", 5, AsOfDate) = HoliDate Then
                     Me.SettlementDate = DateAdd("d", 6, AsOfDate)
               Else
                 Me.SettlementDate = DateAdd("d", 5, AsOfDate)
               End If

       End Select
       End If
       rstHoliday.MoveNext
       Loop
       
       Set rstHoliday = Nothing
         
End Sub