Simplifying this code

[vickan240sx]

Hi,

I'm new to VBA and would like to know if there's a more simple way to write out the code below:

 
Private Sub Save_and_New_Click()
If IsNull(Me.[BorrowerName]) Or IsNull(Me.[NewRenewalExtensionModification]) Or IsNull(Me.[LDDLogFileCompleteOnArrival]) Or IsNull(Me.[BranchNumber]) Or IsNull(Me.[IncreaseOrDecrease]) Or IsNull(Me.[Amount]) Or IsNull(Me.[LoanOfficer]) Or IsNull(Me.[LDDLogDateTimeCompleted]) Then
MsgBox ("Borrower Information Missing")
End If
If Not IsNull(Me.[BorrowerName]) And Not IsNull(Me.[NewRenewalExtensionModification]) And Not IsNull(Me.[LDDLogFileCompleteOnArrival]) And Not IsNull(Me.[BranchNumber]) And Not IsNull(Me.[IncreaseOrDecrease]) And Not IsNull(Me.[Amount]) And Not IsNull(Me.[LoanOfficer]) And Not IsNull(Me.[LDDLogDateTimeCompleted]) Then
DoCmd.GoToRecord , , acNext
End If
End Sub

Basically, I'm trying to add a feature to my form that requires that if mandatory fields are not input, the form will not allow the user to "save" and proceed to the next record. Instead a message box will appear and require the user to input all the necessary information before the form can be "saved" and moved to the next record.

I hope my explanation wasn't confusing.

Thank you for your help.

[pbaldy]

How about:

If ... Then
  MsgBox...
Else
  DoCmd...
End If

You've already tested all the fields, so the Else will only run if none of the fields is Null.

[vickan240sx]

How about:

If ... Then
  MsgBox...
Else
  DoCmd...
End If

You've already tested all the fields, so the Else will only run if none of the fields is Null.

I feel stupid...

[pbaldy]

Don't; we're all at some stage of learning, and nobody knows everything.

[ssanfu]

A couple of points.....

1) What if someone puts a space in a field?? It is not Null, so the record would probably save. I prefer to use

If Len(Trim(Me.[BorrowerName]))>0  OR ....

2) You might think about using the FORM before update instead of a button to check for empty required fields. What happens if someone (don't you just hate him?) leaves 1 or more controls empty and closes the form or changes the record without using the button? The data in the controls will still be saved, resulting in a partial record. Access automatically saves any changes when you move to different record.

Just thinking.........

[pbaldy]

I would also use the before update event:

http://www.baldyweb.com/BeforeUpdate.htm

[vickan240sx]

A couple of points.....

1) What if someone puts a space in a field?? It is not Null, so the record would probably save. I prefer to use

If Len(Trim(Me.[BorrowerName]))>0  OR ....

2) You might think about using the FORM before update instead of a button to check for empty required fields. What happens if someone (don't you just hate him?) leaves 1 or more controls empty and closes the form or changes the record without using the button? The data in the controls will still be saved, resulting in a partial record. Access automatically saves any changes when you move to different record.

Just thinking.........

You make a good point. I'll move the code to the before update and try out your code instead.

Thanks!

[vickan240sx]

How would I be able to customize the before update code to be specific to each form? I have several forms that would need different criteria under the before update event.

[vickan240sx]

How would I be able to customize the before update code to be specific to each form? I have several forms that would need different criteria under the before update event.

Nevermind...it's because I'm using a template form and copying/pasting it to create other forms.