Results 1 to 2 of 2
  1. #1
    walter189 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    7

    Object variable or With block variable not set

    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.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,329
    I don't see it at a glance, but one way to debug it is to temporarily comment out the "On Error..." lines and run the code. The debugger should take you to the offending line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-05-2010, 01:26 PM
  2. Object variable not retaining value
    By tuna in forum Programming
    Replies: 0
    Last Post: 05-21-2010, 05:38 PM
  3. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM
  4. Object variable...Jesus!
    By Baba78 in forum Access
    Replies: 8
    Last Post: 06-19-2009, 03:56 PM
  5. Variable question
    By synapse5150 in forum Programming
    Replies: 1
    Last Post: 07-09-2008, 08:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums