Notify utilisator that he is modifying a field and give him the choice to No not modify or Yes modif

[Céline Brien]

Hi everybody,
In a form, some new utilisators forget to press the search button before
typing. So they modify the contain of the field without even realizing it.
I would like to notify them with a message box, and give them two options.
Something like this :
----------
Private Sub FamilyName_AfterUpdate()
If MsgBox "Do you really want to modify the FamilyName ?", vbYesNo +
vbExclamation + vbDefaultButton2, "Modification of the family name" = VbNo
Then
Cancel edition of family name, not cancel update of recordset, just
edition of family name
Else
Just keep on the work without update of recordset
End Sub

 

[Douglas J. Steele]

AfterUpdate is too late: the change has been saved by that point.

Put the query in the BeforeUpdate event, and set Cancel = True if they don't
want to make the change.

 

[Céline Brien]

Hi everybody,
Hi Douglas,
Thank you for your answer.
The modified codes are bellow : BeforeUpdate and Cancel = True.
It is working better but not exactly as I wish.
When the utilisator types over a name by error and changes the name, he does
not know wich name was there before.
So after answering No to the MsgBox, he is back to the name field and has to
type the previous name but does not know it.
Is it possible, after answering No to the MsgBos that the original name
comes back ???
Many thanks for your help,
Céline
----------
Private Sub FamilyName_BeforeUpdate(Cancel As Integer)
If MsgBox "Do you really want to modify the FamilyName ?", vbYesNo +
vbExclamation + vbDefaultButton2, "Modification of the family name" = VbNo
Then
Cancel =True and bring back original name
Else
End if
End Sub
-----------

AfterUpdate is too late: the change has been saved by that point.

Put the query in the BeforeUpdate event, and set Cancel = True if they
don't want to make the change.

 

[Douglas J. Steele]

Private Sub FamilyName_BeforeUpdate(Cancel As Integer)
If MsgBox "Do you really want to modify the FamilyName ?", _
vbYesNo + vbExclamation + vbDefaultButton2, _
"Modification of the family name") = VbNo Then
Cancel =True
Me!FamilyName.Undo
End if
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi everybody,
Hi Douglas,
Thank you for your answer.
The modified codes are bellow : BeforeUpdate and Cancel = True.
It is working better but not exactly as I wish.
When the utilisator types over a name by error and changes the name, he
does not know wich name was there before.
So after answering No to the MsgBox, he is back to the name field and has
to type the previous name but does not know it.
Is it possible, after answering No to the MsgBos that the original name
comes back ???
Many thanks for your help,
Céline
----------
Private Sub FamilyName_BeforeUpdate(Cancel As Integer)
If MsgBox "Do you really want to modify the FamilyName ?", vbYesNo +
vbExclamation + vbDefaultButton2, "Modification of the family name" = VbNo
Then
Cancel =True and bring back original name
Else
End if
End Sub
-----------

 

[Céline Brien]

Hi Douglas !
It is working perfectly.
Thank you so much !
Have a good day,
Céline

 

[Céline Brien]

Hi everybody,
Hi Douglas,
One last question.
When you create a new record in this form, how would you prevent the MsgBox
to appear ?
Something like :
If Me!FamilyName is Null Then
Go to End if
Many thanks again for your help !
Céline
-----------------
Private Sub FamilyName_BeforeUpdate(Cancel As Integer)
If Me!FamilyName is Null Then
Go to End if
If MsgBox "Do you really want to modify the FamilyName ?", _
vbYesNo + vbExclamation + vbDefaultButton2, _
"Modification of the family name") = VbNo Then
Cancel =True
Me!FamilyName.Undo
End if
End Sub

 

[Douglas J. Steele]

You need to use the IsNull function in VBA:

If IsNull(Me!FamilyName) Then
.....

 

[Céline Brien]

Hi Douglas,
Thank you for your answer.
I could not use
If IsNull(Me!FamilyName) because it is a BeforeUpdate
so I use
If IsNull(Adress) Then Exit Sub
The adress is always fill after the family name.
So, with the addition of these little codes, when you create a new record in
this form, the MsgBox does not appear.
Thank you again !
Have a wonderful day,
Céline