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

Can't get 'Cancel' to work properly using a module

P: 23
Using Access 97 SR-2 with Windows XP.

I have form with a BeforeUpdate event which checks a true/false condition of the record being updated. When the user moves to the next record or leaves the form and the condition is true, then a msgbox asks if the user has saved the record. If the user clicks "No", then the form cancels any movement and remains at the same record so the user can then save.

This works absolutely fine when I use a private sub for the BeforeUpdate event. The problem I'm having is that I have several forms that all do the same check, and to avoid replication of code, I've created a module instead, however, the cancel doesn't work when I do it by module. Every other part of the code seems to go through ok, question, etc, but the form still moves onto the next selected record (or close if that is what the user is doing).

The only difference I can see to doing it both ways, and I'm sure this is something that I'm missing due to a lack of proper training on my part, is that the first line of the private sub is...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
And the first line of the module is...

Expand|Select|Wrap|Line Numbers
  1. Public Function BeforeUpdateRecord()
With "Cancel As Integer" being declared as a public variable.

I can post all code if required, but if anyone knows straight away what the difference is, I'd really appreciate it.

thanks...
Mar 11 '10 #1
Share this Question
Share on Google+
7 Replies


Megalog
Expert 100+
P: 378
Expand|Select|Wrap|Line Numbers
  1. Public Function BeforeUpdateRecord() As Integer
So somewhere in the function you need to set the value of 'BeforeUpdateRecord' and that's what will be returned when you call the function.
Mar 11 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Post the full code of your BeforeUpdate and your BeforeUpdateRecord
Mar 11 '10 #3

P: 23
The form's BeforeUpdate event is set to "=BeforeUpdateRecord()" to call the module before updating. "Cancel" is set using a public declaration as Integer.

The module looks like this...

Expand|Select|Wrap|Line Numbers
  1. Public Function BeforeUpdateRecord()
  2.  
  3. On Error GoTo Err_BeforeUpdateRecord
  4.  
  5. Set mCurrentForm = Screen.ActiveForm
  6.  
  7. If mstrRecordChanged = "True" Then
  8.     If MsgBox("Have you updated history?", vbYesNo) = vbNo Then
  9.         Cancel = True
  10.     Else
  11.         mCurrentForm.ChangedByUser = CurrentUser()
  12.         mCurrentForm.ChangedDate = Date
  13.     End If
  14. Else
  15.     mCurrentForm.ChangedByUser = CurrentUser()
  16.     mCurrentForm.ChangedDate = Date
  17. End If
  18.  
  19. mstrRecordChanged = False      'Reset value of mstrRecordChanged
  20.  
  21. exit_BeforeUpdateRecord:
  22.     Exit Function
  23.  
  24. Err_BeforeUpdateRecord:
  25.     MsgBox Err.Description
  26.     Resume exit_BeforeUpdateRecord
  27. End Function
  28.  
But doesn't work. Then if I set the BeforeUpdate event to [Event Procedure], it does work, and the private sub looks like this...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. On Error GoTo err_BeforeUpdate
  4.  
  5. Set mCurrentForm = Screen.ActiveForm
  6.  
  7.     If mstrRecordChanged = "True" Then
  8.         If MsgBox("Have you updated history?", vbYesNo) = vbNo Then
  9.             Cancel = True
  10.         Else
  11.             mCurrentForm.ChangedByUser = CurrentUser()
  12.             mCurrentForm.ChangedDate = Date
  13.         End If
  14.     Else
  15.         mCurrentForm.ChangedByUser = CurrentUser()
  16.         mCurrentForm.ChangedDate = Date
  17.     End If
  18.  
  19. mstrRecordChanged = False      'Reset value of mstrRecordChanged 
  20.  
  21. exit_BeforeUpdate:
  22.     Exit Sub
  23.  
  24. err_BeforeUpdate:
  25.     MsgBox Err.Description
  26.     Resume exit_BeforeUpdate
  27. End Sub
  28.  
Thanks...
Mar 11 '10 #4

Megalog
Expert 100+
P: 378
I wouldnt set 'Cancel' as a public integer, it's possibly a reserved word.. and by default all your forms privately declare Cancel as an integer so you shouldnt be mixing the two.
I would change:
(Line 1 & Line 9)
Expand|Select|Wrap|Line Numbers
  1. Public Function BeforeUpdateRecord() as Integer
  2.  
  3. On Error GoTo Err_BeforeUpdateRecord 
  4.  
  5. Set mCurrentForm = Screen.ActiveForm 
  6.  
  7. If mstrRecordChanged = "True" Then 
  8.     If MsgBox("Have you updated history?", vbYesNo) = vbNo Then 
  9.         BeforeUpdateRecord = True 
  10.     Else 
  11.         mCurrentForm.ChangedByUser = CurrentUser() 
  12.         mCurrentForm.ChangedDate = Date 
  13.     End If 
  14. Else 
  15.     mCurrentForm.ChangedByUser = CurrentUser() 
  16.     mCurrentForm.ChangedDate = Date 
  17. End If 
  18.  
  19. mstrRecordChanged = False      'Reset value of mstrRecordChanged 
  20.  
  21. exit_BeforeUpdateRecord: 
  22.     Exit Function 
  23.  
  24. Err_BeforeUpdateRecord: 
  25.     MsgBox Err.Description 
  26.     Resume exit_BeforeUpdateRecord 
  27. End Function

It appears that the code was pretty similar between both sets here, so reducing this down to one line should work:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer) 
  2.  
  3. On Error GoTo err_BeforeUpdate 
  4.  
  5. Cancel = BeforeUpdateRecord
  6.  
  7. exit_BeforeUpdate: 
  8.     Exit Sub 
  9.  
  10. err_BeforeUpdate: 
  11.     MsgBox Err.Description 
  12.     Resume exit_BeforeUpdate 
  13. End Sub 
Mar 11 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Setting cancel as a global variable wont have much effect I think. What you should do instead is to have the BeforeUpdateRecord be a function returning true or false.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer) 
  2.   Cancel=BeforeUpdateRecord(Me) 
  3. End Sub
Where your BeforeUpdateRecord could look like this:
Expand|Select|Wrap|Line Numbers
  1. Public Function BeforeUpdateRecord(myForm as Form) 
  2. BeforeUpdateRecord=False
  3.   If myForm.Dirty Then
  4.     If MsgBox("Have you updated history?", vbYesNo) = vbNo Then 
  5.         BeforeUpdateRecord= True 
  6.     Else 
  7.         myForm.ChangedByUser = CurrentUser() 
  8.         myForm.ChangedDate = Date 
  9.     End If 
  10.   Else 
  11.     myForm.ChangedByUser = CurrentUser() 
  12.     myForm.ChangedDate = Date 
  13.   End If 
  14. End Sub
This doesn't take your mstrRecordChanged into account, but uses the forms Dirty property instead. If you need to use the mstrRecordChanged you can just pass that as a variable as well.

Hope this is what you were looking for.
Mar 11 '10 #6

P: 23
I'll give it a try, but I'm not sure that'll work using the Dirty property as it's not exactly a Save Record that I'm prompting the user for. The variable mstrRecordChanged is set to true only when certain text boxes record a change (using the individual text boxes OnChange event). If the user changes any these text boxes, then they have two command buttons offering options of writing these changes to a historical sub-form and/or printing them off. It's only when these certain text boxes are changed and the variable is set to true, that users are then prompted with the reminder question. If the user has forgotten to record the changes in the historical sub-from and clicks "No", then the form should cancel any movement.

I hope this clears up what I'm trying to do. Basically prompt the user after certain changes, but only have 1 module to handle it instead of every form having a private sub, and I offer sincere apologies for mis-representing what exactly I need to do, sorry.

I'll try the code, but edit by changing "If Dirty" to "If mstRecordChanged=True"
Mar 11 '10 #7

P: 23
I gave the code a little tweak to satisfy my needs, and it works!

I'd have preferred the BeforeUpdate event to just go straight to a module, but if it's only 1 line of code needed for each form, then that's better than the whole module being repeated for every form.

Help, as always, is much appreciated.

Thanks...
Mar 11 '10 #8

Post your reply

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