Combine month and date control results into 1 field

[Guest]

Hi,

I have a continuous subform detailing attendance, I need each record to be
given a month and year. Currently I'm trying to use a combo for month and one
for year, and I was hoping to somehow combine the two combo results into one
date field on the underlying table (mmmm/yy).

However, as the two combos are unbound, changing either combo on any one
record changes it for all records on the form. I'm not sure how to implement
a solution to this, can some help me out?

Thanks for reading

 

[Allen Browne]

Firstly, a date/time field will always be a full date (with a day, month,
and year), even if you format it so the day is not displayed. You will
therefore need to use something like the first of the month.

You can fudge what you are asking to do in a continuous form, with a couple
of text boxes that have this expression in their Control Source:
=Month([MyDate])
and:
=Year([MyDate])

Now place your two unbound combos behind the 2 text boxes, so the drop-down
arrow of the combo shows to the right of the text box. (In form design, use
Send To Back on the Format menu to place them behind.)

Use the Got Focus event of the text box to SetFocus to the combo. This makes
the combo jump in front of the text box, but Access handles that correctly
so it only appears that way on the current record and the other rows of the
continuous form still show correctly.

Use the GotFocus event of the combo to copy the value from the text box.

Use the AfterUpdate event of the combo to write to the date/time field. The
calculated text boxes will update themselves as the combo disapears behind
the text boxes again.

Set the Tab Stop property of the combos to No, and set the Tab Order
correctly for the text boxes.