data validation on Form_BeforeUpdate

[Associates]

Hi,

In the form, before closing the form for inserting new record into a table,
Form_BeforeUpdate is called. I want to do some data validation to make sure
some important data are entered. So, i have if-else statement in the
Form_BeforeUpdate as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim mesg, Title As String
Title = "Warning !!!"

If Me.Subject_Code <> "" Then
mesg = "Data has been changed in this record. Do you want to proceed?"

If MsgBox(mesg, vbYesNo, Title) = vbNo Then
Cancel = True
Me.Undo
End If
Else
MsgBox "Subject Code can not be empty", vbOKOnly
Cancel = True
...
End If
End Sub

My question is that instead of using "me.undo" to undo or reset the form, is
there any way of keeping the form and all the other data intact (not reset)
while giving the user to enter for the missing data for Access 03?

Thank you in advance

 

[Arvin Meyer [MVP]]

Instead of Me.Undo try:

Me.[Subject_Code] = Me.[Subject_Code]
Me.[Subject_Code].SetFocus

 

[Associates]

Hi Arvin,

No, it doesn't work. The form gets closed as soon as it executes
Form_BeforeUpdate when Subject_Code is empty.

If Me.Subject_Code <> "" Then
mesg = "Data has been changed in this record. Do you want to proceed?"

If MsgBox(mesg, vbYesNo, Title) = vbNo Then
Cancel = True 'if user respond is No, then don't do updating
Me.Undo 'then undo all the data and reset the
beforeupdate event
End If
Else
MsgBox "Subject Code can not be empty", vbOKOnly
Cancel = True
'Me.Undo
Me.Subject_Code = Me.Subject_Code
Me.Subject_Code.SetFocus
End If

how can i keep the form open and all the data that have already been filled
out while insisting user on entering the missing Subject_Code (rather than
shutting the form immediately?

Thank you in advance

Instead of Me.Undo try:

Me.[Subject_Code] = Me.[Subject_Code]
Me.[Subject_Code].SetFocus
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

In the form, before closing the form for inserting new record into a
table,
Form_BeforeUpdate is called. I want to do some data validation to make
sure
some important data are entered. So, i have if-else statement in the
Form_BeforeUpdate as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim mesg, Title As String
Title = "Warning !!!"

If Me.Subject_Code <> "" Then
mesg = "Data has been changed in this record. Do you want to
proceed?"

If MsgBox(mesg, vbYesNo, Title) = vbNo Then
Cancel = True
Me.Undo
End If
Else
MsgBox "Subject Code can not be empty", vbOKOnly
Cancel = True
...
End If
End Sub

My question is that instead of using "me.undo" to undo or reset the form,
is
there any way of keeping the form and all the other data intact (not
reset)
while giving the user to enter for the missing data for Access 03?

Thank you in advance

 

[Associates]

Hi Arvin,

It works now. In the form, I have a close button which closes the form. I
put your code into it and it works because it hasn't invoked
Form_BeforeUpdate yet. This way, the form won't get shut.

Thank you for your help.

Instead of Me.Undo try:

Me.[Subject_Code] = Me.[Subject_Code]
Me.[Subject_Code].SetFocus
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

In the form, before closing the form for inserting new record into a
table,
Form_BeforeUpdate is called. I want to do some data validation to make
sure
some important data are entered. So, i have if-else statement in the
Form_BeforeUpdate as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim mesg, Title As String
Title = "Warning !!!"

If Me.Subject_Code <> "" Then
mesg = "Data has been changed in this record. Do you want to
proceed?"

If MsgBox(mesg, vbYesNo, Title) = vbNo Then
Cancel = True
Me.Undo
End If
Else
MsgBox "Subject Code can not be empty", vbOKOnly
Cancel = True
...
End If
End Sub

My question is that instead of using "me.undo" to undo or reset the form,
is
there any way of keeping the form and all the other data intact (not
reset)
while giving the user to enter for the missing data for Access 03?

Thank you in advance