How can I disable Ctrl+S so user has to use MY save button?

[julietbrown]

I have a Save button I programmed myself to do a load of complicated validation before saving the record. It's ALL done in code, including writing the record out to file via a recordset edit operation. (I think this is probably a mad thing to do anyway! See later.) So, I want to disable Ctrl+S, as that bypasses all my validation. Is there a way to do that?

A very pathetic question now ... I'm sure I should be able to do all my validation in a BeforeUpdate event for the form, so I can use a "Wizard's" Save button anyway, and presumably that would sort out Ctrl+S as well. BUT I can't fathom out how to use the 'Cancel' parameter in the Form_BeforeUpdate event code. It's a ByVal parameter, so it can't be set by me within the event code .... blah, blah, do I understand this even well enough to explain the problem????? How does 'it' know when to abort the save?

[missinglinq]

Your validation code should, indeed, be in the Form_BeforeUpdate event, which would solve all your problems, I suspect. When validation code is placed here, it doesn't matter how the user tries to save the record, the validation will be done.

And you can cancel the update, via code, when a validation fails, by simply using

Cancel = True

If you have more than one field being validated, you need to add this line

Exit Sub

so you end up with


Cancel = True
Exit Sub

Here's a typical multi-control validation:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(Me.Control1) Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 If IsNull(Me.Control2) Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If
End Sub
 

Linq ;0)>

[julietbrown]

Thanks, that's horribly simple! It seems very odd to me that when a sub has a ByVal parameter (what I think of as an 'in-parameter') I can reset it in the code of the sub, otherwise I guess I could have arrived at this myself. But now you've set me right ... I'm on the case. (I hated my Save code, it was a pig and a mess!)

[missinglinq]

The Form_BeforeUpdate sub does not have a ByVal parameter!

If you look at the header you'll see

Private Sub Form_BeforeUpdate(Cancel As Integer)

Access is, over all, a well thought out programing environment, of long standing. When Access does something automatically, like saving a record when the user moves off of the record or presses <Ctrl> + <S> or closes the form, it is seldom a good idea to try to redo this thru a custom button. It generally leads to problems (as you've seen) and it is annoying to experienced Access users, who generally know how Access databases are supposed to operate. And the end users are what we're all about!

Linq ;0)>

[julietbrown]

All true, and I am duly chastised. I can only quote (precisely) the following from the Access 'Help' system, which explicitly states Cancel is 'ByVal', not 'ByRef' ... as below! Is this the beginnings of an excuse for my bad behaviour?!

"BeforeUpdate Event
Occurs before data in a control is changed.
Syntax
Private Sub object_BeforeUpdate( ByVal Cancel As MSForms.ReturnBoolean)"

[TheSmileyCoder]

Read the access help like you read the bible. Its a set of guidelines, its not a set of truths. :)

[julietbrown]

Thanks to your previous help, the whole thing is much cleaned up now, and no more Ctrl+S problems! But I'm a bit puzzled ...

My new Save button for a new record needs to have some code in it to reset the form if the save is successful (but not if it isn't). So, I can't just use the 'bare save' macro the Wizard gives me. However, my code needs to know whether the BeforeUpdate routine cancelled the update. I tried assuming the ('ByRef'!!) parameter Cancel was available at form level, and wrote

If not Cancel then
     'go ahead and do the reset stuff
end if
 

But, Cancel came back false (default, I suppose) regardless of whether the BeforeUpdate sub had set it to true. So, I did the following ...

Private Sub SaveNewButton_Click()
 
    Dim cancelled As Integer
    Call Form_BeforeUpdate(cancelled)
    If Not cancelled Then
        DoCmd.RunCommand acCmdSaveRecord
          'now need to resort the records, and bookmark
          'the one just added ...  
        Dim ContId As Long
        ContId = Me.ContactID
        Me.Requery
        Dim rst As Recordset
        Set rst = Me.RecordsetClone
        rst.FindFirst "ContactID = " & ContId
        Me.Bookmark = rst.Bookmark        
        ResetForm     'a sub call
          'requery the organisation combo box
        Me.Organisation.Requery
   End If
 
End Sub
 

Yes, it 'works', but in fact the BeforeUpdate sub will run TWICE now. I know it does, because there was an advisory warning message in it that didn't cause Cancel/Exit Sub, and it came up twice, as I expected.

Is there any way I can access the value of Cancel set by BeforeUpdate without calling that sub with a parameter I can read on it's return?

[TheSmileyCoder]

I not 100% sure what you want to do, but im gonna guess that AfterUpdate will suit your needs.

That will run after the record has been updated, and only if the record was updated. So if you set cancel to true, the AfterUpdate wont run. You should of course use the Forms AfterUpdate and not one of the controls' afterupdate.

[julietbrown]

Yep! It's amazingly easier when you actually know what you are doing! Thanks. (By the way, from now on I will treat Access Help with even more scepticism ... I already thought it was pretty UNhelpful but hadn't actually expected it to be WRONG!)
Thank you, Smiley!