having trouble with QueryDef fields

[kitty]

I have a subroutine where I would like to list the fields in a query
which resides in the database but I get an error: "Object variable as
woth block variable not set"

Code:
Public Sub test()
Dim db As Database
Dim qdf As QueryDef
Dim fld As Field

Set db = DBEngine.OpenDatabase("C:\Event.mdb")
Set qdf = db.QueryDefs("qrycodes")
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld
qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing
End Sub

Do you see anything I'm doing wrong in this code

 

[Dirk Goldgar]

I have a subroutine where I would like to list the fields in a query
which resides in the database but I get an error: "Object variable as
woth block variable not set"

Code:
Public Sub test()
Dim db As Database
Dim qdf As QueryDef
Dim fld As Field

Set db = DBEngine.OpenDatabase("C:\Event.mdb")
Set qdf = db.QueryDefs("qrycodes")
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld
qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing
End Sub

Do you see anything I'm doing wrong in this code

No, it looks okay, provided you have a reference to DAO and don't have a
reference to ADO, or else the DAO reference has a higher priority. The
code, modified to fit my test database, ran fine for me. What version
of Access are you running? Is the code running inside "C:\Event.mdb"
itself? If so, try using

Set db = CurrentDb

just in case you're locking yourself out.

 

[Ken Snell]

On which code line does the error occur? Are you using ACCESS 2000 or higher
version? If yes, do you have a reference to DAO library set?

 

[Douglas J. Steele]

No, it looks okay, provided you have a reference to DAO and don't have a
reference to ADO, or else the DAO reference has a higher priority.

Just to tag along on Dirk's post, if you do have references to both DAO and
ADO, change

Dim fld As Field

to

Dim fld As DAO.Field

 

[kitty]

I think you are right about the ADO and DAO. I believe that's my
problem. I will try your suggestions. I am getting the error on the
"For each fld In qdf.Fields" line. As if there is a problem with "fld"
so I will check on the ADO and DAO. THANKS!!

 

[kitty]

YES, YES!!! Victory!! Thank you it worked with DAO.Field.