Create a global field

[Guest]

How can I create a global field, (contains one value for all records in a
file), in Access 2003? In Filemaker Pro I just set it as the field type, but
that isn't one of the options in Access.

 

[Brendan Reynolds]

You could do it in a query. Something like ...

SELECT MyTable.*, 1 AS MyGlobalField FROM MyTable

 

[Guest]

If you want a single value to relate to all rows in a table you should create
a second table with just one row, containing that value in a column. You
should then reference that row from the other table via a foreign key column.
I'd suggest that you give the single row table a numeric primary key column
in addition to its other field and set the DefaultValue of the foreign key in
the referencing table to the value of the other table's primary key. Ensure
referential integrity is enforced.

If you were to store the same value multiple times in a column in the
referencing table without its value being constrained to the one value in
another table by means of enforced referential integrity there would be
nothing to stop the value being changed in one or more rows and consequently
no longer being 'global'.

I must admit, however, that I find it hard to envisage a situation where
you'd want to do this?

Ken Sheridan
Stafford, England

 

[Guest]

Thanks. Actually, there are lots of reasons to use global fields in Filemaker
- mostly to do with scripting. For instance, I can write a script to backup a
copy every 5th time the database closes by having a global field count how
many times the file has been opened.

Or if I am in a Table!Contact record, and I want to click a button to view
all their invoices, I can create a script to go to insert their id into a
global field, and when I go to a layout based on Table!Invoices, it will pick
up the value in the global ID and run a search on Invoices whose ID matches
that. It is just a quick and easy method in FMP. I just need to adjust my
thinking for Access. I wish I could combine those two programs!!

Thanks! -NB

 

[Ron2005]

Here is one approach that works for MDB jet type access files in which
the global fields are for use by the user running this instance of the
application..

Create a form (example called: HiddenForm)
Place on that form as many fields/ as you want to use as "Global"
within the application.
In the onload event of your main menu are whatever form is ALWAYS
opened, issue
docmd.openform "HiddenForm",,,,,achidden ' the
number of commas may or may not be correct here.

On any form that is going to initiate a query or code etc that will
require a global criteria or global type piece of information, update
that information and then intiate the process.

In those processes refer to
forms![HiddenForm]![gblfieldname] ' whatever it is called.

The hiddenform is addressable from ANY form/query/module/macro etc from
within that mdb.


Hope this give you some ideas. We have used it a lot in our
applications here. It can make queries/forms very re-usable since they
can be called from many forms and get the proper results without having
to have a differnet form for every call based on the calling forms
criteria and not having to reconstruct the query repeatedly.

Ron