Today's Date to be increased to tommorrows date if found..

[mtnclark]

Hi,
I have a function which is used to ascertain whether an item on loan is due back today. This is done by searching through a column aptly named LoanEndDate.
On discovering this date and comparing it with the current date a message is displayed on the screen.
I would then like to set the LoanEndDate to tomorrow’s date so this procedure could be repeated the next day if the lender has not returned the item..
Here is my code structure..


Function isLenderOverdue() As Boolean
'Display messagebox informing user that a piece of loankit has now become
' overdue.
Dim Dbs As Database
Dim rstRecordOD As Recordset
Dim LoanEndDate As Variant
Dim strSQL As String
Dim sendEmail As Boolean
Dim name As String


strSQL = "SELECT * FROM LoanKit WHERE LoanEndDate = #" & Date & "#;"
Set Dbs = CurrentDb
Set rstDueToday = Dbs.OpenRecordset(strSQL)



If rstDueToday.RecordCount = 0 Then
Exit Function
End If

rstDueToday.MoveFirst

Do Until rstDueToday.EOF
MsgBox ("This Loan Asset is now Due : Asset Number: " & _
rstDueToday!AssetNumber & ", " & rstDueToday!LoanEndDate & _
", " & "Due By: " & rstDueToday!FirstName & " " & rstDueToday!LastName)


rstDueToday.MoveNext

Loop

End Function
Thanks in advance for any help given

---------------
Marty..

 

[pgk]

Hi,
Add the following lines of code after the MsgBox statement:

rstDueToday.Edit
rstDueToday.Fields("LoanEndDate&quot = DateAdd("d",1,rstDueToday.Fields("LoanEndDate&quot)

rstDueToday.Update


Hope it helps. Let me know what happens.
With regards,
PGK

 

[mtnclark]

Pgk,

Thanks heaps mate. It worked a charm. I had originally tried this code but had forgotten the update at the end. I had:

rstDueToday.Edit

Dim FirstDate As Date ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
IntervalType = "d"

FirstDate = rstDueToday!LoanEndDate
Number = 1
rstDueToday!LoanEndDate = DateAdd(IntervalType, Number, FirstDate)

and of course the bit I forgot.
rstDueToday.update --oops!!
Once again thanks..
marty