Hi Steve
Well done... you *are* nearly there
The second message comes about because you are trying to save the record by
setting the form's Dirty property to False. However, the BeforeUpdate
procedure is forbidding the save (by setting Cancel=True), so that property
fails to be set. This raises the somewhat obscure error "The setting you
entered isn't valid for this property". The error code for this error
happens to be 2101, so your error handler needs to ignore an error with that
code and not display a message. So, your Click procedure should look like
this:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
If Me.Dirty Then Me.Dirty = False
DoCmd.close
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
If Err <> 2101 Then MsgBox Err.Description <<<<<<<<
Resume Exit_Command25_Click
End Sub
The line I have changed is marked <<<<<<<
Note that this is Command25_Click, not Form_Click. You don't need a
Form_Click procedure at all.
Also, you can change the names of controls such as command buttons to make
your design much easier to follow. I suggest you Change the Name property
of your command button from Command25 to cmdSaveAndClose, because
"Command25" is kind of meaningless.
Then your code can be changed to this:
Private Sub cmdSaveAndClose_Click()
On Error GoTo ProcErr
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
ProcExit:
Exit Sub
ProcErr:
If Err <> 2101 Then MsgBox Err.Description
Resume ProcExit
End Sub
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Steve M said:
Graham, I think I am nearly there. I get the mesage box saying to
complete
the class field , followed by another message box saying 'The setting you
entered isn't valid for this property'.
The code so far..
Option Compare Database
Dim fCancelUnload As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Class]) Then
Cancel = True
MsgBox "Class Field must be completed"
End If
fCancelUnload = Cancel
End Sub
Private Sub Form_Click()
If Err <> 2501 Then MsgBox Err.Description
End Sub
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
If Me.Dirty Then Me.Dirty = False
DoCmd.close
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = fCancelUnload
End Sub
Many thanks for your help so far
steve
Graham Mandeno said:
Hmmmm yes - this is a bug where if a record fails to save as a form is
closing the changes are discarded. Unfortunately the Unload event is too
late to trap it.
What you could do is explicitly save the record before closing the form.
Insert the following line before DoCmd.Close:
If Me.Dirty then Me.Dirty = False
If the save is cancelled (by Form_BeforeUpdate) control will pass to the
error handler and bypass DoCmd.Close. Don't forget to add the condition
to
ignore error 2501 in your error handler, otherwise you will get two
messages.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
OK I think I'm getting there - ish. I have entered the following code
which
now stops the form closing but the user still has to re input all the
data
again as it clears the form. It would be great if they could just go
back
and fill in the madatory field. Sorry to be a pain.
Option Compare Database
Dim fCancelUnload As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Class]) Then
Cancel = True
MsgBox "Class Field must be completed"
End If
fCancelUnload = Cancel
End Sub
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
DoCmd.close
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cancel = fCancelUnload
End Sub
Cheers steve
:
I still seem to be struggling I have entered this in the Before update
event
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim fCancelUnload As Boolean
If IsNull(Me.[Class]) Then
Cancel = False
MsgBox "Class Field must be completed"
End If
fCancelUnload = Cancel
End Sub
On the fCancelUnload it gave me the option of true or false
so i'm sure I have done something wrong.
Steve
:
Also, if the Unload event is cancelled in code, then the DoCmd.Close
will
raise error 2501, so your commandbutton's click event must handle
that
appropriately:
If Err <> 2501 then MsgBox Err.Description
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
The user exits by pressing a cmdbutton
On Error GoTo Err_Command25_Click
DoCmd.close
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click
I need to make sure that the user gets a message telling them to
fill
in
the
field.
Many thanks
Steve
:
Hi Steve
Is the user partially completing the form and then clicking the
[x]
button
to save and close?
If so, then what you need to do is block the form from closing by
cancelling
the Unload event.
At the top of your form module, declare a boolean variable:
Dim fCancelUnload as Boolean
At the end of Form_BeforeUpdate, set this variable depending on
whether
or
not Cancel has been set:
fCancelUnload = Cancel
Now, add an event procedure for the Unload event:
Private Sub Form_Unload(Cancel as integer)
Cancel = fCancelUnload
End Sub
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi
I have used the following code for a frm where a field must be
completed
by
the user. However the user clicks ok when the message box
appears
and
it
closes the frm, they then have to open it again and reinput
full
form
again.
Any way I can get it to just close the message box and allow
them
to
complete
the missing field.
Private Sub Form_BeforeUpdate(Cancel As Integer)
