I have a form and there is a field I would like user to complete without fail. I have put a following code in the before update event:

Me!UpdatedOn = Now
Me!LastUser = fOSUserName

If IsNull(Me.PStatus) Then

MsgBox "You must select select a project status before you can save the record!."

Cancel = True

PStatus.SetFocus
PStatus.Dropdown
Exit Sub
End If


End Sub

I have a command button to close the form. Each time when I test the form by amending the form and leaving out the required field and hitting close button, I do get the message but then form closes down completely. How could I have the form stay opoen and drop down the required field? If I remove the command button to close the form and rely on the X in the corner to close, I get 'You can't save the at this time'. So either way it is not working. Any help will be appreciated.

Hi,

If you want to keep the command button, you can try doing a check there as well. For instance:

If IsNull(Me.PStatus) Then
MsgBox "Please select a project status."
Me.PStatus.SetFocus
Else
DoCmd.Close
End If

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

It has long been recommended that the code

If Me.Dirty Then Me.Dirty = False

be inserted before using

DoCmd.Close

to close a Form because of a quirk in Access. When DoCmd.Close is used, Access closes the Form regardless of whether or not a PK field or other required field has been left blank or validation rule has been violated! If one of these things occur, Access will simply dump the record, close the form, and not tell the user that the record has been dumped!

If Me.Dirty Then Me.Dirty = False forces Access to attempt to Save the Record, and if a violation has occurred, will throw up a warning message and allow correction to be made before closing the Form.

I understand that Microsoft sort of fixed the bug in ACC2007, by adding the line to force the save of the record, when the Wizard is used to create a “close” button. This, in turn, has created another problem (what a surprise!) If you use the Wizard to create a “close” button for an unbound form, which has no Dirty Property, the code bombs out!

Linq ;0)>

This post has been edited by missinglinq: Mar 15 2012, 08:17 PM

In the light of help from you both, if I change my code as under, I still have the same issue. Would you amend the code further or did I get get it right?
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!UpdatedOn = Now
Me!LastUser = fOSUserName

If IsNull(Me.PStatus) Then

MsgBox "You must select select a project status before you can save the record!."

'Cancel = True

PStatus.SetFocus
PStatus.Dropdown

Else

If Me.Dirty Then

Me.Dirty = False

'Exit Sub

DoCmd.Close

End If
End If

End Sub

Hi Arshad,

I meant for you to use the close command button's Click event for the code I posted.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Sorry I did not understand your directions. It works very well. Very grateful for your kind help.

Hi Arshad,

(IMG:style_emoticons/default/yw.gif)

Linq and I are happy to help. Good luck with your project.