Warranty by Date

[Adam McReynolds]

This is for a repair database. I currently have a DCount to determine if a unit was previously in for repair and a msgbox that gives a warning to check for a warranty sticker. I want to have a field(incoming_disposition) changed to 'Warranty' if the last occurrence(DMax?) of the field(complete_date) is within 6 months and 1 week(grace period) from the current date. This would take place in AfterUpdate on the fields(bar_code & Incoming_Module_Sn).

I was wondering if the msgbox warning could execute as in my current code(below) and a option to execute the code I am speaking about to change the field to 'Warranty' could be given in a yes/no in the msgbox. Like: "This unit was last completed on [insert last occurrence(complete_date)]. Would you like to mark this as a warranty?"

Any help would be much appreciated.

Current code:

Private Sub Bar_Code_AfterUpdate()
    If Nz(DCount("bar_code", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'"), 0) > 0 Then
        If MsgBox("The Barcode already exists. Check Warranty! Do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        End If
    End If

End Sub

 

[DavidAtWork]

I think you could just modify what you already have ie:

Private Sub Bar_Code_AfterUpdate()
Dim warrantyCheck as Date
 
If Not IsNull(DLookup("bar_code", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")) Then
 
   warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")
    If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week) 
         If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
           Docmd.RunSQL("UPDATE tbl_Module_Repairs SET [warrantyField] = 'Warranty' WHERE [bar_code] = '" & Me.Bar_Code.Text & "'" AND [complete_date] = #" & warrantyCheck & "#") 
         End If
    End If
End If
End Sub

David

 

[Adam McReynolds]

I think you could just modify what you already have ie:
David

Thanks David! This works except I get an error if the previous occurrence does not have a complete date. I get a null error. Now this won't really happen in real time because we won't receive a unit again until it has been completed but I would like to know how to circumvent this error message if possible.

I also took out the UPDATE because I am working in a form datasheet so I am adjusting one record at a time, so I replaced it with a simple Me.field. Thanks again for the huge help!!!!!

Here is the code for any people who stumble upon this in their search:

Dim warrantyCheck As Date
If Not IsNull(DLookup("bar_code", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")) Then

 
   warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")
    If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week)
         If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
         Me.Incoming_Disposition = "Warranty"
         End If
    End If
End If
End If

 

[DavidAtWork]

You need to check for IsNull on the DMax, if no record satisfies criteria or contains no records, DMax returns Null

Dim warrantyCheck As Date
If Not IsNull(DLookup("bar_code", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")) Then
 
  If IsNull(DMax("[complete_date]", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")) Then
     Msgbox "No Complete date found",vbokonly + vbcritical
     Exit Sub
     Else
          warrantyCheck = DMax("[complete_date]", "tbl_Module_Repairs", "bar_code = '" & Me.Bar_Code.Text & "'")
        If Date - warrantyCheck < 190 Then ' approx 183 + 7 (6 months + a week)
          If MsgBox("This unit is still under warranty and was last completed on " & warrantyCheck & ". Would you like to mark this as a warranty?", vbYesNo, "Duplicate Warning") = vbYes Then
             Me.Incoming_Disposition = "Warranty"
          End If
        End If
    End If
End If

David