why do you have to declare currentdb?

[WindAndWaves]

I recently made a very simple little function:
Function x()
Dim Tbl As TableDef
Dim fld As DAO.Field
Set Tbl = currentdb.TableDefs("search")
Debug.Print Tbl.Name
For Each fld In Tbl.Fields
Debug.Print fld.Name
Next fld

To my astonishment, it gave an error: object no longer set/not valid

Then I changed it to

Public Function xx()
Dim Tbl As TableDef
Dim fld As DAO.Field
Dim Dbs As Database

Set Dbs = CurrentDb

Set Tbl = Dbs.TableDefs("search")
Debug.Print Tbl.Name
For Each fld In Tbl.Fields
Debug.Print fld.Name
Next fld
End Function

and it worked.

Can someone tell me why you explicitly have to declare the current database?

TIA

- Nicolaas

[Allen Browne]

CurrentDb creates a new instance of the dbEngine(0)(0) after flushing all
collections. In the context you describe, this instance goes out of scope if
you do not point it to a variable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:qU*******************@news.xtra.co.nz...

I recently made a very simple little function:
Function x()
Dim Tbl As TableDef
Dim fld As DAO.Field
Set Tbl = currentdb.TableDefs("search")
Debug.Print Tbl.Name
For Each fld In Tbl.Fields
Debug.Print fld.Name
Next fld

To my astonishment, it gave an error: object no longer set/not valid

Then I changed it to

Public Function xx()
Dim Tbl As TableDef
Dim fld As DAO.Field
Dim Dbs As Database

Set Dbs = CurrentDb

Set Tbl = Dbs.TableDefs("search")
Debug.Print Tbl.Name
For Each fld In Tbl.Fields
Debug.Print fld.Name
Next fld
End Function

and it worked.

Can someone tell me why you explicitly have to declare the current
database?

TIA

- Nicolaas

[Bas Cost Budde]

WindAndWaves wrote:

Can someone tell me why you explicitly have to declare the current database?

Because every invocation of CurrentDb returns a fresh pointer, which
does generally not point to the same object as before.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea