Dynamic Field Names/Labels

[Guest]

I have a table with 3 fields; I'll call them Time1, Time2, Time3. For
different users these three fields represent different times. For example
for User1 - Time1 represents "24 hours" and for User2 - Time1 represents "48
Hours". These values are set in another table "Time Categories".

On a regular form or report I use text boxes instead of labels for column
headers. In the text box I have written a function that will look up what
Time1 should represent for that particular user (again based on the "Time
Categories" table). That all works great.

The function is GetLabel("Time Categories", 1) which executes a simple SQL
statement and returns the value that I want for the label.

The issue is forms/queries I want to view in a datasheet format. I need to
have the column header in the datasheet view show this dynamic label instead
of the field name ("Time1").

The only way I have found to do it is to change the label for the field in
the Form_Load event to the value returned by my function. This seems to work
but is a bit akward.

Does anyone have any idea how to do this in the query definition as opposed
to having to write event code each and every time I want to do this?

Thanks!

 

[Marshall Barton]

I have a table with 3 fields; I'll call them Time1, Time2, Time3. For
different users these three fields represent different times. For example
for User1 - Time1 represents "24 hours" and for User2 - Time1 represents "48
Hours". These values are set in another table "Time Categories".

On a regular form or report I use text boxes instead of labels for column
headers. In the text box I have written a function that will look up what
Time1 should represent for that particular user (again based on the "Time
Categories" table). That all works great.

The function is GetLabel("Time Categories", 1) which executes a simple SQL
statement and returns the value that I want for the label.

The issue is forms/queries I want to view in a datasheet format. I need to
have the column header in the datasheet view show this dynamic label instead
of the field name ("Time1").

The only way I have found to do it is to change the label for the field in
the Form_Load event to the value returned by my function. This seems to work
but is a bit akward.

Does anyone have any idea how to do this in the query definition as opposed
to having to write event code each and every time I want to do this?

If the text boxes in a datasheet form do not have attached
labels, then the column header uses the name of the text
box.

However, I seriously think changing the attached lable's
Caption is the only practical approach.