RE: Code For Required Fields

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.


"Ben Webber" wrote:

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

Relevant Pages

  • RE: "No current record" after Quit in Form_Unload
    ... You could disable the X to force your users to exit gracefully. ... Private Sub Form_Close ... Dim myDB As Database, myTable As TableDef, myQuery As QueryDef, mySet ... You might say this error message is good, ...
    (microsoft.public.access.forms)
  • Re: Handling "exit" without error msg
    ... >Sub Form_Close ... if you NEED to handle errors in your exit ... >Private mflgClose As Boolean ... >> get an error message because I have no code to handle ...
    (microsoft.public.access.formscoding)
  • Re: On error goto error handler ...question
    ... I would like the error message and then click ok to try the ... sub again or exit to leave the program. ... or error number to a variable so I can upload it into a database. ...
    (microsoft.public.vb.general.discussion)
  • Need replacement of ME
    ... got the error message “Compile error: ... Dim ctl As Control ... For Each ctl In Me.Controls ... End Sub ...
    (microsoft.public.access.modulesdaovba)
  • Re: On error goto error handler ...question
    ... I would like the error message and then click ok to try the ... sub again or exit to leave the program. ... Private Sub Command1_Click ...
    (microsoft.public.vb.general.discussion)