Warning users about updating a text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I want to warn users who change the contents of a text box with a message box. Basically, what I want to happen is that before the Update event occurs, a message box appears with a warning. The user can click Okay to allow the Update to proceed, or Cancel to restore the text box to its OldValue. I have tried assigning my code to two Events: OnChange and BeforeUpdate.

If I assign the code to OnChange, the message box appears whenever the user types or deletes any letter. For example, if they typed the word "text" into the text box, the message box would appear four times, one for each letter in the word "text". This is clearly not satisfactory.

If I assign the code to BeforeUpdate, I get the following error:

Run-time error ‘-2147352567 (80020009)’

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing [Name of database] from saving the data in the field.

The code I am using is as follows:

Dim Response As Integer
Response = MsgBox("Changing the name of this Organisation will change its name wherever it appears." & Chr(13) & Chr(13) & "Do you want to continue?", vbOKCancel, "Change name of Organisation")
If (Response = vbCancel) Then
[Organisation] = [Organisation].OldValue
End If

where [Organisation] is the name of the text box.

Can anyone explain the meaning of this error, or tell me how to do this?

Many thanks

David Cleave
 
BeforeUpdate is the suitable event.

If your code crashes, your database is partially corrupt. The most likely
case is Name AutoCorrect. See:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David Cleave said:
Hi all

I want to warn users who change the contents of a text box with a message
Click to expand...
box. Basically, what I want to happen is that before the Update event
occurs, a message box appears with a warning. The user can click Okay to
allow the Update to proceed, or Cancel to restore the text box to its
OldValue. I have tried assigning my code to two Events: OnChange and
BeforeUpdate.
If I assign the code to OnChange, the message box appears whenever the
Click to expand...
user types or deletes any letter. For example, if they typed the word "text"
into the text box, the message box would appear four times, one for each
letter in the word "text". This is clearly not satisfactory.
If I assign the code to BeforeUpdate, I get the following error:

Run-time error '-2147352567 (80020009)'

The macro or function set to the BeforeUpdate or ValidationRule property
Click to expand...
for this field is preventing [Name of database] from saving the data in the
field.
The code I am using is as follows:

Dim Response As Integer
Response = MsgBox("Changing the name of this Organisation will change its
Click to expand...
name wherever it appears." & Chr(13) & Chr(13) & "Do you want to continue?",
vbOKCancel, "Change name of Organisation")
If (Response = vbCancel) Then
[Organisation] = [Organisation].OldValue
End If

where [Organisation] is the name of the text box.

Can anyone explain the meaning of this error, or tell me how to do this?

Many thanks

David Cleave
Click to expand...
 
David,

Try replacing the following code:
If (Response = vbCancel) Then
[Organisation] = [Organisation].OldValue
End If
Click to expand...

with:

If (Response = vbCancel) Then
' Undo the changes (set the value back to the original one before the
record was modified)
Me.TextBoxName.Undo
' Cancel the update of the combobox
Cancel = True
End If

Cancel is an integer passed in that when set to true will cancel the event.
I have found that if you try to change the value of the text to different
text in this event, you will get the error message that you have described.

HTH,

Neil.

David Cleave said:
Hi all

I want to warn users who change the contents of a text box with a message
Click to expand...
box. Basically, what I want to happen is that before the Update event
occurs, a message box appears with a warning. The user can click Okay to
allow the Update to proceed, or Cancel to restore the text box to its
OldValue. I have tried assigning my code to two Events: OnChange and
BeforeUpdate.
If I assign the code to OnChange, the message box appears whenever the
Click to expand...
user types or deletes any letter. For example, if they typed the word "text"
into the text box, the message box would appear four times, one for each
letter in the word "text". This is clearly not satisfactory.
If I assign the code to BeforeUpdate, I get the following error:

Run-time error '-2147352567 (80020009)'

The macro or function set to the BeforeUpdate or ValidationRule property
Click to expand...
for this field is preventing [Name of database] from saving the data in the
field.
The code I am using is as follows:

Dim Response As Integer
Response = MsgBox("Changing the name of this Organisation will change its
Click to expand...
name wherever it appears." & Chr(13) & Chr(13) & "Do you want to continue?",
vbOKCancel, "Change name of Organisation")
If (Response = vbCancel) Then
[Organisation] = [Organisation].OldValue
End If

where [Organisation] is the name of the text box.

Can anyone explain the meaning of this error, or tell me how to do this?

Many thanks

David Cleave
Click to expand...
 
Back
Top