User defined function and tables

[DZ]

Hello

Has anyone ever noticed that if you fill a listbox using the user defined
function to create a list of all tables in a database, if you create a new
table, the new table won't appear in the list box even if you close and
reopen the form that contains the listbox.

The new table will only apear in the listbox if you quit and then reopen the
database.

Access 97.

Any moments or workarounds ?

 

[Stuart McCall]

Hello

Has anyone ever noticed that if you fill a listbox using the user defined
function to create a list of all tables in a database, if you create a new
table, the new table won't appear in the list box even if you close and
reopen the form that contains the listbox.

The new table will only apear in the listbox if you quit and then reopen
the
database.

Access 97.

Any moments or workarounds ?

Presumably in your listfill function you're looping over the DAO TableDefs
collection? If so, try:

CurrentDb.TableDefs.Refresh

inside the acLBInitialize section of the function.

 

[Allen Browne]

Depends how you store the data for the listbox.

If you use a callback function in a standard moudule, and the open call
stores the table names into a static array, you will get the behavior you
describe.

A simpler alternative might be to use a normal query as the RowSource for
the list box. Something like this:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((MSysObjects.Name Not Like 'MSys*')
AND (MSysObjects.Type In (1,4,6)))
ORDER BY MSysObjects.Name;

 

[DZ]

Thanks Allen. Great solution !

Regards
DZ