Issue with error trap getting Runt-time Error "91" 1

[Prospec]

Using Access 2010 have the following code on a Control Box's NotInList Event.

When the user selects NO from the popup asking to add new item the following error apprears: "Run-Time error '91' Object variable or With Block varialble not set"

It points to this line in the code:
[bold]If cnn.Errors.Count > 0 Then [/bold]

THanks,

Private Sub cboVehiculoMarca_NotInList(NewData As String, Response As Integer)

   Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim lngRecs As Long
    Dim strError As String
    Dim lngError As Long
    Dim strMsg As String

    On Error GoTo ErrorHandler
        
    strMsg = "'" & NewData & "' no esta en la lista de Marcas.  "
    strMsg = strMsg & "Desea crearla?"
    If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
     "Nueva Marca") Then
        'Response = acDataErrDisplay
        Me.cboVehiculoMarca.SetFocus
        
    Else

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn

    cnn.Open adhcADH2002SQLCnn
         
    cmd.CommandText = "proInsertarMarca"
    cmd.CommandType = adCmdStoredProc
    
    ' Check that CustomerId, LastName, and
    ' FirstName are completed.
    If IsNull(NewData) Or _
     NewData = vbNullString Then
        MsgBox "Descripción de Marca es campo obligatorio", _
         vbCritical + vbOKOnly, Me.Caption
        Me.cboVehiculoMarca.SetFocus
        GoTo ExitHere
    End If

    ' Append parameters to the Command
    ' object's Parameters collection.
    Set prm = cmd.CreateParameter("Marca", _
     adVarChar, adParamInput, 50, NewData)
    cmd.Parameters.Append prm
    
    cmd.Execute RecordsAffected:=lngRecs, _
     Options:=adExecuteNoRecords
    Response = acDataErrAdded
    ' Check if stored proc worked.
    If lngRecs <> 0 Then
        MsgBox "Registro Creado.", _
         vbInformation + vbOKOnly, Me.Caption
    Else
        MsgBox "No fue posible crear Registro.", _
         vbCritical + vbOKOnly, Me.Caption
    End If
    End If
ExitHere:
    Set cmd = Nothing
    Set prm = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub
ErrorHandler:
    ' Grab Access/VBA Error
    lngError = Err.Number
    strError = Err.Description
    ' If ADO Error available grab
    ' the first one in the collection.
    If cnn.Errors.Count > 0 Then
        lngError = cnn.Errors(0).NativeError
        strError = cnn.Errors(0).Description
    End If
    MsgBox "Error " & lngError & ": " & _
     strError, vbCritical + vbOKOnly, _
     "frmCustomer Error"
    Resume ExitHere
End Sub

 

[Andrzejek]

If your user says "No" to your question: "Desea crearla?" and
[tt]Me.cboVehiculoMarca.SetFocus[/tt] creates an error (let's say cboVehiculoMarca is not visible or enabled), you go to ErrorHandler: where you check for [tt]cnn.Errors.Count[/tt] but cnn was never Set :-(

That's my guess....

Have fun.

---- Andy

 

[Andrzejek]

You may want to add to your code:

[blue]
  If Not(cnn Is Nothing) Then[/blue]
    If cnn.Errors.Count > 0 Then
        lngError = cnn.Errors(0).NativeError
        strError = cnn.Errors(0).Description
    End If[blue]
  End If[/blue]

Have fun.

---- Andy

 

[Prospec]

Would the follwoing code be the correct to set it?

Set cnn = New ADODB.Connection

 

[Prospec]

Placed the code as suggested but sill get the run-time error.

 

[Andrzejek]

When you step thru your code, which line of code couses to go to [tt]ErrorHandler:[/tt]?

Have fun.

---- Andy

 

[Prospec]

Hi Andrzejek,

ExitHere:
    Set cmd = Nothing
    Set prm = Nothing
    cnn.Close     <--------- E R R O R THIS LINE IS THE ON THAT CAUSES TO GO TO ERRORHANDLER. 88888888888
    Set cnn = Nothing
    Exit Sub
ErrorHandler:
    ' Grab Access/VBA Error
    lngError = Err.Number
    strError = Err.Description   <--------- GOES TO THIS LINE. 88888888888
    ' If ADO Error available grab
    ' the first one in the collection.
    If cnn.Errors.Count > 0 Then
        lngError = cnn.Errors(0).NativeError
        strError = cnn.Errors(0).Description
    End If
    MsgBox "Error " & lngError & ": " & _
     strError, vbCritical + vbOKOnly, _
     "frmCustomer Error"
    Resume ExitHere
End Sub

 

[Andrzejek]

Looks like you are trying to close your connection cnn, but you never set it up or opened it

You may try something like:

        Set cmd = Nothing
    Set prm = Nothing[blue]
    If Not(cnn Is Nothing) Then
        If cnn.State = 1 Then[/blue]
            cnn.Close[blue]
        End If
    End If[/blue]
    Set cnn = Nothing

I think State = 1 is Open, State = 0 is Closed

Have fun.

---- Andy

 

[Prospec]

Great that fixed it thank you very mucho!!!