Data can't be saved to the field

Hi.

BeforeUpdate code:
Me!cboFoo.Text = UCase(Me!cboFoo.Text)

I get the error:

Run-time error '2115':

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.

Why is that?

--
Georges

re: Data can't be saved to the field

I'm not sure why, but Access won't let you change a control's contents during
BeforeUpdate. My work-around has always been to do this sort of thin in the
AfterUpdate handler. You won't be able to use the .Text property there, so
just use the value, and check for Null. By the way, since you're describing a
combo box here, the value should automatically be converted to the same case
as the matching selection with no code whatsoever.

Anyway, if it were a text box, this is an exaple of how I would do it...

Private Sub txtFoo_AfterUpdate()
If IsNull(Me!txtFoo) Then Exit Sub
Me!txtFoo = UCase(Me!txtFoo)
End Sub


On Wed, 10 Dec 2003 01:41:05 +0100, Georges Heinesch <news@geohei.lu> wrote:
[color=blue]
>Hi.
>
>BeforeUpdate code:
> Me!cboFoo.Text = UCase(Me!cboFoo.Text)
>
>I get the error:
>
> Run-time error '2115':
>
> 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.
>
>Why is that?[/color]

re: Data can't be saved to the field

Steve Jorgensen wrote:
[color=blue]
> I'm not sure why, but Access won't let you change a control's contents during
> BeforeUpdate. My work-around has always been to do this sort of thin in the
> AfterUpdate handler. You won't be able to use the .Text property there, so
> just use the value, and check for Null.[/color]

This works, but ... (see below)
[color=blue]
> By the way, since you're describing a
> combo box here, the value should automatically be converted to the same case
> as the matching selection with no code whatsoever.[/color]

In case the "Auto Expand" is set to Yes. But in one of my controls it's not.
[color=blue]
> Anyway, if it were a text box, this is an exaple of how I would do it...
>
> Private Sub txtFoo_AfterUpdate()
> If IsNull(Me!txtFoo) Then Exit Sub
> Me!txtFoo = UCase(Me!txtFoo)
> End Sub[/color]

Yep, working. Preferntially, I would like to have the entered letters be
converted already upon input from lowercase to uppercase. I.o.w., all
entered letters (also lowercase) should show up as uppercase. I tried it
with a mask (>aaaa). To my great surprise, I saw that with "Auto Expand"
of a ComboBox set to Yes, the text completes to the end when entering
the first letter.

I explain:

ComboBox options:
ABCD
EFGH
IJKL

Without the mask:
A ABCD (BCD inverted)
The second entered letter goes after the A

With the mask:
A ABCD
The entire 4 letters "ABCD" are already completed

The latter behaviour is not required. How can I use a mask and make
Access behave like for the first situation (without a mask)?

TIA

--
Georges

re: Data can't be saved to the field

Georges,
[color=blue]
> Yep, working. Preferntially, I would like to have the entered letters be
> converted already upon input from lowercase to uppercase. I.o.w., all
> entered letters (also lowercase) should show up as uppercase. I tried it
> with a mask (>aaaa). To my great surprise, I saw that with "Auto Expand"
> of a ComboBox set to Yes, the text completes to the end when entering
> the first letter.[/color]

Use the Change-event to convert to UpperCase upon input.:
Me!cboName.text=UCase(Me!cboName.text)


--
Hope this helps
Arno R

re: Data can't be saved to the field

On Wed, 10 Dec 2003 08:49:33 +0100, "Arno R" <arracomn_o_s_p_a_m@tiscali.nl>
wrote:
[color=blue]
>Georges,
>[color=green]
>> Yep, working. Preferntially, I would like to have the entered letters be
>> converted already upon input from lowercase to uppercase. I.o.w., all
>> entered letters (also lowercase) should show up as uppercase. I tried it
>> with a mask (>aaaa). To my great surprise, I saw that with "Auto Expand"
>> of a ComboBox set to Yes, the text completes to the end when entering
>> the first letter.[/color]
>
>Use the Change-event to convert to UpperCase upon input.:
>Me!cboName.text=UCase(Me!cboName.text)[/color]

There's a problem with the Change event, and changing the .Text property.
When you set the Text property, Access insists on trying to update the Value
immediately as if you had tabbed out of the field, and the data may not yet be
valid after any given keypress. Instead, I would use the OnKeypress event,
and change the Ascii code of any lower case letter to an upper case one.

re: Data can't be saved to the field

Steve,[color=blue]
> There's a problem with the Change event, and changing the .Text property.
> When you set the Text property, Access insists on trying to update the Value[/color]

I was not aware of that. Thanks.
Never ran into this problem because where I used it, there is no validation needed for the input.
It explains the 'kind of screen-flickering' though.

Arno R

re: Data can't be saved to the field

Arno R wrote:
[color=blue]
> Use the Change-event to convert to UpperCase upon input.:
> Me!cboName.text=UCase(Me!cboName.text)[/color]

The .Text property does a great deal more than just chaning the content
of a control (not the field). It triggers some events (e.g.
BeforeUpdate, ...) without this being desired.

--
Georges

re: Data can't be saved to the field

Steve Jorgensen wrote:
[color=blue][color=green]
>>Use the Change-event to convert to UpperCase upon input.:
>>Me!cboName.text=UCase(Me!cboName.text)[/color]
>
> There's a problem with the Change event, and changing the .Text property.
> When you set the Text property, Access insists on trying to update the Value
> immediately as if you had tabbed out of the field, and the data may not yet be
> valid after any given keypress. Instead, I would use the OnKeypress event,
> and change the Ascii code of any lower case letter to an upper case one.[/color]

Which mothod would you then use inside the OnKeyPress event to change
the control's content using VBA? The .Text or .Value or ... property?

I discovered that the very trivial task of changing the content of a
control is not trivial at all. There are 2 possibilities.

1. .Text property. This triggers some events right after the .Text
property is executed.

2. .Value property. This doesn't trigger any events, also not the usual
events (e.g. BeforeUpdate, ...) when leaving the control. This is not
desired either.

Simply changing the content of a control without influencing the trigger
behaviour of events doesn't seem to be possible.

--
Georges

re: Data can't be saved to the field

On Wed, 10 Dec 2003 11:20:47 +0100, Georges Heinesch <news@geohei.lu> wrote:
[color=blue]
>Steve Jorgensen wrote:
>[color=green][color=darkred]
>>>Use the Change-event to convert to UpperCase upon input.:
>>>Me!cboName.text=UCase(Me!cboName.text)[/color]
>>
>> There's a problem with the Change event, and changing the .Text property.
>> When you set the Text property, Access insists on trying to update the Value
>> immediately as if you had tabbed out of the field, and the data may not yet be
>> valid after any given keypress. Instead, I would use the OnKeypress event,
>> and change the Ascii code of any lower case letter to an upper case one.[/color]
>
>Which mothod would you then use inside the OnKeyPress event to change
>the control's content using VBA? The .Text or .Value or ... property?[/color]

None of the above. Change the value of KeyAscii instead, so it's as if the
upper case letter was typed instead of the lower case letter.

Private Sub txtFoo_KeyPress(KeyAscii As Integer)
If KeyAscii >= 32 Then
Debug.Print KeyAscii;
KeyAscii = Asc(UCase$(Chr$(KeyAscii)))
Debug.Print " " & KeyAscii
End If
End Sub

re: Data can't be saved to the field

Steve Jorgensen wrote:
[color=blue]
> None of the above. Change the value of KeyAscii instead, so it's as if the
> upper case letter was typed instead of the lower case letter.
>
> Private Sub txtFoo_KeyPress(KeyAscii As Integer)
> If KeyAscii >= 32 Then
> Debug.Print KeyAscii;
> KeyAscii = Asc(UCase$(Chr$(KeyAscii)))
> Debug.Print " " & KeyAscii
> End If
> End Sub[/color]

Works great!
I changed it slightly into ...

Private Sub txtFoo_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Why did you include the >= 32 condition?

--
Georges

re: Data can't be saved to the field

On Wed, 10 Dec 2003 15:45:27 +0100, Georges Heinesch <news@geohei.lu> wrote:
[color=blue]
>Steve Jorgensen wrote:
>[color=green]
>> None of the above. Change the value of KeyAscii instead, so it's as if the
>> upper case letter was typed instead of the lower case letter.
>>
>> Private Sub txtFoo_KeyPress(KeyAscii As Integer)
>> If KeyAscii >= 32 Then
>> Debug.Print KeyAscii;
>> KeyAscii = Asc(UCase$(Chr$(KeyAscii)))
>> Debug.Print " " & KeyAscii
>> End If
>> End Sub[/color]
>
>Works great!
>I changed it slightly into ...
>
>Private Sub txtFoo_KeyPress(KeyAscii As Integer)
> KeyAscii = Asc(UCase(Chr(KeyAscii)))
>End Sub
>
>Why did you include the >= 32 condition?[/color]

Codes less than 32 are non-printing control characters such as Enter or Tab.
I wasn't sure how all the string functions would handle them, so I thought it
safer to skip processing of them.