I am trying to run a public subroutine after i deleted a record (some of the records values were saved in public variables) in order to determine and re-associate min and max values as being min/max if they have now become so because of the old records deletion.
Here is the subroutine to delete the record:
Private Sub Delete_Record_Click()
Dim UserResponse As Integer
On Error GoTo Delete_Record_Click_Err
On Error Resume Next
DoCmd.GoToControl Screen.PreviousControl.Name
Err.Clear
If (Not Form.NewRecord) Then
UserResponse = MsgBox("Are you sure you want delete this record?", vbYesNo, "Delete Record?")
If UserResponse = vbYes Then
FindMinMax
DoCmd.RunCommand acCmdDeleteRecord
Me.Requery
SetMinMaxAfter
DoCmd.GoToRecord , "", acNewRec
End If
End If
End Sub
and FindMinMax() gets some public values of the record before it's deleted
and SetMinMaxAfter()'s code is as follows (much longer but this is the jist):
Public Sub SetMinMaxAfter()
Dim dbCollection As DAO.Database
Dim Rec As DAO.Recordset
On Error GoTo SetMinMaxAfter_Err
If Core = "Min" Then
Set dbCollection = CurrentDb
'Set Rec = dbCollection.OpenRecordset("Entry Data", dbOpenDynaset)
Set Rec = dbCollection.OpenRecordset("SELECT * FROM [Entry Data] ORDER BY [Core Loss]", dbOpenDynaset)
Rec.FindFirst ("[Symbol Number] = '" & Symbol & "' And Year([Date Tested]) = Year(" & "#" & DateTest & "#" & ")")
If Not Rec("[Core Spec]") = "Min" Then
Rec("[Core Spec]") = "Min"
MinimumCore = Rec("[Core Loss]")
Rec.Update
Do While IsNull(DLookup("[Serial Number]", "[Entry Data]", "[Symbol Number] = '" & Symbol & "' AND Year([Date Tested]) = Year(" & "#" & DateTest & "#" & ") And [Core Loss] = " & MinimumCore & " And Not [Core Spec] = ""Min""")) = False
Rec.FindNext ("[Symbol Number] = '" & Symbol & "' And Year([Date Tested]) = Year(" & "#" & DateTest & "#" & ") And [Core Loss] = " & MinimumCore & " And Not [Core Spec] = ""Min""")
Rec.Edit
Rec("[Core Spec]") = "Min"
Rec.Update
Loop
End If
. . . most variables you see are names given to textboxes in a form however "core" is a public variable. I have narrowed it down giving me the error "Object variable or With block variable not set" before the if statement in SetMinMaxAfter() even occurs. I believe it has something to do with DAO because when i comment out the database and recordset it jumps into the if statement. Any thoughts? I have looked in other forums and have seen things on this error when trying to delete records and then use DAO but didn't fully understand it.