Keeping it tidy

[GS1]

I've got some code as follows

****************************************************

Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Lead_Partner) And IsNull(Me.Details_Descriptions) Then
MsgBox "Please enter the Lead Partner's name and some information in the Details and Descriptions field"
Cancel = True 'stops the save command from running
Me.Lead_Partner.SetFocus

ElseIf IsNull(Me.Lead_Partner) And Not IsNull(Me.Details_Descriptions) Then
MsgBox "Please enter a Lead Partner's Name"
Cancel = True
Me.Lead_Partner.SetFocus

ElseIf IsNull(Me.Details_Descriptions) And Not IsNull(Me.Lead_Partner) Then
MsgBox "Please enter some Details/Descriptions about the Activity"
Cancel = True
Me.Details_Descriptions.SetFocus

Else: DoCmd.RunMacro ("Last Modified")
End If

End Sub
****************************************************

which needs to be applied to every form on my database.

Is it better to put this code in a module and then call it from the before update event of each form? If so, i would be grateful if someone could tell me what modifications (if any) need to be made to the code, and what exactly I need to write to call it - I have been experimenting all morning and cannot crack it!

Thanks

G

 

[Travis]

Add this to a Module and call it from each form:

Form

Sub Form_BeforeUpdate(Cancel As Integer) 
     Cancel=FormBeforeUpdate(Me)
End Sub

Module

Public Function FormBeforeUpdate(ByRef Frm as Form) As Boolean
Dim fValue as Boolean
   fValue=False
   If IsNull(frm.Lead_Partner) And IsNull(frm.Details_Descriptions) Then 
     MsgBox "Please enter the Lead Partner's name and some information in the Details and Descriptions field" 
     fValue = True 'stops the save command from running 
     frm.Lead_Partner.SetFocus 

ElseIf IsNull(frm.Lead_Partner) And Not IsNull(frm.Details_Descriptions) Then 
     MsgBox "Please enter a Lead Partner's Name" 
fValue = True 
     frm.Lead_Partner.SetFocus 

ElseIf IsNull(frm.Details_Descriptions) And Not IsNull(frm.Lead_Partner) Then 
     MsgBox "Please enter some Details/Descriptions about the Activity" 
     fValue = True 
     frm.Details_Descriptions.SetFocus 

Else: DoCmd.RunMacro ("Last Modified") 
End If 
     FormBeforeUpdate=fValue
End Function

 

[GS1]

Thank you very much indeed!

It works perfectly - I'm going to take some time to study the changes that you made, so that i can understand the errors that my code was making.

I'm still new to vba - this board has really helped me out though!

Thanks again

G

 

[Travis]

The Items that I changed:

1. Moved code to a Function "FormBeforeUpdate". This function returns a Boolean (or True/False) Value
2. Sent the Form itself as a ByRef Variable (This allows for complete control of the form in the code)
3. In the BeforeUpdate Event of the Forms set the Cancel variable = to the return from the Function
   [/list=1]