Object Variable Not Found

[nag9127]

I am getting the infamous "Object variable or with block variable not found" message. I only get the error after responding to the message box with a Yes. A No response to the message box works fine. Also the error message does nor prevent the routine from running properly once I click the OK box on the error. Here is my code. Thanks for any help!

Private Sub Combo17_AfterUpdate()
On Error GoTo Err_Combo17_AfterUpdate

Me.ItemUsed = Me.Combo17.Column(1)

Dim CurDB As Database
Dim rs As recordset
Dim Resp As Integer

Set CurDB = CurrentDb
Set rs = CurDB.OpenRecordset("SELECT * from ProductionBatchDetailTable WHERE" & _
" BatchID =" & Forms!ProductionForm!ProductionSubForm1!BatchID)

If rs.RecordCount = 0 Then
    rs.Close
    Set rs = Nothing
    Me.QuantityUsed.Enabled = True
    Me.QuantityUsed.Locked = False
    Me.QuantityUsed.SetFocus
    Exit Sub
    Else
    rs.MoveFirst
    Do Until rs.EOF
        If rs!ItemUsed = Me.ItemUsed Then
            Resp = MsgBox("Item Already In Batch!" _
            & vbCrLf & vbCrLf & "Add This Item Again?", vbYesNo _
            + vbExclamation + vbDefaultButton2, "Item Already Exists!")
                If Resp = vbYes Then
                    rs.Close
                    Set rs = Nothing
                    Me.QuantityUsed.Enabled = True
                    Me.QuantityUsed.Locked = False
                    Me.QuantityUsed.SetFocus
                    Exit Do
                    Exit Sub
                    Else
                    Me.Undo
                    Me.Combo17.SetFocus
                    Exit Sub
                End If
        End If
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Me.QuantityUsed.Enabled = True
    Me.QuantityUsed.Locked = False
    Me.QuantityUsed.SetFocus
    Exit Sub
End If

Exit_Combo17_AfterUpdate:
    Exit Sub

Err_Combo17_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Combo17_AfterUpdate

End Sub

 

[ck1999]

Probrably because once you

exit do

it does not

exit sub
but finishes the sub

change to this

If Resp = vbYes Then

Exit Do

Else
Me.Undo
Me.Combo17.SetFocus
Exit Sub
End If


ck1999

 

[TheAceMan1]

How are ya nag9127 . . .

Try:

[blue]Dim CurDB As [purple][b]DAO.[/b][/purple]Database
Dim rs As [purple][b]DAO.[/b][/purple]Recordset[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nag9127]

Thanks for your responses!

I resolved the problem which was that the recordset was being closed twice, once before the "Exit do" statement, and once after the "Loop" statement. The error was generated by the second close attempt because the recordset was already closed (the object variable was not active). I reworked the code taking out the first close sequence.

Thanks again!

 

[ck1999]

Wish I would of thought of that

ck1999

 

[TheAceMan1]

nag9127 . . .

Your Access version is?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nag9127]

Access 2000