Validate data then save it

[Tara]

I have a form that uses the BeforeUpdate event to ensure that all fields
contain data before a user can close the form. The user typically clicks a
"Save" button which saves the data then closes the form. Now that the
BeforeUpdate event has been added, if the user forgets to add data to a field
and clicks the "Save" button, they get a custom error message reminding them
to fill in all fields. However they also get a message saying "The
DoMenuItem action was canceled". I understand why this happens (Obviously it
wants to save the data and can't) but I'm not sure how to code it so that if
validation fails, it doesn't even try to save the data.

Any help is appreciated!

 

[Marshall Barton]

I have a form that uses the BeforeUpdate event to ensure that all fields
contain data before a user can close the form. The user typically clicks a
"Save" button which saves the data then closes the form. Now that the
BeforeUpdate event has been added, if the user forgets to add data to a field
and clicks the "Save" button, they get a custom error message reminding them
to fill in all fields. However they also get a message saying "The
DoMenuItem action was canceled". I understand why this happens (Obviously it
wants to save the data and can't) but I'm not sure how to code it so that if
validation fails, it doesn't even try to save the data.

DoMenuItem? What DoMenuItem? You don't need no stinking
DoMenuItem ;-)

Seriously, Replace the save button's DoMenuItem with the
line:
If Me.Dirty Then Me.Dirty = False

 

[John W. Vinson]

I have a form that uses the BeforeUpdate event to ensure that all fields
contain data before a user can close the form. The user typically clicks a
"Save" button which saves the data then closes the form. Now that the
BeforeUpdate event has been added, if the user forgets to add data to a field
and clicks the "Save" button, they get a custom error message reminding them
to fill in all fields. However they also get a message saying "The
DoMenuItem action was canceled". I understand why this happens (Obviously it
wants to save the data and can't) but I'm not sure how to code it so that if
validation fails, it doesn't even try to save the data.

Any help is appreciated!

Please post your code. You may want to trap the error using On Error GoTo code
to suppress the error message, but I've not had to do this usually!

 

[Tara]

Thanks for getting back with me John. Here's the code for the BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If IsNull(Me.FamilyNumber) Then
Cancel = True
strMsg = strMsg & "Family Number required." & vbCrLf
End If

If IsNull(Me.cboFSW) Then
Cancel = True
strMsg = strMsg & "FSW is required." & vbCrLf
End If

If IsNull(Me.MOBFirst) Then
Cancel = True
strMsg = strMsg & "MOB First Name is required." & vbCrLf
End If

If IsNull(Me.MOBLast) Then
Cancel = True
strMsg = strMsg & "MOB Last Name is required." & vbCrLf
End If

If IsNull(Me.FamLvl) Then
Cancel = True
strMsg = strMsg & "Current Family Level is required." & vbCrLf
End If



If Cancel Then
strMsg = strMsg & "Please Correct the entry or click the close
button to exit the form without saving the record"
MsgBox strMsg, vbExclamation, "Invalid data"
End If


End Sub

And here's the code for the Save button event:

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "FrmAddClient", acSaveYes

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

 

[Stefan Hoffmann]

hi Tara,

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "FrmAddClient", acSaveYes

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

The first thing you have to do, is to determin the error code: change
the MsgBox in the error handler:

Err_Command40_Click:
MsgBox Err.Number & " - " & Err.Description


Then use this error number to handle (suppress) the error:


Private Sub Command40_Click()

On Local Error GoTo LocalError

If Me.Dirty Then
Me.Dirty = False
End if
DoCmd.Close acForm, "FrmAddClient", acSaveNo

Exit Sub

LocalError:
If Err.Number <> ERROR_NUMBER Then
MsgBox Err.Description
End If

End Sub


mfG
--> stefan <--

 

[Tara]

Thanks so much for your help Stefan. It worked perfectly!

One more question if you have time though...

When I click the Close button (to close without saving anything), I also get
the message. Is there a way to supress the BeforeUpdate event if I click
that command button?

Thanks again!

 

[Marshall Barton]

When I click the Close button (to close without saving anything), I also get
the message. Is there a way to supress the BeforeUpdate event if I click
that command button?

Before closing the form, add a line to make sure the data is
back in its original state:
Me.Undo

Not that using acSaveYes saves the form's design. This is
someting that no running program should do so be sure to
use acSaveNo