Uppercase only fix and prevent

[Bob Quintal]

One complete fool wrote in

I have an Access 2003 database.

There is one field which must always be all uppercase.

I have just discovered that about 3500 records have been
entered in mixed case.

Is there a way to automatically change the case without the
need to retype manually?

And is there a way to prevent this from happening so that no
matter what is typed only uppercase will be entered into the
field?

Thank you

Sure. Write a query that does an update of fieldname to UCASE
(fieldname)

In the AfterUpdate event of the control for this field, on the
form used for data entry, just put me.txtboxname = ucase
(me.txtboxname) - change txtboxname to the real name for that
control.

 

[One complete fool]

I have an Access 2003 database.

There is one field which must always be all uppercase.

I have just discovered that about 3500 records have been entered in
mixed case.

Is there a way to automatically change the case without the need to
retype manually?

And is there a way to prevent this from happening so that no matter
what is typed only uppercase will be entered into the field?

Thank you

 

[Douglas J. Steele]

You could always use an Update query:

UPDATE MyTable
SET MyField = UCase([MyField])

To ensure that it's always in upper case, use the UCase function in the
form's BeforeUpdate event. If you're not using a form, there's nothing you
can do.

 

[Klatuu]

You mean Before Update event, don't you. After is too late.

 

[Graham Mandeno]

Hi Bob

Actually, strange as it may seem, it IS the AfterUpdate event you should
use.

You cannot change the data in a control during its BeforeUpdate event
because you get the following message:
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Access from saving the data in the field.

 

[missinglinq via AccessMonster.com]

Graham is right, you have to use the textbox's AfterUpdate event, not the
BeforeUpdate, for the reason he stated. You can, as Doug stated, use the
BeforeUpdate event of the Form! Ain't Access grand?

 

[Bob Quintal]

You mean Before Update event, don't you. After is too late.

No I meant AfterUpdate of the Control, not BeforeUpdate of the
Form.

 

[Bob Quintal]

Hi Bob

Actually, strange as it may seem, it IS the AfterUpdate event
you should use.

You cannot change the data in a control during its
BeforeUpdate event because you get the following message:
The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing Microsoft
Access from saving the data in the field.

I know that. You can use the beforeUpdate, with the control.text
property, which is only available while the control has focus. I've
found this to be a much more complex situation, with possibilities
for error.

And it is possible to use the BeforeUpdate of the Form, but again
you need to ensure that the focus is not on the control you are
trying to uppercase, which can happen