I have a Motor Vehicle Travel Diary (tblMVLog) table with records, as the parent to Trip Log table. Apart from in-Travel Diary record validation in BeforeUpdate such as MVLogStartDate (Diary start date) is before the MVLogEndDate (Diary End Date), I would also like to check that, if the record is a new record, that the MVLogStart Date is Greater than the MVLogEnd date of the previous record, and that the OdometerStart is greater than or equal to the OdometerEnd of the previous record.
Should such checks be in the BeforeUpdate event? I have tried using AfterUpdate and a recordset to compare the newest record and the prior one, however, as edits might be done in earlier records this can fail - the user may be editing an earlier record and so the comparison may not be against the newest record in tblMVLog and the previous one in MVlogEndDate order. It seems that I need to do this in BeforeUpdate if the record being updated is a new record. However this record has not been committed so how do I use a recordset (or other method) to compare the new record values and the last record.
This is the code I was trying in AfterUpdate
Appreciate your advice.
Should such checks be in the BeforeUpdate event? I have tried using AfterUpdate and a recordset to compare the newest record and the prior one, however, as edits might be done in earlier records this can fail - the user may be editing an earlier record and so the comparison may not be against the newest record in tblMVLog and the previous one in MVlogEndDate order. It seems that I need to do this in BeforeUpdate if the record being updated is a new record. However this record has not been committed so how do I use a recordset (or other method) to compare the new record values and the last record.
This is the code I was trying in AfterUpdate
Code:
Dim intMVID, prevRecODEnd As Integer
Dim prevRecEndDt As Date
Dim strSQL As String
Dim db As dao.Database
Dim rs As dao.recordSet
intMVID = Me.MVID
If DCount("MVLOgID", "tblMVLOG", "MVID = " & intMVID) > 1 Then ' check if there are at least 2 records to enable checks
Set db = CurrentDb
strSQL = "SELECT tblMVLog.MVLogID, tblMVLog.MVID, tblMVLog.LogSheetEndDate, tblMVLog.OdometerSheetEnd, tblMVLog.LogSheetstartDate, tblMVLog.OdometerSheetStart" & _
" FROM tblMVLog" & _
" WHERE MVID = " & intMVID & _
" ORDER BY tblMVLog.LogSheetEndDate DESC;"
Set rs = db.OpenRecordset(strSQL)
' If the rs is empty, exit.
If rs.EOF Then Exit Sub
With rs
.MoveFirst ' the most recent record by logsheetenddate
.MoveNext ' Navigate to previous record to get previous values for comparison
prevRecEndDt = ![LogSheetEndDate]
prevRecODEnd = ![OdometerSheetEnd]
.MoveFirst ' back to the most recent record
If ![LogSheetstartDate] < prevRecEndDt Then
MsgBox "The Start date for this Travel Diary is before the end date for the previous Travel Diary. " & _
"This is not permitted. Adjust the start date", vbInformation, "Start date is earlier than end date of previous record"
Me.txtStartDate.SetFocus
GoTo exit_afterUpdate
End If
If ![OdometerSheetStart] < prevRecODEnd Then
MsgBox "The odometer value entered is less than the previous Travel Diary odometer end value. " & _
"This must be corrected. Adjust the odometer at start value.", vbInformation, "Start reading is less than end reading of previous record"
Me.OdometerSheetStart.SetFocus
GoTo exit_afterUpdate
End If
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End IfAppreciate your advice.
