[RESOLVED] MS Access to SQL Linked tables connection

[FishGuy]

I am getting the following error on this pretty basic line of code hope someone can help.
Object Variable or With block variable not set
'MS Access VBA Code

Private Sub Form_Load()
Dim rs As Recordset
Dim cnn As Connection
[U]cnn = CurrentProject.Connection[/U]
rs.Open "SELECT * from Employees", cnn, , , adCmdText
Me.lstEmpStates.RowSourceType = ValueList
While Not rs.BOF And Not rs.EOF
'Me.lstEmpStates.Additem
rs.MoveNextWend
End Sub

[kaffenils]

Change the line to

set cnn = CurrentProject.Connection

[FishGuy]

Thanks that has taken me past that error, I now get the same error on the next line..
rs.Open "SELECT * from Employees", cnn, , , adCmdText

[kaffenils]

Thanks that has taken me past that error, I now get the same error on the next line..
rs.Open "SELECT * from Employees", cnn, , , adCmdText

The problem is that rs is not an instance of an object yet. Theis can be done with the New keyword when you declare the variable

dim rs as new recordset

or later by using the Set keyword

Set rs=New recordset

[FishGuy]

Thanks