Solved Validate required and close form

lacampeona

lacampeona

Registered User.
Local time
Today, 20:01
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
Click to expand...
 
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.

How do I stop a form from closing from the BeforeUpdate Event? - Post.Byes

I have some code in the forms BeforeUpdate event that checks if a certain checkbox is checked. If it is not, I have a message appear that says "Do you wish to cancel this transaction?" with VbYesNo buttons. I want to be able to have it so that if I click no, the BeforeUpdate event is canceled...
bytes.com

Cancelling a Record Update with BeforeUpdate Event - Post.Byes

Hello all, I am trying to handle a scenario where a user clicks the 'X' close control on a form window border. I want to force the user to use the form's 'save' button to write the updated record. (If the form is not dirty, the 'X' control will just close it silently.) In the BeforeUpdate...
bytes.com
 
Hello
hmmm
ok i will look for all articles
If i will be lost in strings i will came again for help
thank you
 
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.
 
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
 
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
Click to expand...

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
Click to expand...
 
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
 
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
Click to expand...
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
 
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.
 
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
 
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.
 
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.
 
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
 
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
Click to expand...
how I do that?

dim SaveYN as boolean
Click to expand...
you mean in this way?

thanks
 
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
 
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
 
Minty, we are giving a novice different direction. That can only cause confusion.
 
You must log in or register to reply here.

Similar threads

Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
400
Mike Krailo
Mike Krailo
D
Solved Unbound form required using VBA
Replies
9
Views
665
Pat Hartman
P
A
Combining multiple reports into a PDF export
Replies
2
Views
371
KitaYama
K
A
Solved How to use ME in a public function
Replies
13
Views
948
CJ_London
C
D
Solved Change password using tempvar not passing to following form
Replies
4
Views
351
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom