Formatting on a Per Record Basis on a Sub-Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don - 01 May 2005 21:19 GMT

On an employee form, there is a sub-form which lists education (high
school, college, etc). The subform is rather simple and really just lists
the degree, graduation date, and some comments for each degree a person has.
(The subform gets its data from a table where the record contains fields for
employee number, degree, etc, so it is a one to many link with the employee
table.) We would like to format the graduation date field on a
record-by-record basis (education record that is). I had the following
compound IIF in the control source field:

=IIf([GradDateValidity]="Day",Format([GraduationDate],"mm/dd/yyyy"),(IIf([Gr
adDateValidity]="Month",
Format([GraduationDate],"mm/yyyy"),Format([GraduationDate],"yyyy"))))

However, during testing found this caused problems with trying to input
data. Basically, it does not like the IIF in the control source field for
entering data.

It comes down to modifying the Controls format property on a detail record
by detail record basis. Can this be done?

Thanks!

Don

Reply to this Message

Marshall Barton - 01 May 2005 21:58 GMT

>On an employee form, there is a sub-form which lists education (high
>school, college, etc). The subform is rather simple and really just lists
[quoted text clipped - 15 lines]
>It comes down to modifying the Controls format property on a detail record
>by detail record basis. Can this be done?

It can be done that way in Single view. In Continuous view,
you have to get a little tricky. AFAIK, it can not be done
in Datasheet view.

Assuming, your subform is in Continuous view, create another
text box bound to the GraduationDate field, no formatting.
Name this new text box txtGradDate and set its size and
location the same as the one with the formatting. Use
Format - Send to Back to place it behind the formatted text
box.

Add a line of code to the formatted text box's GotFocus
event to shift the focus to the new text box:
Me.txtGradDate.SetFocus

Signature

Marsh
MVP [MS Access]

Reply to this Message

Don - 02 May 2005 03:39 GMT

Marshall,

Maybe a "little tricky", but it seems like quite a slick solution! I hope
to work on the form a little tomorrow and will give it a try then.

Thanks!!!!

Don

> >On an employee form, there is a sub-form which lists education (high
> >school, college, etc). The subform is rather simple and really just lists
[quoted text clipped - 30 lines]
> event to shift the focus to the new text box:
> Me.txtGradDate.SetFocus

Reply to this Message