Populate listview window problem 1

[vamoose]

Hello, this is dealing with Access 2003 and SQL 2000. I am trying to populate a list box using the following code:

Dim cn As ADODB.Connection: Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Dim SQLa As String
SQLa = "Select * from turnspec.dbo.turncurrent order by Analyzer"
cn.Open "driver={SQL Server};server=serverms001;database=turnspec;Trusted_Connection=Yes"

With [Forms]![frm_Schedule_list].ListView1: .ListItems.Clear: .ColumnHeaders.Clear: End With
With [Forms]![frm_Schedule_list].ListView1.ColumnHeaders
.Add , , "Analyzer", 700, lvwColumnLeft
.Add , , "Coil", 700, lvwColumnLeft
.Add , , "Checked", 1000, lvwColumnLeft
.Add , , "Cell", 2200, lvwColumnLeft
.Add , , "Wkorder", 1100, lvwColumnLeft
.Add , , "LastUpdate", 1000, lvwColumnLeft
End With
rs.MoveFirst
Do Until rs.EOF
Set lstitem = [Forms]![frm_Schedule_list].ListView1.ListItems.Add()
lstitem.Text = rs!Analyzer
lstitem.SubItems(1) = rs!Coil
lstitem.SubItems(2) = rs!Checked
lstitem.SubItems(3) = rs!Cell
lstitem.SubItems(4) = rs!Wkorder
lstitem.SubItems(5) = rs!LastUpdate
rs.MoveNext: Loop: rs.Close: DoCmd.Echo True: Call FormatColorsTurnSpec

But there is always an error at the rs.MoveFirst line of code. I have used this code before but not with a SQL database. Am I doing something wrong ?

Error: Object variable or with block variable not set, run-time error 91.

Thanks for any advice.

 

[PHV]

The recordset is never instantiated (opened) ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886