Formatting on a Per Record Basis on a Sub-Form

  • Thread starter Thread starter Don
  • Start date Start date
D

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
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
 
Don said:
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?
Click to expand...


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
 
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





Marshall Barton said:
Don said:
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([G
Click to expand...
r
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?
Click to expand...


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
Click to expand...
 
Back
Top