docmd.close ignores validation rules?

[svdoerga]

Hi guys,

I have a split form that only shows records, no edits, adds, or deletions. On the form I have a button "edit", to edit the selected record. It opens a popup form in dialog mode. The popped up form has a "save" and a "cancel" button. The way I need this to work is that no changes should be saved when the cancel button is pressed. Changes should only be saved when the save button is pressed.

To do this I have put the following code into the before update event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If (blnSaveChanges) Then
    GoTo ExitSub 'do nothing
Else
    Cancel = True 'cancel changes
End If

ExitSub:
End Sub

I set blnSaveChanges to false in the form load event, to true in the click event of the save button, and false in the click event of the cancel button.

This seems to work fine when I click the cancel button, changes are discarded. But when I use the save button it saves without checking the required fields and validation rules.

Here is the save button event:

Private Sub cmd_Save_Click()

blnSaveChanges = True
DoCmd.Close acForm, "Receivement_Add", acSaveYes

End Sub

Seems like closing the form like this, causes the validation rules to be ignored. Can anyone tell me how I should do this?

 

[lameid]

First, I notice that you are saving the design of your form with docmd.close. This means any user form design changes are saved when the button is clicked. I usually don't want to keep user changes in case they go mucking with things, but it may be right for you.


That being said, my hunch is your events etc. aren't firing right because you are closing before saving the record...

Try....

Private Sub cmd_Save_Click()

blnSaveChanges = True
docmd.RunCommand acCmdSaveRecord 'Or some other command 
                         'to save the record

DoCmd.Close acForm, "Receivement_Add", acSaveYes 'Saves form design changes

End Sub

 

[lameid]

One more thought you can simplify your other event...

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = Not(blnSaveChanges) 'cancel changes when flag not set

End Sub

 

[svdoerga]

Hi lameid,

Thanks for replying. As you can see I am a newbie to programming so your tip on simplifying the beforeupdate procedure was very welcome

The form design shouldn't be saved, that was not intended, thanks for pointing that out.

I have tried your suggestion, changed the code to:

Private Sub cmd_Save_Click()

blnSaveChanges = True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "Receivement_Edit", acSaveNo

End Sub

(I have a button to add records too, the code in my previous post was from that form "Receivement_Add", but of course it's a minor thing since they are the same.)

But that didn't change anything, the validation rules are still not applied. I then set a breakpoint on the line "DoCmd.RunCommand acCmdSaveRecord", when I step into that line I get the error: "the command 'save record' isnt available now". I don't get the error in runtime without the breakpoint.

So I googled for more information and tried:

Private Sub cmd_Save_Click()

blnSaveChanges = True

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

DoCmd.Close acForm, "Receivement_Edit", acSaveNo

End Sub

But that changes nothing either. The save button is on the same form as the records.

Any other suggestions?

 

[lameid]

Your form is bound, correct? Bound meeting that there is a table or query supplying the data under the form....

If it is a query, is it updateable? Meaning if you open the query directly, can you edit the record and commit the changes by clicking the record selector or moving off the record?

My only other thought is timing... I usually don't or maybe haven't tried to cancel an update when saving the record... Put a break point in your before update event and see if it is firing after the button click or sometime before...

And now for the basic question... Why do you only want to save the data on the button click?

 

[svdoerga]

I have made a very stupid mistake here. My opening post and thread topic say that validation rules are ignored, but that is not true. The only problem is that required fields are not checked, so when I use my save button I can save the record with a required field empty, which should not happen. Validation rules are applied ok. I wonder if I should start a new topic and get this one closed, because "docmd.close ignores validation rules?" is wrong.

Very sorry for this misunderstanding.

Anyway, everything else we discussed still applies. Only the problem is that required fields are not checked.

In the app I'm making, changes should be made very conciously. No edits and additions can be made on the form where the user gets the datasheet view. Only by pressing the "edit" or "add" button, should the user be able to make changes. The user should have the possibility to undo any changes made while editing by pressing a cancel button, and changes should only be submitted after pressing the save button. In my opinion that is how a user would expect a form like this to work. I hope this clarifies why I want this functionality.

Now to answer your other questions:
The form is bound to a query that can be updated. If I open the query directly, it saves any edits and new records can be added.

The before update event fires after I click save. It fires after "DoCmd.RunCommand acCmdSaveRecord". When I click cancel it only fires when I have made changes. So it only fires after button clicks, not before.

 

[lameid]

thread181-1386828


Based on PHV's answer there, I bet if you setfocus on each of the controls in the BeforeUpdate event, you'll trigger validation... It is intersting that is required.

 

[svdoerga]

Hi lameid,

I have tried setting focus on all the controls on the form in the beforeupdate event, but that doesn't seem to fix the problem.
From what I understand, PHV means that I have to check for empty values in the beforeupdate manually. As far as I can imagine, that's probably because using cancel = true means required fields won't be checked automatically.

So now I'm trying to check the field values manually, but I can't figure out how to get to the property that returns the field value. Here's what I do:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
If (blnSaveChanges) Then
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            Select Case ctl.Name
                Case "txt_ReceivementDate"
                If ctl.Value = Null Then
                    MsgBox "Please fill in the 'Receivement date' field", vbExclamation, "Required field empty"
                End If
                Case "txt_ReceivedBy"
                If ctl.Value = Null Then
                    MsgBox "Please fill in the 'Received by' field", vbExclamation, "Required field empty"
                End If
                Case "txt_Customer"
                If ctl.Value = Null Then
                    MsgBox "Please fill in the 'Customer' field", vbExclamation, "Required field empty"
                End If
                Case "txt_Country"
                If ctl.Value = Null Then
                    MsgBox "Please fill in the 'Country' field", vbExclamation, "Required field empty"
                End If
                Case "txt_DeliveredBy"
                If ctl.Value = Null Then
                    MsgBox "Please fill in the 'Delivered by' field", vbExclamation, "Required field empty"
                End If
            End Select
        End If
    Next ctl
Else
    Cancel = True
End If

End Sub

I have made a select with 5 cases that are fields that are required. "ctl.Value" doesn't work here, it doesn't return a value, though the code runs without errors. Do you know which property I should use to get the value of the field here?

 

[lameid]

This is where setting a breakpoint in the code and stepping through it line by line and debugging comes in handy.

My hunch is that the value of the control is not really null...
Also you can combine multiple values in a select statnement as below. You could also use the tag property to identify the validation rule... maybe make a packed field so that the first part is a validation rule and the second part is the 'field name' to display.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
If (blnSaveChanges) Then
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            Select Case ctl.Name
                Case "txt_ReceivementDate", "txt_ReceivedBy", "txt_Customer", "txt_Country", "txt_DeliveredBy"
                If NZ(ctl.Value,””) = “” Then
                    'Fill in the tag property with the name of the field or consider using ctl.properties("ControlSource")
                    MsgBox "Please fill in the '” & ctl.Tag & ”' field", vbExclamation, "Required field empty"
                End If

            End Select
        End If
    Next ctl
Else
    Cancel = True
End If

End Sub

 

[svdoerga]

I finally got it working as I want it (as far as I can tell now haha).
There also was a very sneaky (to me at least) setting that messed up my validation rules...text fields in a table have a property called "allow zero length". This is set to true by default. So on saving, the empty textboxes were validated.

Thanks for all your help lameid!