updating recordset with VBA problem

[cman121]

I am testing this code to solve a larger problem


Private Sub Command0_Click()

Dim rst As Recordset
*** Set rst = CurrentDb.OpenRecordset("tblDepartment&quot
rst.Index = "PrimaryKey"
rst.AddNew
rst!DepartmentName = "new dept"
rst.Update
'rst.Bookmark = rst.LastModified
rst.MoveNext

End Sub
I get an error on the indicated line (***) that says there is a type mismatch (I assume trying to assign my table to a Recordset ojbect.) Do I need to change my table properties? Why is this a type mismatch?

 

[scriverb]

Try changing the dimension statement to the following:

Dim rst As DAO.Recordset
*** Set rst = CurrentDb.OpenRecordset("tblDepartment&quot

Post back with the results.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[chickey]

Looks like you are trying to open a table and not
a recordset, so change the line to the following:

Set rst = CurrentDb.OpenRecordset("select * from tblDepartment", dbOpenDynaset)

Regards...

 

[scriverb]

Chickey, using the table name as the Source is perfectly okay. The Type Constant "dbOpenDynaset" is optional and the absence of which does not trigger the error being found here.

I have tried numerous combinations of code and the only one that triggers the error that is being indicated is if the spelling of the tablename is incorrect or the DAO.Recordset is not used. So, since the tablename is more than likely spelled correctly I belive the addition of the DAO to the recordset declaration will solve the problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???