Need help with code that looks up date of previous record.

[HillTJ]

Hi, I'm writing an equipment calibration database, where I lookup the date of last calibration, add a new "frequency" & save the new record. See my code attached. This code works for all records where there is a previous one, but crashes when a new record is entered. It assumes a date exists in [Anniversary_Date]. I had a go with the 'if not null' line. but this didn't work.

Would appreciate a heads up.

Private Sub Calibration_Frequency_AfterUpdate()

Dim ID As Long
Dim Temp_Date As Date

On Error GoTo ErrorHandler

If Not IsNull(Me.[Anniversary_Date]) Then ' This affects the new Record too!!

   ID = DMax("InspectID", "Qry_Inspect_Equipment")
      
   Temp_Date = DLookup("[Anniversary_Date]", "TBL_Inspect_Record", "InspectID=" & ID)

    Select Case [Calibration_Frequency]

    Case 1 ' Annually
        Me.[Anniversary_Date] = DateAdd("yyyy", [Calibration_Unit], [Temp_Date])

    Case 2 'Monthly
        Me.[Anniversary_Date] = DateAdd("m", [Calibration_Unit], [Temp_Date])

    Case 3 'Weekly (Weekdays)
        Me.[Anniversary_Date] = DateAdd("ww", [Calibration_Unit], [Temp_Date])

    Case 4 ' Daily
        Me.[Anniversary_Date] = DateAdd("d", [Calibration_Unit], [Temp_Date])

    Case Else
    End Select

End If
ExitError:
    Exit Sub
    
ErrorHandler:
  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Calculate Anniversary Date")
        Resume ExitError
    End Select
 
End Sub

 

[theDBguy]

Hi. You declared your Temp_Date variable as a Date and then assign a value to it using DLookup(). If there is no matching value from the DLookup(), that line should error out. You could try using the Nz() function to avoid the error, but you'll have to decide on what value to use if there is no Anniversary Date.

 

[HillTJ]

theDBguy, Yeah, I tried that but used '0' as the default. A bit dumb because '0' cannot be evaluated in the case(s). Will use date() instead & get rid of the if statement..Cheers.

 

[HillTJ]

theDBguy, tried this;

Temp_Date = Nz(DLookup("[Anniversary_Date]", "TBL_Inspect_Record", "InspectID=" & ID), #1/1/2019#)

Gave me error message 94 'Invalid use of null'. I used date 1/1/2019 as a test only.

 

[arnelgp]

if dmax did not find anything it will return Null, so check this one also. use debugging tool.

 

[HillTJ]

arnelgp, yes I'll check. What's the syntax of a NZ command to return todays date, it would not accept date(), changed it to 'Date'. That's why I tried 1/1/2019. Cheers

 

[arnelgp]

first check whether ID is 0.

Private Sub Calibration_Frequency_AfterUpdate()

Dim ID As Long
Dim Temp_Date As Date

On Error GoTo ErrorHandler

If Not IsNull(Me.[Anniversary_Date]) Then ' This affects the new Record too!!

   ID = NZ(DMax("InspectID", "Qry_Inspect_Equipment"), 0)
   '---Remove this msgbox when the ID is not 0
   If ID = 0 Then
      msgbox ID
      Exit sub
   End If
   '---end of code ---

   Temp_Date = Nz(DLookup("[Anniversary_Date]", "TBL_Inspect_Record", "InspectID=" & ID), Date)

    Select Case [Calibration_Frequency]

    Case 1 ' Annually
        Me.[Anniversary_Date] = DateAdd("yyyy", [Calibration_Unit], [Temp_Date])

    Case 2 'Monthly
        Me.[Anniversary_Date] = DateAdd("m", [Calibration_Unit], [Temp_Date])

    Case 3 'Weekly (Weekdays)
        Me.[Anniversary_Date] = DateAdd("ww", [Calibration_Unit], [Temp_Date])

    Case 4 ' Daily
        Me.[Anniversary_Date] = DateAdd("d", [Calibration_Unit], [Temp_Date])

    Case Else
    End Select

End If
ExitError:
    Exit Sub
    
ErrorHandler:
  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Calculate Anniversary Date")
        Resume ExitError
    End Select
 
End Sub

 

[HillTJ]

arnelgp, looks good. I'll give it a try. Thanks for your input, I only wish I'd thought of it & not bugged you!