424 Object Required Error

[jatin32]

Hi,

I have below code on Form_Load() , I get one time error when I open this form.

424 Object required, please help ASAP.

Option Compare Database
Private Sub Form_Load()
 
On Error GoTo ThisFormError
 
Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String
Dim stSQL1 As String
Dim stConn As String
 
 
 'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
 
 'Path to the database.
stDB = "\\server23\abc\db.mdb"
 
 'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";Persist Security Info=False"
 
 
cnt.Open (stConn)
stSQL1 = "SELECT DISTINCT [table 1].[CH#] FROM [table1];"
 
 rst1.Open stSQL1, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
 
 
        rst1.MoveFirst
    With Me.Cmb_StoreNo
 
        Cmb_StoreNo.AddItem ("All ")
 
         Do While Not rst1.EOF
         Cmb_StoreNo.AddItem rst1![CH#]
         rst1.MoveNext
 
        Loop
 End With
 
ThisFormErrorExit:
        On Error Resume Next
        rst.Close
        Set rst = Nothing
        cnt.Close
        Set cnt = Nothing
ThisFormError:
 MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
 Resume ThisFormErrorExit
End Sub

[Stewart Ross]

Hi. I have added code tags to your code, which give line numbers that make it easier to refer to specific lines.

It would have helped us if you had advised where in your code your object error is arising. Otherwise we are guessing. Informed guesswork, but still guessing.

I'd suggest looking at what follows the With statement in line 32. If you wish to refer to your combo properties you need to use the dot notation to do so - which you are not doing (lines 34 and 37). As you already specified the name of the combo in line 32 you do not need to repeat this. Instead the syntax for lines 34 and 37 should be

.AddItem ("All ") 
.AddItem rst1![CH#]

As I said this is a guess at present - your error could be occurring before this point for all I know - but your syntax is definitely in error here.

-Stewart

[ADezii]

In your code block, modify Lines 34, 37, 45, and 46, then add Line XX. The corrected code is posted below for your convenience:

Private Sub Command84_Click()
On Error GoTo ThisFormError
 Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String
Dim stSQL1 As String
Dim stConn As String
 
'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
 
'Path to the database.
'stDB = "\\server23\abc\db.mdb"
stDB = "C:\Ed Herbst\Ed New.mdb"
 
 'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";Persist Security Info=False"
 
cnt.Open (stConn)
stSQL1 = "SELECT DISTINCT [table 1].[CH#] FROM [table1];"
 
rst1.Open stSQL1, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
 
rst1.MoveFirst
 
With Me.Cmb_StoreNo
  .AddItem ("All ")
    Do While Not rst1.EOF
      Cmb_StoreNo.AddItem rst1![CH#]
      rst1.MoveNext
    Loop
End With
 
ThisFormErrorExit:
  On Error Resume Next
  rst1.Close
  Set rst1 = Nothing
  cnt.Close
  Set cnt = Nothing
    Exit Sub
 
ThisFormError:
  MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
  Resume ThisFormErrorExit
End Sub

[ADezii]

In your code block, modify Lines 34, 37, 45, and 46, then add Line 42 in the corrected code block. The corrected code is posted below for your convenience, and the corrected lines in the revised code are: 29, 31, 38, and 39. There was never a rst Recordset in the 1st place, so you could never close it, ergo the Object Required Error. You must also Exit the Routine prior to the Msgbox Line (added Line 42).

Private Sub Command84_Click()
On Error GoTo ThisFormError
 Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String
Dim stSQL1 As String
Dim stConn As String
 
'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
 
'Path to the database.
'stDB = "\\server23\abc\db.mdb"
stDB = "C:\Ed Herbst\Ed New.mdb"
 
 'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";Persist Security Info=False"
 
cnt.Open (stConn)
stSQL1 = "SELECT DISTINCT [table 1].[CH#] FROM [table1];"
 
rst1.Open stSQL1, cnt, adOpenForwardOnly, adLockReadOnly, adCmdText
 
rst1.MoveFirst
 
With Me.Cmb_StoreNo
  .AddItem ("All ")
    Do While Not rst1.EOF
      .AddItem rst1![CH#]
      rst1.MoveNext
    Loop
End With
 
ThisFormErrorExit:
  On Error Resume Next
  rst1.Close
  Set rst1 = Nothing
  cnt.Close
  Set cnt = Nothing
    Exit Sub
 
ThisFormError:
  MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
  Resume ThisFormErrorExit
End Sub

[ADezii]

Hi. I have added code tags to your code, which give line numbers that make it easier to refer to specific lines.

It would have helped us if you had advised where in your code your object error is arising. Otherwise we are guessing. Informed guesswork, but still guessing.

I'd suggest looking at what follows the With statement in line 32. If you wish to refer to your combo properties you need to use the dot notation to do so - which you are not doing (lines 34 and 37). As you already specified the name of the combo in line 32 you do not need to repeat this. Instead the syntax for lines 34 and 37 should be

.AddItem ("All ") 
.AddItem rst1![CH#]

As I said this is a guess at present - your error could be occurring before this point for all I know - but your syntax is definitely in error here.

-Stewart

Hello Stewart! The code segment that you are referring to, although not generally an accepted format, is still nonetheless fully functional. Just some useless information. Take care.

[Stewart Ross]

Thanks for the clarification ADezii.

@ Jatin32 I do wish you had let us know where in your code the original error was occurring, as it would have avoided looking at other lines in the code which although non-standard (and indeed not requiring a WITH statement to work) were not the source of the problem.

-S

[jatin32]

Thanks for the clarification ADezii.

@ Jatin32 I do wish you had let us know where in your code the original error was occurring, as it would have avoided looking at other lines in the code which although non-standard (and indeed not requiring a WITH statement to work) were not the source of the problem.

-S

This error did not give me any line number.
I have commented out 'On error line and it worked. I have not tried this posted code yet, but I will try it out and let you know.

Thanks a lot guys, appreciated.