This is a multi-part message in MIME format.



------=_NextPart_000_0007_01C1A4E8.3EBC99C0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Hi



The code below gives me error 91

object variable or wih block not set.







The code runs OK but it still produces this error. It's almost like  a 

subtle background error.



On Error GoTo Error_combobugnumber_AfterUpdate

  

   Dim apRoutineError As String

   apRoutineError =3D "combobugnumber_AfterUpdate"

  





'code to calculate the number of subscribers

Dim cnn As ADODB.Connection

Dim rst1 As ADODB.Recordset

Dim strSQL As String

Dim strSQL2 As String

Dim strnumber As String



Set cnn =3D CurrentProject.Connection



Set rst1 =3D New ADODB.Recordset

rst1.ActiveConnection =3D cnn



strnumber =3D Me!combobugnumber



strSQL =3D "SELECT Count(MainID) AS [TotalCount]" & _

         "FROM Request_email where Request_email.MainID =3D " & 

strnumber & ""





rst1.Open strSQL, cnn, adOpenStatic



On Error Resume Next



' exit sub on the off chance there are no records

 If rst1!TotalCount =3D 0 Then

    Exit Sub

    rst1.Close

    cnn.Close

    Set rst1 =3D Nothing

    Set cnn =3D Nothing



 Else

    Me!emailsubnumber =3D rst1!TotalCount

 End If



    rst1.Close

    cnn.Close

    Set rst1 =3D Nothing

    Set cnn =3D Nothing

   



Error_combobugnumber_AfterUpdate:



   apCurrErrNo =3D Err.Number

   apCurrErrMsg =3D Err.Description

   

   ap_ErrorLog apModuleError, apRoutineError, apCurrErrNo, _

                apCurrErrMsg, cnn

   Select Case ap_ErrorHandler(apCurrErrNo, apCurrErrMsg)

       Case apTryAgain

           Resume

       Case apExitRoutine

           Exit Sub

       Case apResumeNext

           Resume Next

   End Select



Thanks andrew

This is a multi-part message in MIME format.



------=_NextPart_000_0009_01C1A4A3.1C18B3D0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: 7bit



Andrew,



If you have a !TotalCount of zero, you will set your recordset and

connection to nothing.



Then, after that "If" clause, you try to access the objects again to close

them but, at this point, neither of these objects is set. Hence the error.



Instead of using "On Error Resume Next" to work around this,use:



 If Not rst1 Is Nothing Then

        If rst1.State = adStateOpen Then

            rst1.Close

        End If

        Set rst1 = Nothing

    End If



    If Not cnn Is Nothing Then

        If cnn.State = adStateOpen Then

            cnn.Close

        End If

        Set cnn = Nothing

    End If





Generally speaking, unless an error is in an external app or otherwise

beyond your control, it is preferable to eliminate the reasons for the error

(as above) OR, if necessary, trap for a SPECIFIC error and act accordingly

rather than rely on "On Error Resume Next".





Ian Ashton







-----Original Message-----

From: AAustin [mailto:plant-tech@x...]

Sent: Thursday, January 24, 2002 2:03 AM

To: Access

Subject: [access] error 91 object variable or wih block not set????





  Hi



  The code below gives me error 91

  object variable or wih block not set.







  The code runs OK but it still produces this error. It's almost like  a

subtle background error.



  On Error GoTo Error_combobugnumber_AfterUpdate



     Dim apRoutineError As String

     apRoutineError = "combobugnumber_AfterUpdate"







  'code to calculate the number of subscribers

  Dim cnn As ADODB.Connection

  Dim rst1 As ADODB.Recordset

  Dim strSQL As String

  Dim strSQL2 As String

  Dim strnumber As String



  Set cnn = CurrentProject.Connection



  Set rst1 = New ADODB.Recordset

  rst1.ActiveConnection = cnn



  strnumber = Me!combobugnumber



  strSQL = "SELECT Count(MainID) AS [TotalCount]" & _

           "FROM Request_email where Request_email.MainID = " & strnumber &

""





  rst1.Open strSQL, cnn, adOpenStatic



  On Error Resume Next



  ' exit sub on the off chance there are no records

   If rst1!TotalCount = 0 Then

      Exit Sub

      rst1.Close

      cnn.Close

      Set rst1 = Nothing

      Set cnn = Nothing



   Else

      Me!emailsubnumber = rst1!TotalCount

   End If



      rst1.Close

      cnn.Close

      Set rst1 = Nothing

      Set cnn = Nothing





  Error_combobugnumber_AfterUpdate:



     apCurrErrNo = Err.Number

     apCurrErrMsg = Err.Description



     ap_ErrorLog apModuleError, apRoutineError, apCurrErrNo, _

                  apCurrErrMsg, cnn

     Select Case ap_ErrorHandler(apCurrErrNo, apCurrErrMsg)

         Case apTryAgain

             Resume

         Case apExitRoutine

             Exit Sub

         Case apResumeNext

             Resume Next

     End Select



  Thanks andrew