[RESOLVED] MS Access to SQL Linked tables connection

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

VB 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

Change the line to

VB Code:

set cnn = CurrentProject.Connection

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

Quote:

---

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

---

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

VB Code:

dim rs as new recordset

or later by using the Set keyword

VB Code:

Set rs=New recordset

:D Thanks