Before Update Usage

[Fornatian]

I now know that you use the before update event to perform last minute checks on data validity before committing the changes but my question is this:

If I have a form with a close button on it using Docmd.Close, is there an easy way to cancel the close buttons execution if a rule in the before update event is broken to return the focus to a control on the form. I am currently using a boolean variable as a flag but would like to know if this is the right way to use the before update event to control closing of a form?

Thanks in advance for any replies.

 

[Robert Dunstan]

Hi,

This is just a thought and my not be entirely relevant. Why not create a sub that will validate your data on your form. Within this sub if the validation rule etc is broken then you could set your boolean variable to False.

In your form's BeforeUpdate event and your cmdButton's On_Click event you could call this sub and the value of your boolean variable would be passed back to the calling sub for further processing i.e. msgbox and Cancel = True etc.

HTH
Rob

 

[Fornatian]

Thanks for the reply Robert - to be honest that is exactly what I am doing at the moment except that the validating rule is inside the BeforeUpdate sub and is called from the close button as follows:

Dim blnPassCheck As Boolean
Private Sub Command22_Click()'close button
blnPassCheck = False
Form_BeforeUpdate False
If blnPassCheck = False Then
CloseActForm
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'validation stuff
If Me.MedHighUpperBound > Me.HighUpperBound Or _
Me.MediumUpperBound > Me.MedHighUpperBound Or _
Me.MedLowUpperBound > Me.MediumUpperBound Or _
Me.LowRUpperBound > Me.MedLowUpperBound Then
DisplayMessage "The risk bands must increment up to the highest level" & Chr(13) & _
"e.g.20,40,60,80,100. One or more of the figures entered does not conform to this rule" & Chr(13) & _
"Please check the figures and try again"
Cancel = True
Me.LowRUpperBound.SetFocus
blnPassCheck = True
End If

Maybe you have confirmed that I am doing the right thing.

I just thought there might be an easier way to prevent the close action seeing as this is probably when the forms beforeupdate event is called anyway.

Any other ideas?

Ian

 

[Robert Dunstan]

Ian

The way I suggested is what I normally do. I like to shove all the code that handles the checking and validating of a form's data into a sub then that way you can call it from the form's BeforeUpdat event, OnClose event etc or from a command button.

The other way would be to code in the BeforeUpdate event and handle all your error traps and validation here as you have done.

Rob

 

[Fornatian]

Thanks again. I can see the advantage of your putting the code in behind the other event procedures but this is a form that by design doesn't allow moving between records and therefore doesn't trigger the beforeupdate event except when the form closes. You have highlighted the fact that I need to take the close X out of the top corner so the users have to click my button.

Cheers again.

Ian

 

[The_Doc_Man]

I used something similar to this on a form that had "CANCEL" and "COMMIT" buttons based on whether any field had been updated.

When you changed a field value, the _Change() event code set a common flag in the declaration section of the form's class module. Then when you tried to leave the form, close it, or do anything else, it tested that flag.

If the flag was still set then it told you to either commit or cancel the update. It returned a value to the (Cancel as integer) parameter based on the state of that flag and how you answered the question in the dialogue box. Worked just fine for my system.

You can return True as an integer, by the way. You can set Cancel = True if you want to because VBA just converts it invisibly. At least, it did for me.