Code For Required Fields

[Guest]

Hi,

I need to check to make sure all 40 text fields on a form are inputted. I
have 40 fields on a form that I have marked in the TAG section on each field
property to be 'reqd'. I then run a routine on form before update to check
if all the fields are inputted by checking for string length.

I now have to add an exception line......meaning .....if DESC field contents
are called "LAPTOP" and the FIRST_NAME field is empty then I need to bypass
the error message and exit the subroutine.

It is not working ...any ideas?


Private Sub Form_BeforeUpdate(Cancel As Integer) Dim ctl As Control For Each
ctl In Me.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.Value & vbNullString) = 0 Then
If Me!DESC_ = "LAPTOP" And Me!FIRST_NAME = "" Then Exit Sub
MsgBox "You must enter a value into """ & _
ctl.Controls(0).Caption & """."
ctl.SetFocus
Exit Sub
End If
End If
Next
End Sub


Thanks

 

[Guest]

Hi, Ben.

I need to check to make sure all 40 text fields on a form are inputted. I
have 40 fields on a form that I have marked in the TAG section on each field
property to be 'reqd'. I then run a routine on form before update to check
if all the fields are inputted by checking for string length.

I now have to add an exception line......meaning .....if DESC field contents
are called "LAPTOP" and the FIRST_NAME field is empty then I need to bypass
the error message and exit the subroutine.

By saying your code below is "not working," I take it that the cursor is
still moving on to another record, even after the message box informs the
user that a field needs to be filled in. Try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrHandler

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.Value & vbNullString) = 0 Then

If (Me!DESC_ = "LAPTOP" And Nz(Me!FIRST_NAME, "") = "") Then
Exit Sub
Else
MsgBox "You must enter a value into """ & _
ctl.Controls(0).Caption & """."
Cancel = True
End If

ctl.SetFocus
Exit Sub
End If
End If
Next

Exit Sub

ErrHandler:

MsgBox "Error in Form_BeforeUpdate( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & Err.Number & vbCrLf & Err.Description

End Sub ' Form_BeforeUpdate( )


It's easiest to assign the "Required" field property on each of the table's
fields while in Table Design View, but we don't always have that luxury when
we have multiple forms, or even multiple databases, using the same table, and
we want some fields to be required and others not required, depending upon
the form. You might want to analyze the application's needs and see if it
makes more sense to put the "Required" Property on the table's fields --
instead of in the form's logic -- to reduce future programming maintenance.
If you do this analysis, then you will also need to consider this table's
relationships with other tables with regard to the foreign keys and cascading
constraints to help determine which fields need to be required fields.

Please note that with the above logic, if the "DESC_" field is the only
field that's filled in, then the record will still be saved as the cursor
moves to another record, without any other fields needing to be filled in.
If that's not the logic that you want, then please let me know and we can
alter this. Also, you mentioned the "DESC" field, but in the code it's
written "DESC_". I'm assuming the code isn't a typo.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[Graham Mandeno]

Hi Ben

You're missing the vital step of setting Cancel=true to prevent the update
occuring.

Insert:
Cancel = True
just after ctl.SetFocus