[RESOLVED]BIG problem run time error 91

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 :cry:


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

On what line does the error occur?

Quote:

---

Originally Posted by manavo11

On what line does the error occur?

---

the rs.open sql_stm line.

Why have you commented out this line?

'Dim rs As ADODB.Recordset

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...

Quote:

---

Originally Posted by MartinLiss

Why have you commented out this line?

'Dim rs As ADODB.Recordset

---

It's farther down as well (redeclared).

VB Code:

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

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

What type of database uses the SPWHPSQ00 provider?

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

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"

Quote:

---

Originally Posted by 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"

---

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

Something like this :

VB Code:

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

Quote:

---

Originally Posted by manavo11

Something like this :

VB Code:

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 ?

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.

How do I populate multiple fields in the list box ?

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

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

Quote:

---

Originally Posted by manavo11

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. :D

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 ?

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