Combo box and subforms

[thart21]

I have just switch to a SQL backend, linking the tables to my Access 2000 db and am having difficulty getting my Employee name combo box to retrieve the subform data.

No matter what I try from the forum suggestions I keep getting the same error "Object variable or With block variable not set" I have tried both ADO and DAO examples, With blocks, everything I can find and nothing works. The second set of code is what I used with both the FE and BE in Access and it worked perfectly so I can only assume it has something to do with the BE change to SQL (???) I have ensured that my ADO and DAO references are checked.

Dim rs As ADODB.Recordset

Set rs = Me.Recordset.Clone
If Not rs.BOF Then
rs.MoveFirst
rs.Find "[EmployeeID] = " & Me![Combo6].Value
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

I have 4 subforms that will be populated with the selected Employee's training data.

FE and BE in Access
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & str(Me![Combo6])
Me.Bookmark = rs.Bookmark

Thanks in advance.

Toni

 

[DB7]

NOT Set rs = Me.Recordset.Clone

BUT Set rs = Me.Recordsetclone

 

[DB7]

Forgot to mention,
I don't believe, ADO has a recordsetclone object.
You should be using DAO.

Dim rs As DAO.Recordset