Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / March 2007

Tip: Looking for answers? Try searching our database.

Required Field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kyle Friesen - 24 Jan 2005 23:44 GMT
Hello,
I have a form that I want to make a few fields required. If I set the required property to Yes in the table the form is linked to, an error message comes up when one tries to save the form. However, this does not identify which fields are required for input. Is there a way to communicate which field the user needs to update. For example Msgbox "You must complete the Customer field".

Thanks,
Kyle
Dave - 25 Jan 2005 00:21 GMT
Insert before_update events on each of the fields you want to validate. Use
the ISNULL function to check if the field is empty and if it is, use the
MSGBOX function to explain the problem to the end user.

eg:
if isnull([txtYOURFIELDNAME]) then
msgbox "You cannot leave this field empty"
cancel = true
end if

Hope that helps

> Hello,
> I have a form that I want to make a few fields required. If I set the required property to Yes in the table the form is linked to, an error message comes up when one tries to save the form. However, this does not identify which fields are required for input. Is there a way to communicate which field the user needs to update. For example Msgbox "You must complete the Customer field".
>
> Thanks,
> Kyle
Kyle Friesen - 25 Jan 2005 00:56 GMT
Thank you and excuse my ignorance. I am a novice.

where/how do I insert before_update events on each of the required fields? I inserted the code in the save form_click module and the message box comes up upon click but not sure it is validating the null values, I think I just inserted the message box.

Thanks, I really appreciate it.
Dave - 25 Jan 2005 01:27 GMT
OK,
double click the text box you want to add the validation to. This will bring
up the property box for this text box. Click on the Event tab, then click in
the Before Update field. Three dots will appear at the right. Click on them.
Now, paste the following code in where the cursor is flashing:

If IsNull(Me![YOURFIELD]) Then
   MsgBox ("YOURFIELD must not be left empty.")
   Cancel = True
   Exit Sub
End If

You'll have to change YOURFIELD to the name of the text box once you've
pasted it in to your module.
Hope this helps.
Dave

> Thank you and excuse my ignorance. I am a novice.
>
> where/how do I insert before_update events on each of the required fields? I inserted the code in the save form_click module and the message box comes up upon click but not sure it is validating the null values, I think I just inserted the message box.
>
> Thanks, I really appreciate it.
Dirk Goldgar - 25 Jan 2005 01:56 GMT
> Hello,
> I have a form that I want to make a few fields required. If I set the
[quoted text clipped - 3 lines]
> communicate which field the user needs to update. For example Msgbox
> "You must complete the Customer field".

I'd recommend a somewhat different approach from that suggested by Dave.
Rather than writing a BeforeUpdate event procedure for each control --
which won't work anyway unless the user actually modifies the control --
it would be a lot simpler to write a single BeforeUpdate event for the
form itself.  In that procedure, check each required field to see if it
has been filled in, and accumulate a list of those that haven't been.
Then if that list is not empty, you can cancel the update and display an
error message that includes the list of fields to be fixed.

Here's a function you can paste into a standard module and call from the
form's BeforeUpdate event.  It checks every text box, combo box, list
box, and check box on the form, but only if the control's Tag property
is set to "Required".  That lets you designate, by tagging, those fields
that are required.  The function is passed a reference to the form to be
checked and returns True if required fields are missing, as well as
displaying a message and setting the focus to the first control that is
required but empty.

'----- start of function code -----
Function fncRequiredFieldsMissing(frm As Form) As Boolean

   Dim ctl As Access.Control
   Dim strErrCtlName As String
   Dim strErrorMessage As String
   Dim lngErrCtlTabIndex As Long
   Dim blnNoValue As Boolean

   lngErrCtlTabIndex = 99999999  'more than max #controls

   For Each ctl In frm.Controls
       With ctl
           Select Case .ControlType
               Case acTextBox, acComboBox, acListBox, acCheckBox
                   If .Tag = "Required" Then
                       blnNoValue = False
                       If IsNull(.Value) Then
                           blnNoValue = True
                       Else
                           If .ControlType = acTextBox Then
                               If Len(.Value) = 0 Then
                                   blnNoValue = True
                               End If
                           End If
                       End If
                       If blnNoValue Then
                           strErrorMessage = strErrorMessage & vbCr & _
                                       "   " & .Name
                           If .TabIndex < lngErrCtlTabIndex Then
                               strErrCtlName = .Name
                               lngErrCtlTabIndex = .TabIndex
                           End If
                       End If
                   End If
               Case Else
                   ' Ignore this control
           End Select
       End With
   Next ctl

   If Len(strErrorMessage) > 0 Then
       MsgBox "The following fields are required:" & vbCr & _
                   strErrorMessage, _
               vbInformation, "Required Fields Are Missing"
       frm.Controls(strErrCtlName).SetFocus
       fncRequiredFieldsMissing = True
   Else
       fncRequiredFieldsMissing = False
   End If

End Function
'----- end of function code -----

To use the function, create a BeforeUpdate event for your form like
this:

'----- start of event procedure code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

   Cancel = fncRequiredFieldsMissing(Me)

End Sub
'----- end of event procedure code -----

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dave - 25 Jan 2005 02:15 GMT
A much better solution than mine Dirk.
I assumed that there would at least be a default value if the field was set
to not allow nulls at the table level.

> > Hello,
> > I have a form that I want to make a few fields required. If I set the
[quoted text clipped - 86 lines]
> End Sub
> '----- end of event procedure code -----
Kyle Friesen - 25 Jan 2005 19:16 GMT
Thank You Dirk and Dave!!
sdh31873 - 08 Mar 2007 19:33 GMT
Awesome code but I have a question....It works great but I use my form as a
data entry form and I have a subform that data must be keyed into as well.
It prompts the user of all missing "required" fields before they get to the
subform to enter data.  I need to see if there is a way they can go through
all of their data and then let them know of the missing fields?  Thanks.

>> Hello,
>> I have a form that I want to make a few fields required. If I set the
[quoted text clipped - 84 lines]
>End Sub
>'----- end of event procedure code -----
sdh31873 - 08 Mar 2007 21:25 GMT
Okay...this code works wonderful, but let's say they get the message box that
the field name "Phone Number" is required.  Let's say they absolutely don't
know the phone number.  I don't want them to go forward without this
information so I want them to be able to close out of the form without saving.
I can't figure out how.  Please help.

>> Hello,
>> I have a form that I want to make a few fields required. If I set the
[quoted text clipped - 84 lines]
>End Sub
>'----- end of event procedure code -----
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.