Solved Validate required and close form (1 Viewer)

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hello experts
I manage to write something by myself... :rolleyes:
now I need your opinion if what I make is correct or not?:unsure:

I have some validation funcion in module...
What i Want is that.
1. user opens and close the form...nothing happen..no saving records
2. users open the form and starts to write into the field..then he suddely say ohh i dont want to save it..i will close.. i want the form close aksing him if he want to save..if he say yes..then validation funcion is called and user receive the message that he must enter all field..if he say no..then form close..if he say yes then he must enter all the field..he can exit the form?

i read a lot the forum and all experts say that validation rule must be used in form before update event? can somebody say me how to write that?

now i am using validation rule on button save and on buttton close.
Do you think that is better to delete the button save and only to have the button close and the validation rule on the before update on the form? hmmmmmm

can somebody show me example how he would write the code and where? on save button? on close button? or all in the before update on the form?

thanks to all

Private Sub btnClose_Click()
If ValidateRequired(Me, "aaaa") Then
Me.Undo
DoCmd.Close acForm, Me.Name
Exit Sub
End If
If MsgBox("bbbb." & vbCrLf & _
"cccc?", vbYesNo, "Confirm") = vbYes Then
If Not ValidateRequired(Me, "aaaa") Then Exit Sub
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name
Else
Me.Undo
MsgBox "dddd.", vbInformation, "Confirm"
DoCmd.Close acForm, Me.Name
End If
End Sub
 

bastanu

AWF VIP
Local time
Today, 12:43
Joined
Apr 13, 2010
Messages
1,401
You should stick to the BefoReUpdate as that will run regardless how the user chooses to close the form and it has a Cancel argument that prevents the record to be saved. You should review the many examples on how to do that in this forum and Google in general.
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hello
hmmm
ok i will look for all articles
If i will be lost in strings i will came again for help
thank you
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:43
Joined
Feb 28, 2001
Messages
27,001
Here is a quick overview.

In the form's _BeforeUpdate event, you have the chance to decide to save or not to save. What you do to validate of course depends on YOUR rules for what makes the record valid or not. So we are going to have a hard time telling you too much about that. You can do all sorts of tests in code, as many as you need, but in the end analysis you either WILL or WILL NOT want to save something.

If you want to save what is there because it is complete (or at least acceptable), ... do nothing because Access will automatically save "dirty" forms. It is built to do that, and if you are in the _BeforeUpdate event code, it is about to do exactly that - save the record.

IF you don't want to save, you now have two MORE choices. Do you want to erase it all and not save anything? Or do you just want to continue with what you have and let the user enter more data to the same record?

Remember that MsgBox is actually a FUNCTION that can return information on which button was clicked. Also, note that the event entry point for _BeforeUpdate includes a "Cancel" argument that can be used to prevent the update.

Code:
iAns = MsgBox( "Insufficient data entered, form cannot be saved yet." & vbCRLF & "Hit OK to continue entering data or CANCEL to erase without saving", vbOKCancel, "Insufficient Data")
If iAns = vbCancel then
    Me.Undo
Else
    Cancel = -1
End If

Do NOT confuse the Cancel that is the argument for the _BeforeUpdate event entry point with vbCancel that is one of answers returned by the MsgBox function.

If your user wants to erase it all, the Me.Undo erases the record and makes the form not "dirty" any more, so Access will not save it. It will make the form go through the _Current event again because at that moment, the form matches the current record (because of the Undo).

If your user wants to continue entering data to the current record, the OK return will return a TRUE (= -1) to Access to stop the update from proceeding. So the user could then do whatever else is needed to finish the record and try saving it again.
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hello Doc Man
thank you for your explanation. That is what I need also ..to understand the meaning of every line of code.
I will now try to implement my solution.
If I will be lost in string I will came back to ask more things.
thanks to all of your replies and solutions
Best regards
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hello i am crazy...
i am trying to use my validate funcion on before update and I cant manage...if i say yes or not record is saved.

can someone show me an example how to write the correct syntax on before update using the validate function?
now i am using the button save and button close..and in that way is working..

but how to make it work also on before update?

thank you
Private Sub btnClose_Click()
If ValidateRequired(Me, "aaaa") Then
Me.Undo
DoCmd.Close acForm, Me.Name
Exit Sub
End If
If MsgBox("bbbb." & vbCrLf & _
"cccc?", vbYesNo, "Confirm") = vbYes Then
If Not ValidateRequired(Me, "aaaa") Then Exit Sub
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name
Else
Me.Undo
MsgBox "dddd.", vbInformation, "Confirm"
DoCmd.Close acForm, Me.Name
End If
End Sub

Private Sub btnSave_Click()
'Validate required fields
If Not ValidateRequired(Me, "Manjkajoča polja") Then Exit Sub

'Save the record
If Me.Dirty Then Me.Dirty = False 'Save

MsgBox " Zapis je zabeležen."
DoCmd.Close acForm, Me.Name

End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:43
Joined
Feb 19, 2002
Messages
42,973
Your validation code is NOT in the form's BeforeUpdate event. That is why it doesn't work. Access ALWAYS saves the record when the form closes so whether they pressed your save button or not, the record is getting saved and since the validation code is not where it belongs, it isn't running.

You can force the user to press your save button but you need to put code in the form's BeforeUpdate event to enforce that rule. So all roads lead to code in the form's BeforeUpdate event to do this correctly. The form's BeforeUpdate event is like the flapper on the bottom of a funnel. Itis the last event to run before a record is saved and it ALWAYS runs regardless of what prompted the record save. In it, you validate the data and if it isn't valid, then you set the cancel property to true to prevent Access from saving the record.

Code:
If "some test" is True then
Else
    Cancel = True
    Exit Sub
End If
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hellooo
i am batelling hours with my code. Yesterday i was happy becouse i was thinking that this is what i want but now I have strange thinking on what users can do in my form..now i have another problem

I manage to put correctly the code in the form before update event. I delete my close button and save button. Now I only have the access "X" close button on the form.

i put on form before update this code:
Private Sub Form_BeforeUpdate(cancel As Integer)

If ValidateForm(Me, "required") = False Then
cancel = True
End If
That mean..if users forget to enter some data, validation rule reminds him what he must enter and ask the user if he want to save the record or not.
That is good for now.

But let say that user is crazy he start entering data ..he enter all the data..that mean that my validation rule is happy...validation rule will save the data and close the form..and validation rule will not trigger no message to the user becouse all is correct, but my user in the final decide that he dont want to save the record but all field are already all filled..and he already press X" close button.
now access save the record which is correct but the user is not happy becouse he didnt want to save it in the end?

how can i put message that will ask the user when he enter all the data and on pressing X" close button if he is sure that he want to save it?
i try so many messages...but i only managed that then the message is displaying all the time...

maybe me and the user all both crazy :unsure:

can you show me some example of good practice on the before update displaying message and closing the form without saving it if users say that he dont want to save it?
thanks in advance for you advices
 

Minty

AWF VIP
Local time
Today, 19:43
Joined
Jul 26, 2013
Messages
10,355
You can't anticipate or fix everything for stupidy.

You have a save button, that does exactly what it says on it.
If you add another msg box that says "Are You Sure you want to save" 99 % of your users will get annoyed by it, just for the 1% crazy one.
Add a cancel button instead, that simply undo's (undoes?) the current record.
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hello Minty,

yes I know what you mean and you are right. I am just thinking what user can do...and how to prevent...how to fix it..

Can you tell me what buttons you expert normally use in the form?

Do you make buttons....save ,close, cancel separetly ... do you use special messages for the users what they can do or not....or you just put cancel button and "X" close button on the form?
thanks in advance for your advices of good practice
 

Minty

AWF VIP
Local time
Today, 19:43
Joined
Jul 26, 2013
Messages
10,355
It depends on the requirements.

I have a few forms that have a completion date on them. They can only close the form with this filled out if everything else required on the form is filled out. If not they can close it but have to remove the completion date first. The completion date drives the showing (or not) of the record on a "To be Completed" list. It's very binary, it's completed and disappears off the list or it isn't and sits there waiting to be done, forever! I remove the X to force them to use the Close Command Button.

I have other forms that have a save (with validation if required) and a cancel button. I generally don't ask "are you sure", I expect users to understand, and if not they will soon learn.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:43
Joined
Feb 19, 2002
Messages
42,973
Some people put a prompt at the end of the BeforeUpdate event to ask one more time if the user wants to save. I don't do this. It is annoying more than helpful and irrelevant messages like this one train the users to ignore your messages so they stop reading them and just blow by them.
I didn't say to remove the save button. You can keep it if you want to but if you keep it, you should ensure that the user uses it. To make the form work this way, define a form level Boolean variable. Name it SaveYN. Then set it to True in the Click event of your save button. Then save the record. At the beginning of the form's BeforeUpdate event add an If to check the Boolean. If the value is false, cancel the event, give the user a warning to always use the save button, and exit. Otherwise, set the value to False and continue.

Toggling the button this way forces the user to always use the save button even if he changes the same record a second time.
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Minty,
can you show me some example how you use that in forms? i need to see good practice like you experts do.
thank you
 

lacampeona

Registered User.
Local time
Today, 20:43
Joined
Dec 28, 2015
Messages
392
Hello Pat
ok I will try to implement your idea also... i need to see what is more easier for user...becouse my users are strange

define a form level Boolean variable
how I do that?

dim SaveYN as boolean
you mean in this way?

thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:43
Joined
Feb 19, 2002
Messages
42,973
I can't post entire applications that I build for clients. I gave you step by step instructions. Please at least try to implement them

In the form's class module, above the first procedure header.
Code:
Dim Save YN as Boolean
In the Click event of the save button
Code:
On Error go to ErrProc
    SaveYN = True
    Docmd.RunCommand acCmdSaveRecord
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        case 2501    'save canceled'
            Resume ExitProc
        Case Else
            Msgbox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select

In the form's BeforeUpdate event
Code:
If SaveYN = True
    SaveYN = False
Else
    Cancel = True
    Msbbox "Please use the Save Button if you want to save the record.", vbOKOnly
    Exit Sub
End If
 

Minty

AWF VIP
Local time
Today, 19:43
Joined
Jul 26, 2013
Messages
10,355
It's pretty straightforward.
At the top of the forms code module, I have two boolean variables I use.

Code:
Option Compare Database
Option Explicit

Dim bOkClose    As Boolean
Dim bHistory    As Boolean

The second one is because I use the same form for viewing the historic records. You can probably ignore that.

As Pat then suggests you then can control if you let the form close by checking the result of that boolean.
I then have some validation code in a function - this is pretty specific to my form but should give you an idea;

SQL:
Function ValidateRecs() As Boolean
   
    Dim sControls As String
    Dim ctl As Control
    Dim ctlColour As Long
   
    If bHistory Then
        ValidateRecs = True
        Exit Function
    End If
 
    If IsNull(Me.CompletionDate) Then
        bOkClose = True
        ValidateRecs = True
        For Each ctl In Me.Controls
            If ctl.Tag = "Req" Then     'Reset the colours
                If ctl.BackColor = vbYellow Then
                    ctlColour = GetHexColor("#C6D9F1")
                    ctl.BackColor = ctlColour
                End If
            End If
        Next
        Exit Function
    End If
   
    For Each ctl In Me.Controls
        If ctl.Tag = "Req" Then     'Reset the colours
            If ctl.BackColor = vbYellow Then
                ctlColour = GetHexColor("#C6D9F1") ' light blue
                ctl.BackColor = ctlColour
            End If
            If ctl.Enabled Then
                'Debug.Print ctl.Name, ctl.value, ctl.Properties("DataSheetCaption")
                If IsNull(ctl.value) Then
                    sControls = sControls & ctl.Properties("DataSheetCaption") & vbCrLf
                    ctl.BackColor = vbYellow
                End If
            End If
        End If
    Next ctl
   
    ValidateRecs = True
    bOkClose = True
    If Len(sControls & "") > 0 Then
        Box "The following fields require data before the completion date can be entered;" & vbCrLf & vbCrLf & sControls, vbInformation + vbOKOnly, "Missing Data!", , , 0, , 0, 0
        ValidateRecs = False
        bOkClose = False
    End If
   
End Function

The code uses a TAG value to check and also if the control is enabled as I have code on the load event that enables and disables certain controls, there is no point validating a disabled control's value on this form.

Then on the exit / close save button;

SQL:
Private Sub cmdExit_Click()
   If IsNull(Me.CompletionDate) Then bOkClose = True
    
    If bHistory Then bOkClose = True
    If Not bOkClose Then
        If Not ValidateRecs Then
            Exit Sub
        End If
    End If
                    
    DoCmd.Close acForm, Me.Name
    
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:43
Joined
Feb 19, 2002
Messages
42,973
Minty, we are giving a novice different direction. That can only cause confusion.
 

Users who are viewing this thread

Top Bottom