VBA to alter a date to the 1st of the month

[ECEK]

When I close my form I wish my "MOEB" date field to default to the 1st of the month that had been selected.

User enters any date via the inbuilt date picker. ie. 08/08/2016

They close the form and the field updates to 01/08/2016

It would also be preferable to do this "After Update" on the field itself but im not sure if that is possible?

 

[Isskint]

You will be able to perform this in the AfterUpdate event(). Have a look at the DateSerial() function.

mycontrol = DateSerial(Year(mycontrol),Month(mycontrol),1)

 

[arnelgp]

use BeforeUpdate event not AfterUpdate.

 

[Isskint]

use BeforeUpdate event not AfterUpdate.

If you try changing the value of a control in BeforeUpdate(), it will generate an error ..."prevent Access from saving the data in the field". It would need to be AfterUpdate()

 

[arnelgp]

no it doesn't. already tested it.

 

[TJPoorman]

no it doesn't. already tested it.

You guys may want to specify WHICH After_Update and Before_Update event to use.
If you use the Before_Update event of the control it will generate an error as Isskint says.
However, using the Before_Update event of the form will work as arnelgp states.

 

[Isskint]

You guys may want to specify WHICH After_Update and Before_Update event to use.

Fair point TJ, however the OP did say ..."It would also be preferable to do this "After Update" on the field itself..", so i did not see the need to clarify as i was addressing the OP's requirements.

 

[ECEK]

Thanks Guys. Job well done by all. Much appreciated.