asked on

Object Variable or With Block Variable Not Set Error

I will appreciate if I could get an assistance in resolving the error below.

Run-Time Error "91" error
"Object Variable or With Block Variable Not Set not set"

Public Sub DeleteRenumber()
 
    Dim rs As DAO.Recordset
    Dim cbo As String
    Dim i As Integer
    Dim bLoop As Boolean
    Dim sPrefix As String
    Dim sSql As String
 
On Error GoTo Err_cmdDelete_Click
 
    bLoop = True
     
    sPrefix = "XW-" & rs!TestType & "-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-"
    'Find next number from one about to be deleted
    i = Val(Mid$(Me.FDID, 2)) + 1
      
    'Now update existing records
    Set rs = Me.RecordsetClone
 
    'Find first next highest number
    sSql = "FDID = '" & sPrefix & Format(i, "00") & "'"
    Debug.Print "Looking for", sSql
    rs.FindFirst sSql
    Do While bLoop
 
        'If no records found, end loop
        If rs.NoMatch = True Then
            bLoop = False
        Else
 
            'Decrement number
            Debug.Print "Updating ID", rs!SID
            
            rs.Edit
            rs!FDID = sPrefix & Format(i - 1, "00")
            rs.Update
 
            'Find next highest
            i = i + 1
            sSql = "FDID = '" & sPrefix & Format(i, "00") & "'"
            Debug.Print "Finding next", sSql
            rs.FindNext "FDID = '" & sPrefix & Format(i, "00") & "'"
        End If
    Loop
 
    'Closedown
    rs.Close
    Set rs = Nothing
 
    Me.Requery
    
Exit_cmdDelete_Click:
    Exit Sub
 
Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
 
End Sub

Open in new window

Scott McDaniel (EE MVE )

Can you determine the line where this occurs? Set a breakpoint at the beginning of the code, then run it and "walk" through it to find the offending line.

However, typically you should check for a NoMatch or .EOF when using FindFirst. If your code doesn't find anything, the rs variable would not be "set", and you would get this error:

rs.FindFirst sSql

If rs.NoMatch Then
Msgbox "No Record Found"
Exit Sub
Else
Do While bLoop
etc etc etc
End If

Scott McDaniel (EE MVE )

Sorry, just saw where you ARE using NoMatch ... not sure how that one slipped past.

So, again: Can you determine the offending line?

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billcute

ASKER

sorry...LSMConsulting

rockiroads suggestion resolved the error