how to use the BeforeUpdate event of a bound form in ACCESS 2010

I am trying to use more bound forms, but am having some issues.  For continuous form, if several of the fields are required, where and how would be the best way to validate the data before the record is saved?  That is, if the user has only entered three of five required fields, I need to return a message box telling them which fields still need to be completed.  I have, on my unbound form, a procedure that loops through the fields on the form with the Required tag and produces a Message box that displays those fields that are left to fill.  It also dislays little warning icons next to the label for the fields with missing data.  But if the form is continuous, what would be the best way to do this?
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
In a continuous form, you cannot make unbound controls visible (warning icons).  However, you could set the conditional formatting of required fields to a color if they currently have no value.

Then, you could use the Form_BeforeUpdate event to check for your required fields, display a message box and then set the Cancel argument to true if one of the required fields is missing.  something like:
Private Sub Form_BeforeUpdate(Cancel as integer)

    if me.txt_RequireField & "" = "" then
        msgbox "Required field must be entered"
        me.txt_RequiredField.setfocus
        Cancel = true
    Elseif me.txt_RequiredField2 & "" = "" then
        msgbox "Required field #2 must be entered"
        me.txt_RequiredField2.setfocus
        Cancel = true
    End If

End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
use the tag property of the controls, use this code in the before update event of the form
 Dim sCtlName As String, NullCtl As String, i As Integer
 sCtlName = ""
 Dim ctl As Control
 For Each ctl In Me.Controls


     If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
             If ctl.Tag = "mandatory" then
             If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
                 sCtlName = sCtlName & ctl.Name & ";"
                 Else
                
             End If
             end if
     End If



 Next ctl
 If Len(sCtlName) > 0 Then
     NullCtl = Mid(sCtlName, 1, InStr(sCtlName, ";") - 1)
     Me(NullCtl).SetFocus

     msgbox "Please complete all fields!"
     Exit Sub
     Else
end if

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To give a bit more commentary on Before Update:

The BU event can be cancelled, which means you can force the user to remain on that record until they either (a) enter valid data in all required fields or (b) revert their changes (or revert their record addition, if they're adding a new record). In a Bound form, that's the place to make validation, as the others have said.

Remember too that each Control has a Before Update event, and the Form has a Before Update event. If you want granular control, use the Control's BU event. If you want the user to enter a valid value in each control before they move to a different control, use the Control's BU event. Note, however, that this can get somewhat annoying to users, so be certain that's what you want. If you want to validate only when the user tries to save their changes/addition, then use the Form's BU event.

You can still use the methodology you described here:

"a procedure that loops through the fields on the form with the Required tag and produces a Message box that displays those fields that are left to fill."

This gives the user information necessary to fulfill the requirements. I don't think you can use the icon method, or the concept of coloring the background (unless you use Conditional Formatting, as Dale suggests).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PatHartmanCommented:
As the others have already mentioned, you can use the same loop technique with a continuous form.  The problem will be with colorization.  Continuous forms have more possibilities than do datasheet forms.  With datasheet forms, you are pretty much restricted to conditional formatting.  Although by adding hidden controls to a continuous form, you can get conditional formatting to work for you.  You just use hiddenctl1 in the conditional formatting statement for visiblectl1 to control formatting.  With the ds view form you don't have hidden controls although you can make the width 0 so that effectively the columns are hidden.  The problem is that the user could undo that by choosing show all in the column properties of the form.

Access keeps only one set of properties for a form so when you are working with continuous and ds forms where you are seeing multiple instances of the same form, formatting done with VBA will appear to affect all visible rows which is why you need to try to use conditional formatting which circumvents the problem.
Rey Obrero (Capricorn1)Commented:
.


in the code i posted above, you can change the backcolor of the controls that are nulls.
Sandra SmithRetiredAuthor Commented:
I want to thank all for the explanations and clarifications.  This is really helping in reducing my coding, won't get rid of unbound forms completely, but I can take advantage a little more of bound forms.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.