Mel:
To add the GetFieldDescription function to a standard module go to the
modules page of the database window and select New. The VBA editor will
open
at a new module with a couple of lines already in place. Copy the
following
code to the clipboard and paste it in immediately below the existing lines
in
the module. The first line form 'Public' to the final 'As String' is all
one
line, but might have split into two in your newsgroup reader, so watch out
for this and correct it after pasting in if necessary. Then save the
module
under a name of your choice, but not the same name as the function; I
generally give module names a 'bas' prefix, e.g. basUtilities, to avoid
any
confusion with other object names.
Public Function GetFieldDescription(strTable As String, strField As
String)
As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strField)
GetFieldDescription = fld.Properties("Description")
End Function
The second line of code is not part of a function; it goes in each
relevant
form's Open event procedure, and you'll need to repeat it for each control
for which you want to show the field description as a tool tip. So open
the
form in design view and open its properties sheet if its not already open.
On the Event tab of the properties sheet select the On Open event property
and click on the 'build' button (the one on the right with 3 dots).
Select
'Code Builder' in the dialogue and OK out. This will open the VBA editor
at
the Open event procedure in the form's class module. The first and last
lines will be in place already. Enter variations on the following as new
lines between the existing two, one line for each control in question, and
change the control, table and field names from MyControl, MyTable and
MyField
in each line as necessary. Note again that this should be all one line if
its been split in your reader:
Me.MyControl.ControlTipText = GetFieldDescription("[MyTable]",
"[MyField]")
The form's Open event procedure executes when you open a form. This then
calls the GetFieldDescription function however many times are necessary,
i.e.
once for each relevant control, and assigns the value of the relevant
field's
Description property to the control's ToolTipText property. The tool tip
with the appropriate description will then show when a user moves the
mouse
pointer over the control.
Functions or procedures in a standard module are not called when the
database opens, but when explicitly called, as above. Sometimes, however,
you might want a function or procedure to be called immediately when the
database is opened, e.g. to check if links to tables in a shared back end
are
still valid. There are various ways you can do this, e.g. by an autoexec
macro or by including code in the Open event of a form such as a
switchboard
set as the database's opening form.
Sometimes you might want other functions or procedures to be called when a
database closes, e.g. to perform some tidying up housework tasks. A way
to
do this id to use a hidden form as the database's opening form and call
the
functions or procedures in its Close event procedure. The form will stay
open, but hidden, throughout the session, but will close when the database
closes, executing its Close event procedure to call the housework
functions
or procedures.
Ken Sheridan
Stafford, England
Ken,
I've done some programming but not much in Access.
How do I (where do I click) to add a function to the "standard
module" ???
And, am I correct in understanding that the first function would be
called each time the db opened?
And the 2nd function would be called each time the form opened??
thanks again.
Mel
