Small modification to code

[hardhitter06]

Hi All,

I have a form where all fields are required except comments or record will not save. If they are filled out properly, then it will display "record saved"

Comments is my last field on the form and i have this code

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If IsNull(ctrl) Then
MsgBox ctrl.Name & " Cannot Be Left Empty!"
Cancel = True
ctrl.SetFocus
Exit Sub
End If
End If
Next
MsgBox "Record Saved!"
End Sub

I just want to modify this so that it ignores the Comments field since no data has to be entered.

 

[boblarson]

The easiest way I can think of is to put something like skip in the tag property of the control for the comments. Then you can just modify your current code to this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.Tag <> "skip" Then
   If ctrl.ControlType = acTextBox Then
      If IsNull(ctrl) Then
         MsgBox ctrl.Name & " Cannot Be Left Empty!"
         Cancel = True
         ctrl.SetFocus
         Exit Sub
      End If
   End If
End If
Next
MsgBox "Record Saved!"
End Sub

 

[MSAccessRookie]

Hi All,

I have a form where all fields are required except comments or record will not save. If they are filled out properly, then it will display "record saved"

Comments is my last field on the form and i have this code

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then

' Ignore Comments Control Name

If ctrl.Name <> {CommentsControlName} Then
If IsNull(ctrl) Then
MsgBox ctrl.Name & " Cannot Be Left Empty!"
Cancel = True
ctrl.SetFocus
Exit Sub
End If
End If
End If
Next
MsgBox "Record Saved!"
End Sub

I just want to modify this so that it ignores the Comments field since no data has to be entered.

A reference to CommentsControlName similar to the one depicted above might work. Substitute as required.

NOTE: I just noticed the SKIP suggestion by BobLarson. This is another example of multiple ways to do the same thing, and how I learn new things here all of the time. Although my answer works in your particular case, it requires modification each time another field needs to be excluded from the check, so his answer would be better.

 

[hardhitter06]

Perfect! Thanks!