[RESOLVED]BIG problem run time error 91

[Christopher_Arm]

I am trying populate a listbox within a SStab control with data when the form loads from a SQL table (instead of the stored procedure I have within that I have commented out )and I get the following :

RUNTIME ERROR 91 OBJECT VARIABLE or block variable not set..

someone please help


Private Sub Form_Load()
'Dim rs As ADODB.Recordset
Dim sql_stm As String
Dim SADOconnect As String

Dim rs As ADODB.Recordset
Set rs = Adodc1.Recordset
'MsgBox rs.RecordCount


SADOconnect = "PROVIDER = SPWHPSQ00;" & _
"Data Source = ilr_test ;" & _
"Trusted_Connection = yes"

'sql_stm = "exec sp_ILR_Admin_select_Request_status"
sql_stm = "select * from Requests"

rs.Open sql_stm, SADOconnect, adOpenDynamic, adLockPessimistic
Set rs = Nothing
MsgBox rs(0)

End Sub

[manavo11]

On what line does the error occur?

[Christopher_Arm]

On what line does the error occur?

the rs.open sql_stm line.

[MartinLiss]

Why have you commented out this line?

'Dim rs As ADODB.Recordset

[manavo11]

Why this line?

Set rs = Adodc1.Recordset

I think it should be :

Set rs = New ADODB.Recordset

or just :

Dim rs As New ADODB.Recordset

could be wrong...

[manavo11]

Why have you commented out this line?

'Dim rs As ADODB.Recordset

It's farther down as well (redeclared).

[bushmobile]

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

[Christopher_Arm]

Now it READS with the run time error 3706 provider can not be found it may not be properly be installed.

[brucevde]

What type of database uses the SPWHPSQ00 provider?

[Christopher_Arm]

it is a SQL database that houses my stored procedures and tables.

[brucevde]

The provider name for SQL Server is sqloledb.

Is SPWHPSQ00 the name of your Server?

"provider=sqloledb;data source=SPWHPSQ00;initial catalog=ilr_test;trusted_connection=yes"

or
"provider=sqloledb;data source=SPWHPSQ00;initial catalog=ilr_test;integrated security=sspi"

[Christopher_Arm]

The provider name for SQL Server is sqloledb.

Is SPWHPSQ00 the name of your Server?

"provider=sqloledb;data source=SPWHPSQ00;initial catalog=ilr_test;trusted_connection=yes"

or
"provider=sqloledb;data source=SPWHPSQ00;initial catalog=ilr_test;integrated security=sspi"

Great thanks guys, but now how do i display the data in the listbox on the form load within an SSTABcontrol on the form ?

[manavo11]

Something like this :

If Rs.RecordCount <> 0 Then
        Rs.MoveFirst
        Do While Not Rs.EOF
            cboCity.AddItem Rs.Fields("Name")
            Rs.MoveNext
        Loop
        
        Rs.Close
        Set Rs = Nothing
    End If

[Christopher_Arm]

Something like this :

If Rs.RecordCount <> 0 Then
        Rs.MoveFirst
        Do While Not Rs.EOF
            cboCity.AddItem Rs.Fields("Name")
            Rs.MoveNext
        Loop
        
        Rs.Close
        Set Rs = Nothing
    End If

This part I am curious about cbocity.addItem does what exactly and does rs.Fields take the the field name references from the table itself ?
cboCity.AddItem Rs.Fields("Name")

And how would this work with a listview ?

[manavo11]

cboCity is the name of a combobox. Change it with the name of your listbox.

The Fields gives you access to the columns you have selected from the select statement above.

[Christopher_Arm]

How do I populate multiple fields in the list box ?

[manavo11]

Listbox.AddItem Rs.Fields("SomeField") & vbTab & Rs.Fields("AnotherField")

Or replace vbTab with a space, an underscore or whatever you want.

[Christopher_Arm]

Listbox.AddItem Rs.Fields("SomeField") & vbTab & Rs.Fields("AnotherField")

Or replace vbTab with a space, an underscore or whatever you want.

Thanks guys you've been a big help.

PS. I want to do this trick for every listbox that appears within each tab i nthe tab control. There are four of them is there anything else to consder here when doing that ?

[jcis]

Nothing different I think, just use <ListboxName>.AddItem <Your Items> with all of them.