Code:
'---------------------------------------------------------------------------------------
' Procedure : PopulatePreviousExamDate
' Author : Rod
' Date : 13/08/2012
' Purpose : Retrieve, if any, the previous examination date and populate the correponding
' field. If the Serial field is null (e.g. a new record) then skip the look up.
' If the ID is null (e.g. current record has not yet been saved) look up the
' maximum date for this serial. Otherwise retrieve the maximum examination date
' excluding this record that is less than the date of this record.
'---------------------------------------------------------------------------------------
Private Sub PopulatePreviousExamDate()
Dim strSQLDate As String
Dim varExamDate As Variant
If IsNull(Me.Serial) Then Exit Sub
If IsNull(Me.ID) Then
Me.Previous_Examination_Date = DMax("TEDate", "TblLoler", "TESerial = '" & Me.Serial & "'")
Else
varExamDate = Nz(Me.ExamDate, "X")
If IsDate(varExamDate) Then
strSQLDate = "#" & _
Format(DatePart("m", varExamDate), "00") & "/" & _
Format(DatePart("d", varExamDate), "00") & "/" & _
Format(DatePart("yyyy", varExamDate), "0000") & _
"#"
Me.Previous_Examination_Date = DMax("TEDate", "TblLoler", "TESerial = '" & Me.Serial & "'" & _
" AND TEDate < " & strSQLDate & _
" AND ID <> " & Me.ID)
Else
Me.Previous_Examination_Date = DMax("TEDate", "TblLoler", "TESerial = '" & Me.Serial & "'" & _
" AND ID <> " & Me.ID)
End If
End If
End Sub
It's not really rocket science but here goes. There are three controls of interest on the form:
- 'Serial' which contains the key of the item (named TESerial on file),
- 'ExamDate' which contain this instance's date (named TEDate on file) and
- [Previous Examination Date] which contains the most recent TEDate before this instance.
All three controls are bound to their respective columns in the underlying result set (table) via the Control Source property.
I puposely designed the procedure for use upon the form's On Current event or the Serial control's Before Update event, so the first task is to make sure there is a non null value in the Serial control. If the value of Serial is null, which most likely happens when moving to a new record, then there's nothing to do so exit the procedure. (Code highlighted in red.)
The next statement tests ID for being null. ID is an autonumber, the primary key of the bound table. There is no corresponding bound control so Me.ID directly interrogates the underlying result set. Autonumber primary keys are typically null when adding a new record. If the ID is null the blue code applies. However it is nigh on impossible that there will be a value in Serial while ID is null but I thought I had better cover the situation anyway. The blue code simply retrieves the latest date on file for this Serial using DMax. As serial is a text attribute it is necessary to enclose it in quotation marks.
So now there is a record with a valid primary key (ID) and a non null value for Serial (TESerial). The next test is to determine whether the value in ExamDate is a valid date; it could be null (empty), some other rubbish or a valid date. Rather than use the IsNull function I use the trick of capturing the value of ExamDate in a variant (variant because I have as yet no idea what it is) while forcing the value to 'X' if ExamDate is in fact null. That's what the Nz function does: if the value of the first argument is null then use the second argument. (The green code applies.)
The next statement tests whether the value of the variant can be interpreted as a date. 'X' cannot be a date which is why I chose it for the previous statement. This test is not foolproof however since numbers can be interpreted as dates. If the 'rubbish' I referred to in the previous paragraph happened to be a number then Access would be happy to believe it is a date. However if the control is defined and formatted for a date then this 'loophole' is unlikely to occur.
If it's not a date the gray (grey for UK side of the Pond) text applies. This simply looks for the latest date for this Serial that is not this record. I have had second thoughts about this - see end of post.
Otherwise the chunk of code in the middle applies. We have this record identified by ID; we have the Serial which identifies the item and we have ExamDate which specifies the date of the examination. The first few lines format the date suitably for SQL (see preceding post). It's then a matter of finding the most recent examination date for this item that is not this record and is earlier than the date on this record. Why the last condition? Well we can't be sure we are always dealing with the latest examination record.
---------
Invoking this procedure from the form's On Current event does mean a slight performance hit but has the benefit of making the whole thing self-correcting in that, if in the interim, another record has been inserted between this and the former previous record, then the newly inserted record will be found.
Second thoughts.
I now think it better to set the previous date to null if this date is invalid. (Gray text.)
Dave, I recommended that you should invoke this procedure before update of Serial. I now think that in addition it should be invoked before update of exam date.
Using data from 2 records in a report (specifying recordset record?)
Windows 7 32bit 
Access 2007
).
Reply With Quote
)
