Okay, I think I figure out the issue with criteria part of the DMax function. I revised my code to:
Code:
Private Sub Next_Maintenace_Date_Click()Dim Maintenance_Interval As String
Dim Number_Maintenance_Interval As Integer
Dim DCalc As Date
DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", "[Equipment_ID] =" & Forms![Equpment_Tracking_Form]!Equipment_ID)
If Me.Parent!Maintenance_Schedule = 1 Then
Maintenance_Interval = "m"
Number_Maintenance_Interval = 1
ElseIf Me.Parent.Maintenance_Schedule = 2 Then
Maintenance_Interval = "m"
Number_Maintenance_Interval = 2
ElseIf Me.Parent!Maintenance_Schedule = 3 Then
Maintenance_Interval = "q"
Number_Maintenance_Interval = 1
Else: Me.Parent!Maintenance_Schedule = 4
Maintenance_Interval = "yyyy"
Number_Maintenance_Interval = 1
End If
Me.Next_Maintenace_Date = DateAdd(Maintenance_Interval, Number_Maintenance_Interval, DCalc)
End Sub
and it appears to be working. I need to do some more testing to know for sure.
Now for my own edification I would really like to know what is going on with the line of code
Code:
DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", "[Equipment_ID] =" & Forms![Equpment_Tracking_Form]!Equipment_ID)
The first two parts of DMax make sense to me. I am telling access to get the maximum value in the field named Last_Maintenance_Date from the table Maintenance_TBL, but I get stumped on the third part in this case.
"[Equipment_ID] =" tells access to look at the Equipment_ID Field on the current form, which in this case is the Maintenance_FRM_SUB
then &Forms!{Equpment_Tracking_Form]!Equipemnt_ID is telling access to open the Equpment_Tracking_Form and get the record where the Equipment_ID field of the Equpment_Tracking_Form matches the Equipment_ID of the Maintenance_FRM_SUB?