By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

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

P: 99
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?
Jan 27 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  If IsNull(Me.Control1) Then
  3.    MsgBox "Control1 Must Not Be Left Blank!"
  4.    Cancel = True
  5.    Control1.SetFocus
  6.    Exit Sub
  7.  End If
  8.  If IsNull(Me.Control2) Then
  9.    MsgBox "Control2 Must Not Be Left Blank!"
  10.    Cancel = True
  11.    Control2.SetFocus
  12.    Exit Sub
  13.  End If
  14. End Sub
  15.  

Linq ;0)>

Share this Question
Share on Google+
8 Replies


missinglinq
Expert 2.5K+
P: 3,532
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  If IsNull(Me.Control1) Then
  3.    MsgBox "Control1 Must Not Be Left Blank!"
  4.    Cancel = True
  5.    Control1.SetFocus
  6.    Exit Sub
  7.  End If
  8.  If IsNull(Me.Control2) Then
  9.    MsgBox "Control2 Must Not Be Left Blank!"
  10.    Cancel = True
  11.    Control2.SetFocus
  12.    Exit Sub
  13.  End If
  14. End Sub
  15.  

Linq ;0)>
Jan 27 '10 #2

P: 99
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!)
Jan 27 '10 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jan 27 '10 #4

P: 99
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)"
Jan 28 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Read the access help like you read the bible. Its a set of guidelines, its not a set of truths. :)
Jan 28 '10 #6

P: 99
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

Expand|Select|Wrap|Line Numbers
  1. If not Cancel then
  2.      'go ahead and do the reset stuff
  3. end if
  4.  
But, Cancel came back false (default, I suppose) regardless of whether the BeforeUpdate sub had set it to true. So, I did the following ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveNewButton_Click()
  2.  
  3.     Dim cancelled As Integer
  4.     Call Form_BeforeUpdate(cancelled)
  5.     If Not cancelled Then
  6.         DoCmd.RunCommand acCmdSaveRecord
  7.           'now need to resort the records, and bookmark
  8.           'the one just added ...  
  9.         Dim ContId As Long
  10.         ContId = Me.ContactID
  11.         Me.Requery
  12.         Dim rst As Recordset
  13.         Set rst = Me.RecordsetClone
  14.         rst.FindFirst "ContactID = " & ContId
  15.         Me.Bookmark = rst.Bookmark        
  16.         ResetForm     'a sub call
  17.           'requery the organisation combo box
  18.         Me.Organisation.Requery
  19.    End If
  20.  
  21. End Sub
  22.  
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?
Jan 28 '10 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 28 '10 #8

P: 99
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!
Jan 28 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.