How to avoid Form Close if Before Updating event is true

[lmarconi]

Good afternoon everyone. My problem is the following:

I have an input bounded form which i use to create new projects. In this form there are some mandatory fields which need to be filled if the user want to save the record (managed by Before Updating event). In the same form i have a button to close and save data (i know it is not necessary but user want to have it to close the form).

At the moment i press the Close Button (the code in the button is simple docmd.close) the system, trough the Before Updating event:
- ask if i want to save the record;
- if answer is NO, close the form without save;
- if the answer is Yes, it control if all mandatory fields are filled and if some mandatory fields are not filled, it gives a message with the field name to be filled.

At this point, my problem: since I'm in the "Close Button" event, it close ANYWAY the form while i would like it remain open to fill the missing information.
Below is my Before update code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to save the changes for this record?", _

vbYesNo + vbQuestion, "Save Changes?") = vbNo Then

Cancel = True

Me.Undo

Else

vMsg = ""

Select Case True

Case IsNull(Me.RAMZ_ID)

vMsg = "RAMZ ID is missing"

Case IsNull(Me.Project_Name)

vMsg = "Project Name is missing"

Case IsNull(Me.Department)

vMsg = "Department is missing"

..............

End Select

If vMsg <> "" Then

Cancel = True

MsgBox vMsg, vbCritical, kREQD

End If

End If
End Sub

I tried to check in the group, but it seams i didn't find what i need. Probably it is something simple to be added at the close event but i cannot understand how to manage.
Thanks for any suggestion.


Cheers.

[ranman256]

stop the close in the form UNLOAD event.
the 'IsValidForm' would be your checks to make sure your data is correct

Private Sub Form_Unload(Cancel As Integer)
   if Not IsValidForm() then cancel = true

End Sub

[orange]

Why do you give the user the option to save changes before the mandatory fields are populated?

In the same form i have a button to close and save data (i know it is not necessary but user want to have it to close the form).

[Bob Fitz]

Good afternoon everyone. My problem is the following:

I have an input bounded form which i use to create new projects. In this form there are some mandatory fields which need to be filled if the user want to save the record (managed by Before Updating event). In the same form i have a button to close and save data (i know it is not necessary but user want to have it to close the form).

At the moment i press the Close Button (the code in the button is simple docmd.close) the system, trough the Before Updating event:
- ask if i want to save the record;
- if answer is NO, close the form without save;
- if the answer is Yes, it control if all mandatory fields are filled and if some mandatory fields are not filled, it gives a message with the field name to be filled.

At this point, my problem: since I'm in the "Close Button" event, it close ANYWAY the form while i would like it remain open to fill the missing information.
Below is my Before update code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to save the changes for this record?", _

vbYesNo + vbQuestion, "Save Changes?") = vbNo Then

Cancel = True

Me.Undo

Else

vMsg = ""

Select Case True

Case IsNull(Me.RAMZ_ID)

vMsg = "RAMZ ID is missing"

Case IsNull(Me.Project_Name)

vMsg = "Project Name is missing"

Case IsNull(Me.Department)

vMsg = "Department is missing"

..............

End Select

If vMsg <> "" Then

Cancel = True

MsgBox vMsg, vbCritical, kREQD

End If

End If
End Sub

I tried to check in the group, but it seams i didn't find what i need. Probably it is something simple to be added at the close event but i cannot understand how to manage.
Thanks for any suggestion.
Cheers.

You could test for simple Null values using table validation without the need for any code in the form.

[lmarconi]

stop the close in the form UNLOAD event.
the 'IsValidForm' would be your checks to make sure your data is correct

Private Sub Form_Unload(Cancel As Integer)
   if Not IsValidForm() then cancel = true

End Sub

Good morning, and thanks for suggestion. So what i did:
1. move (and adjust) the beforeupdate code to the function "IsValidForm"
2. add the following to the UnLoad event:

'------------------------------------
Private Sub Form_Unload(Cancel As Integer)

If Not IsValidForm() Then

Cancel = True

End If

End Sub

But there is still something wrong. When i try to close the form (with the close button) system ask if i want to save data (as per IsValidForm) and:
1. If i answer YES, he showed me which field is missing, but anyway it close and save the record if the Key is present;
2. If i answer NO, I got RunTime Error 2051 - at the close button event (the close action was cancelled)

A part this and if everything will work, i suppose i need to delete the record if i decide to stop the input after the Key is inserted (otherwise record will be anyway saved).

Where i'm wrong ?

Thank you,
Cheer

[lmarconi]

You could test for simple Null values using table validation without the need for any code in the form.

Good morning and thanks.
Could you please explain better in detail? Shall i need to continue to use BeforeUpdate for validation? or how to do ?
Cheers.

[lmarconi]

Why do you give the user the option to save changes before the mandatory fields are populated?

Good morning and thanks for answer.
I don't give the possibility to save record if all mandatory fields are not populated, but i need to give the possibility to close without save (i though trough BeforeUpdate + Close Button).
Cheers.

[Bob Fitz]

Good morning and thanks.
Could you please explain better in detail? Shall i need to continue to use BeforeUpdate for validation? or how to do ?
Cheers.

Open, in design view, the table that has the fields which are to be made mandatory.
click on the appropriate field. It's field properties are listed below.
In the "Validation Rule" property type Is Not Null
In the Validation Text" property type the wording that you would like to appear when the rule is broken. Perhaps something like "Field [The Field Name] must be entered."

The attached db illustrates this approach. One table (tblTest), with two fields ("txt" and "num"). The "txt" field has the Validation applied to it.
Form frmTest is bound to table tblTest but the only code used is for the button which closes the form.
Try to enter/edit a record wher field "txt" is null.

Post back if you have questions.

[lmarconi]

Thanks a lot for clarification and example. Yes, i red about this solution which work fine but what I didn't like is that the system given an automatic "standard" message which refer to the <table.field> which is not nice (and understandable) for the user.
From what you explain, I supposed the message in the "Validation Text" should appear, but it seems is not like that. Following what i get from your example, regardless you add a specific text in the Validation Text:



And another question: i need to give the possibility to close without save the record. How to manage in this case?

Cheers

[Bob Fitz]

From what you explain, I supposed the message in the "Validation Text" should appear, but it seems is not like that. Following what i get from your example, regardless you add a specific text in the Validation Text:

Yes, I thought it worked like that but if not then use the same properties of the text box on the form. That definitely works. I've just tried it

[Bob Fitz]

And another question: i need to give the possibility to close without save the record. How to manage in this case?

Sorry, I missed this question.
You could try the following code in the forms Before Update event:

If Me.Dirty Then    
     If MsgBox("Save this record?", vbYesNo) = vbNo Then
        Me.Undo
    End If
End If

[Micron]

If i answer NO, I got RunTime Error 2051 - at the close button event (the close action was cancelled)

Just some info to explain this as it's a common issue that you might see again in the future.

Let's say you have a procedure (e.g. button click event) that contains a line to close a form. When that line is executed, program control passes to the unload event for that form. When the load event terminates control passes back to the event that contained the command to close the form. Here it is detected that a command was not completed, thus the error message. It could be said that this is unnecessary because it was the desired outcome, but it would seem that the wisest course of action would be to alert the user lest code unexpectedly caused the event to cancel. So a common solution is to trap error 2501 and ignore it. For more information, perhaps research error handling and events for Access objects. Here's a link for events in Access...
https://support.microsoft.com/en-us/...7-ce86553682f9

[lmarconi]

Thanks to all, i really appreciate the effort to give suggestion in this group. The problem is me and now i'm confused.
Considering the use of the field (or form) validation not work properly to me, or maybe it not work with my procedure (thanks Bob to share example DB), i would like to understand the use of the Unload Event and how to adjust the code of the Close Form button.
I have the BeforeUpdate event section as per my code at the first post which will validate the data and, so far, in the close button event i have only "DOCMD.CLOSE".

Can someone explain with example how to:
- Add Unload Event of the Form
- Adjust the close button in order to allow the close only if all mandatory field (as per beforeupdate event) are filled ?

What i would like to have is:
1. If i press the close button without any filled data, form is close;
2. If i press the close button but at least one field is filled, the beforeupdate event will check if the mandatory fields are filled. If one or more mandatory fields are missing, procedure need to ask if i want to close (if yes data will be deleted because not all mandatory fields are filled, if not form will remain open);
3. If i press the close button and all mandatory fields are filled, procedure will ask to confirm i want to save the record (if yes, form is close and data are saved, if no,t form is closed and data are deleted).

Thanks again for tremendous support and help you give and sorry if it is not clear to me but before to write again i tried to adjust using all what you suggested but i was not able to solve it.
Cheers.

[lmarconi]

Good morning all. For your information and to close this tread, i decided to move forward in different way, i don't know if it is the best but cover all my requirements. What i did:
1. In the input form i added 2 command button, one to "Exit without save", one to "Save record" (instead of only one);

2. In the click event of the "Exit Without Save" button i add the following code:

If Me.Dirty Then Me.Undo
DoCmd.Close acForm, Me.Name, acSaveNo

3. In the click event of the "Save&Close" button, i add what before was in the "BeforeUpdating" event (which i removed now). If any mandatory field is missed, i got the waring message with the indication of the missing field and the form is not closed.

This method, it seems fully cover all my requirements.

Thanks to all those support me to understand, it remain not clear to me the use of "Unload" event but will try to study and understand.
Cheers.

[ssanfu]

@lmarconi,

Be aware that the "Save" parameter for the command

DoCmd.Close acForm, Me.Name, acSaveNo

has nothing to do with saving or not saving the DATA in the record/controls. The "Save" parameter is for saving changes to the DESIGN of the object that you want to close (in this case a form).

Because you have not made changes to the DESIGN of the form, These three commands are equal:

DoCmd.Close                              <<-- implicitly closes the active/current form
DoCmd.Close acForm, Me.Name              <<-- explicitly closes the named form
DoCmd.Close acForm, Me.Name, acSaveNo    <<-- explicitly closes the named form without saving changes to the design of the form