How to prevent saving bad data to the table

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

Guest

I have a form which bound to a table, I added validation codes in the
Form_BeforeUpdate. However, sometimes Form_BeforeUpdate is not fired, then
the bad data got saved to the table. How do I handle this situation?
 
If you cancel the BeforeUpate event of the form, the data cannot get into
the table.

Was it there before you added the validation? Or did it get there by typing
directly into the table?

How complex is the validation? Could you use the Validation Rule for the
table? (You will find that in the Properties box when the table is open in
design view.)
 
Hello Allen,

There are 4 buttons and other text boxes on that form. The buttons on the
form opens another form.

If I fill all text boxes, then clcik the first two buttons -- "Price" or
"Finacial", then exit from, the Form_BeforeUpdate fires.

However, if I fill all text boxes, then click another two buttons -- "User
screen" or "Requester", then exit, the Form_BeforeUpadate is not fired. Only
this two button, I don't know why.

Here is the validation section in my Form_BeforeUpdate

If Not IsNull(Me.CustomerID) Then
If Len(Me.CustomerID) > 0 Then
If MsgBox("Data has been changed. Do you wish to save these
changes?", vbYesNo, "Adding a customer") = 7 Then
Me.Undo
Else
'make sure user entered required data
If IsNull(Me.CompanyName) Or Len(Me.CompanyName) = 0 Then
MsgBox "cannot save this record without Customer Name."
Me.Undo
Exit Sub
Else
'user choose save option
StandardFormBeforeUpdateSave([Form], "TypeGroup")
End If
End If
End If
Else
Me.Undo
End If
 
Try adding this to the top of the buttons that give you the problem:
If Me.Dirty Then Me.Dirty = False

That line forces Access to save the record if it needs it. During that
process Form_BeforeUpdate will fire. If the record cannot be saved, the
attempt to set the form's Dirty property to False will fail, so you will
need error handling in the code of those 2 buttons to catch that, but of
course that's exactly what you need to know: that the save failed, and so
cannot just merrily proceed on with other things that depend on this record.

There is a serious bug in Access where you don't get the messages you should
if you use the Close action/method on the form. I'm not sure if that's
related to what you are describing but you can read about that bug in this
article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

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

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

Ming said:
Hello Allen,

There are 4 buttons and other text boxes on that form. The buttons on the
form opens another form.

If I fill all text boxes, then clcik the first two buttons -- "Price" or
"Finacial", then exit from, the Form_BeforeUpdate fires.

However, if I fill all text boxes, then click another two buttons -- "User
screen" or "Requester", then exit, the Form_BeforeUpadate is not fired.
Only
this two button, I don't know why.

Here is the validation section in my Form_BeforeUpdate

If Not IsNull(Me.CustomerID) Then
If Len(Me.CustomerID) > 0 Then
If MsgBox("Data has been changed. Do you wish to save these
changes?", vbYesNo, "Adding a customer") = 7 Then
Me.Undo
Else
'make sure user entered required data
If IsNull(Me.CompanyName) Or Len(Me.CompanyName) = 0 Then
MsgBox "cannot save this record without Customer Name."
Me.Undo
Exit Sub
Else
'user choose save option
StandardFormBeforeUpdateSave([Form], "TypeGroup")
End If
End If
End If
Else
Me.Undo
End If

--
Software Developer


Allen Browne said:
If you cancel the BeforeUpate event of the form, the data cannot get into
the table.

Was it there before you added the validation? Or did it get there by
typing
directly into the table?

How complex is the validation? Could you use the Validation Rule for the
table? (You will find that in the Properties box when the table is open
in
design view.)
Click to expand...
Click to expand...
 
Hello Allen,

The history of this problem is the bad data got saved to the table without
any warning when user click "X" button on the top of window. The codes of
validation and msg box, which is used to ask user to save data, were in
"Exit" button click event. So if user clicks "Exit" button, the validation
will be executed. If user clicks the "X" button to exit, the bad data got
saved to the table without warning. I posted the issue at newsgroup. I was
suggested to move the validation codes to the Form_BeforeUpdate event so the
validation codes will be always executed when user exits from the customer
screen.

Last month, I noticed that if I enter some data and click the "Price" or
"Financial" button, then click "Exit" or "X" button, the form_BeforeUpdate
event is then always fired. However, if I enter data and click the "User
Screen" or "Requester" button, then click "Exit" or "X" button, the
Form_BeforeUpdate event is not fired, the bad data got saved into the table
without any warning.

We have the similar codes behind 4 buttons as below:

DontPromptUser = True
DoCmd.DoMenuItem A_FILE, acFormBar, A_SAVERECORD
DontPromptUser = False
' CheckUserTables
DoCmd.OpenForm "fdgUserScreens", acNormal, , , , acDialog, "CUST," &
Me!lngCustomerID
DoCmd.Close acForm, "fdgUserScreens"

"DontPromptUser" is a flag, which we use to indicate to show msgbox to ask
save data. Form_BeforeUpdate event check this flag. If it is true, do
nothing. If it is false, it will pop up msg to ask use to save data or not
and do validation.

We want the msg pop up only when user click "Exit" or "X" button.

Any suggestion?
--
Software Developer


Allen Browne said:
Try adding this to the top of the buttons that give you the problem:
If Me.Dirty Then Me.Dirty = False

That line forces Access to save the record if it needs it. During that
process Form_BeforeUpdate will fire. If the record cannot be saved, the
attempt to set the form's Dirty property to False will fail, so you will
need error handling in the code of those 2 buttons to catch that, but of
course that's exactly what you need to know: that the save failed, and so
cannot just merrily proceed on with other things that depend on this record.

There is a serious bug in Access where you don't get the messages you should
if you use the Close action/method on the form. I'm not sure if that's
related to what you are describing but you can read about that bug in this
article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

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

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

Ming said:
Hello Allen,

There are 4 buttons and other text boxes on that form. The buttons on the
form opens another form.

If I fill all text boxes, then clcik the first two buttons -- "Price" or
"Finacial", then exit from, the Form_BeforeUpdate fires.

However, if I fill all text boxes, then click another two buttons -- "User
screen" or "Requester", then exit, the Form_BeforeUpadate is not fired.
Only
this two button, I don't know why.

Here is the validation section in my Form_BeforeUpdate

If Not IsNull(Me.CustomerID) Then
If Len(Me.CustomerID) > 0 Then
If MsgBox("Data has been changed. Do you wish to save these
changes?", vbYesNo, "Adding a customer") = 7 Then
Me.Undo
Else
'make sure user entered required data
If IsNull(Me.CompanyName) Or Len(Me.CompanyName) = 0 Then
MsgBox "cannot save this record without Customer Name."
Me.Undo
Exit Sub
Else
'user choose save option
StandardFormBeforeUpdateSave([Form], "TypeGroup")
End If
End If
End If
Else
Me.Undo
End If

--
Software Developer


Allen Browne said:
If you cancel the BeforeUpate event of the form, the data cannot get into
the table.

Was it there before you added the validation? Or did it get there by
typing
directly into the table?

How complex is the validation? Could you use the Validation Rule for the
table? (You will find that in the Properties box when the table is open
in
design view.)

I have a form which bound to a table, I added validation codes in the
Form_BeforeUpdate. However, sometimes Form_BeforeUpdate is not fired,
then
the bad data got saved to the table. How do I handle this situation?
Click to expand...
Click to expand...
Click to expand...
 
Form_BeforeUpdate is a safe way to ensure record-level validation is
performed, and bad data does not make it into your table.

I'm not sure what else is going on with your other buttons and screens.

If the validation is simple, you could also use the Validation Rule of the
fields in your table, or the Validation Rule of the table itself (in the
Properties box in table design.)

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

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

Ming said:
Hello Allen,

The history of this problem is the bad data got saved to the table without
any warning when user click "X" button on the top of window. The codes of
validation and msg box, which is used to ask user to save data, were in
"Exit" button click event. So if user clicks "Exit" button, the validation
will be executed. If user clicks the "X" button to exit, the bad data got
saved to the table without warning. I posted the issue at newsgroup. I was
suggested to move the validation codes to the Form_BeforeUpdate event so
the
validation codes will be always executed when user exits from the customer
screen.

Last month, I noticed that if I enter some data and click the "Price" or
"Financial" button, then click "Exit" or "X" button, the form_BeforeUpdate
event is then always fired. However, if I enter data and click the "User
Screen" or "Requester" button, then click "Exit" or "X" button, the
Form_BeforeUpdate event is not fired, the bad data got saved into the
table
without any warning.

We have the similar codes behind 4 buttons as below:

DontPromptUser = True
DoCmd.DoMenuItem A_FILE, acFormBar, A_SAVERECORD
DontPromptUser = False
' CheckUserTables
DoCmd.OpenForm "fdgUserScreens", acNormal, , , , acDialog, "CUST,"
&
Me!lngCustomerID
DoCmd.Close acForm, "fdgUserScreens"

"DontPromptUser" is a flag, which we use to indicate to show msgbox to ask
save data. Form_BeforeUpdate event check this flag. If it is true, do
nothing. If it is false, it will pop up msg to ask use to save data or not
and do validation.

We want the msg pop up only when user click "Exit" or "X" button.

Any suggestion?
--
Software Developer


Allen Browne said:
Try adding this to the top of the buttons that give you the problem:
If Me.Dirty Then Me.Dirty = False

That line forces Access to save the record if it needs it. During that
process Form_BeforeUpdate will fire. If the record cannot be saved, the
attempt to set the form's Dirty property to False will fail, so you will
need error handling in the code of those 2 buttons to catch that, but of
course that's exactly what you need to know: that the save failed, and so
cannot just merrily proceed on with other things that depend on this
record.

There is a serious bug in Access where you don't get the messages you
should
if you use the Close action/method on the form. I'm not sure if that's
related to what you are describing but you can read about that bug in
this
article:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

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

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

Ming said:
Hello Allen,

There are 4 buttons and other text boxes on that form. The buttons on
the
form opens another form.

If I fill all text boxes, then clcik the first two buttons -- "Price"
or
"Finacial", then exit from, the Form_BeforeUpdate fires.

However, if I fill all text boxes, then click another two buttons --
"User
screen" or "Requester", then exit, the Form_BeforeUpadate is not fired.
Only
this two button, I don't know why.

Here is the validation section in my Form_BeforeUpdate

If Not IsNull(Me.CustomerID) Then
If Len(Me.CustomerID) > 0 Then
If MsgBox("Data has been changed. Do you wish to save these
changes?", vbYesNo, "Adding a customer") = 7 Then
Me.Undo
Else
'make sure user entered required data
If IsNull(Me.CompanyName) Or Len(Me.CompanyName) = 0 Then
MsgBox "cannot save this record without Customer Name."
Me.Undo
Exit Sub
Else
'user choose save option
StandardFormBeforeUpdateSave([Form], "TypeGroup")
End If
End If
End If
Else
Me.Undo
End If

--
Software Developer


:

If you cancel the BeforeUpate event of the form, the data cannot get
into
the table.

Was it there before you added the validation? Or did it get there by
typing
directly into the table?

How complex is the validation? Could you use the Validation Rule for
the
table? (You will find that in the Properties box when the table is
open
in
design view.)

I have a form which bound to a table, I added validation codes in the
Form_BeforeUpdate. However, sometimes Form_BeforeUpdate is not
fired,
then
the bad data got saved to the table. How do I handle this situation?
Click to expand...
Click to expand...
Click to expand...
 
Allen said:
Form_BeforeUpdate is a safe way to ensure record-level validation is
performed, and bad data does not make it into your table.

If the validation is simple, you could also use the Validation Rule of the
fields in your table, or the Validation Rule of the table itself (in the
Properties box in table design.)
Click to expand...

I broadly agree but with difference emphases:

1) Front end validation is not enough to prevent saving bad data to the
table e.g. consider that users with permissions in the database could
legitimately access the data via Excel. If you can only 'afford' to
have validation in one place, better to go for validation in the
database.

2) Unless the business rule is extremely complex, you should also
create Validation Rules in the database (bearing in mind that one
business rule could map to multiple validation rules).

Before deciding a business rule is too complex for database validation
rules, post it here so we can have a go <g>.

Jamie.

--
 
Back
Top