Recordset Issue- Please help!

[srpathak]

Hi Friends,
I am using Access 2002 with a SQL backend for a project. I am trying to
access a table in the database using Recordset, and I am popped with
the error.

*Object variable or WIth block variable not set *

Following is the code-

Dim DbTmp As Database
Dim rsCity As Recordset

Set DbTmp = CurrentDb()

Set rsCity = CurrentDb.OpenRecordset("Select * FROM [ZIPS]") ' This is
the source of error

rsCity.Find "CITY = '" & Me.CITY.Value & "'"

I have included the DAO library in my project. I tried using
DAO.Database and DAO.Recordset but no luck! I am just not able to
figure this thing out. Any help in this matter would be really great
for me!
Thank you.

Sincerely,
Sameer

 

[Malcolm Smith]

Hi

This could be because you are not using the pointer (object reference) to
the Database.

Does the following work, perhaps?


Dim oDB As DAO.Database
Dim oRS As DAO.Recordset

Set oDB = DBEngine.Workspaces(0).Databases(0)
Set oRS = oDB.OpenRecordset("Select * FROM ZIPS")


It's been many a long year since I did any DAO, so I am a little rusty
here. But these are the points that I have.

1. When I created the oDB reference I used the
DBEngine.Workspaces(0).Databases(0) to get to the CurrentDB() rather than
the direct call. This is because a long time ago there was a problem
with the CurrentDB() call and, no, I can't remember what it was either.
But basically it means that I am getting the zeroth database in the zeroth
workspace which translates to the current database.

2. When I am making the Dim statements note that I am using the library
name in case there is a clash with another library's Database or RecordSet
object.

3. I am using the reference to the oDB (the pointer to the database)
where as you are not. Also, I have given it a slightly better name than
yours.


This ought to work. Let me know if it doesn't.

- Malc
www.dragondrop.com