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

[d4zza]

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...

Private Sub Form_BeforeUpdate(Cancel As Integer)

And the first line of the module is...

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...

[Megalog]

Public Function BeforeUpdateRecord() As Integer

So somewhere in the function you need to set the value of 'BeforeUpdateRe cord' and that's what will be returned when you call the function.

[TheSmileyCoder]

Post the full code of your BeforeUpdate and your BeforeUpdateRec ord

[d4zza]

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

The module looks like this...

Public Function BeforeUpdateRecord()

On Error GoTo Err_BeforeUpdateRecord

Set mCurrentForm = Screen.ActiveForm
    
If mstrRecordChanged = "True" Then
    If MsgBox("Have you updated history?", vbYesNo) = vbNo Then
        Cancel = True
    Else
        mCurrentForm.ChangedByUser = CurrentUser()
        mCurrentForm.ChangedDate = Date
    End If
Else
    mCurrentForm.ChangedByUser = CurrentUser()
    mCurrentForm.ChangedDate = Date
End If

mstrRecordChanged = False      'Reset value of mstrRecordChanged

exit_BeforeUpdateRecord:
    Exit Function
    
Err_BeforeUpdateRecord:
    MsgBox Err.Description
    Resume exit_BeforeUpdateRecord
End Function

But doesn't work. Then if I set the BeforeUpdate event to [Event Procedure], it does work, and the private sub looks like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo err_BeforeUpdate

Set mCurrentForm = Screen.ActiveForm

    If mstrRecordChanged = "True" Then
        If MsgBox("Have you updated history?", vbYesNo) = vbNo Then
            Cancel = True
        Else
            mCurrentForm.ChangedByUser = CurrentUser()
            mCurrentForm.ChangedDate = Date
        End If
    Else
        mCurrentForm.ChangedByUser = CurrentUser()
        mCurrentForm.ChangedDate = Date
    End If

mstrRecordChanged = False      'Reset value of mstrRecordChanged 

exit_BeforeUpdate:
    Exit Sub
    
err_BeforeUpdate:
    MsgBox Err.Description
    Resume exit_BeforeUpdate
End Sub

Thanks...

[Megalog]

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)

Public Function BeforeUpdateRecord() as Integer
  
On Error GoTo Err_BeforeUpdateRecord 
  
Set mCurrentForm = Screen.ActiveForm 
  
If mstrRecordChanged = "True" Then 
    If MsgBox("Have you updated history?", vbYesNo) = vbNo Then 
        BeforeUpdateRecord = True 
    Else 
        mCurrentForm.ChangedByUser = CurrentUser() 
        mCurrentForm.ChangedDate = Date 
    End If 
Else 
    mCurrentForm.ChangedByUser = CurrentUser() 
    mCurrentForm.ChangedDate = Date 
End If 
  
mstrRecordChanged = False      'Reset value of mstrRecordChanged 
  
exit_BeforeUpdateRecord: 
    Exit Function 
  
Err_BeforeUpdateRecord: 
    MsgBox Err.Description 
    Resume exit_BeforeUpdateRecord 
End Function

It appears that the code was pretty similar between both sets here, so reducing this down to one line should work:

Private Sub Form_BeforeUpdate(Cancel As Integer) 
  
On Error GoTo err_BeforeUpdate 
  
Cancel = BeforeUpdateRecord
  
exit_BeforeUpdate: 
    Exit Sub 
  
err_BeforeUpdate: 
    MsgBox Err.Description 
    Resume exit_BeforeUpdate 
End Sub

[TheSmileyCoder]

Setting cancel as a global variable wont have much effect I think. What you should do instead is to have the BeforeUpdateRec ord be a function returning true or false.

Private Sub Form_BeforeUpdate(Cancel As Integer) 
  Cancel=BeforeUpdateRecord(Me) 
End Sub

Where your BeforeUpdateRec ord could look like this:

Public Function BeforeUpdateRecord(myForm as Form) 
BeforeUpdateRecord=False
  If myForm.Dirty Then
    If MsgBox("Have you updated history?", vbYesNo) = vbNo Then 
        BeforeUpdateRecord= True 
    Else 
        myForm.ChangedByUser = CurrentUser() 
        myForm.ChangedDate = Date 
    End If 
  Else 
    myForm.ChangedByUser = CurrentUser() 
    myForm.ChangedDate = Date 
  End If 
End Sub

This doesn't take your mstrRecordChang ed into account, but uses the forms Dirty property instead. If you need to use the mstrRecordChang ed you can just pass that as a variable as well.

Hope this is what you were looking for.

[d4zza]

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 mstrRecordChang ed 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 mstRecordChange d=True"

[d4zza]

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...