I would like an Error Message to display the names of the Fields missing data
entry BEFORE the a macro to open the next form is intiated. The forms are
mostly point n click in option groups. I want to make sure the person
entering data did not miss any fields before clicking the macro to open the
next form. If they do miss a field, I want the name of the field displayed
and the focus of the cursor set back to the missing item. I'm relatively new
to VBA code, so if there is an easy code that anyone knows..it would be
helpful Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

Use the form's Before Update event to ensure controls have data in them. If
you find a field with missing data, you cancel the update. Here is an
outline:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnMissingData As Boolean

If IsNull(Me.FirstControl) Then
MsgBox "FirstControl Data Missing"
Cancel = True
Me.FirstControl.SetFocus
Exit Sub
End If

If IsNull(Me.SecondControl) Then
MsgBox "SecondControl Data Missing"
Cancel = True
Me.SecondControl.SetFocus
Exit Sub
End If

End Sub

Just do the same for each control you want to check using your own names.
--
Dave Hargis, Microsoft Access MVP


"Musa via AccessMonster.com" wrote:

> I would like an Error Message to display the names of the Fields missing data
> entry BEFORE the a macro to open the next form is intiated. The forms are
> mostly point n click in option groups. I want to make sure the person
> entering data did not miss any fields before clicking the macro to open the
> next form. If they do miss a field, I want the name of the field displayed
> and the focus of the cursor set back to the missing item. I'm relatively new
> to VBA code, so if there is an easy code that anyone knows..it would be
> helpful Thanks.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...forms/200801/1
>
>

I prefer checking all the fields, rather than stopping when the first
missing field is found:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMessage As String

If IsNull(Me.FirstControl) Then
strMessage = strMessage & "FirstControl Data Missing" & vbCrLf
End If

If IsNull(Me.SecondControl) Then
strMessage = strMessage & "SecondControl Data Missing" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage
Cancel = True
End If

End Sub

Granted, you don't get focus set to the invalid control doing that (but then
if you've got multiple errors, to which field would you set focus?)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" <(E-Mail Removed)> wrote in message
news:AA76A65F-7396-4CF2-8B89-(E-Mail Removed)...
> Use the form's Before Update event to ensure controls have data in them.
> If
> you find a field with missing data, you cancel the update. Here is an
> outline:
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> Dim blnMissingData As Boolean
>
> If IsNull(Me.FirstControl) Then
> MsgBox "FirstControl Data Missing"
> Cancel = True
> Me.FirstControl.SetFocus
> Exit Sub
> End If
>
> If IsNull(Me.SecondControl) Then
> MsgBox "SecondControl Data Missing"
> Cancel = True
> Me.SecondControl.SetFocus
> Exit Sub
> End If
>
> End Sub
>
> Just do the same for each control you want to check using your own names.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Musa via AccessMonster.com" wrote:
>
>> I would like an Error Message to display the names of the Fields missing
>> data
>> entry BEFORE the a macro to open the next form is intiated. The forms are
>> mostly point n click in option groups. I want to make sure the person
>> entering data did not miss any fields before clicking the macro to open
>> the
>> next form. If they do miss a field, I want the name of the field
>> displayed
>> and the focus of the cursor set back to the missing item. I'm relatively
>> new
>> to VBA code, so if there is an easy code that anyone knows..it would be
>> helpful Thanks.
>>
>> --
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/For...forms/200801/1
>>
>>

All of them? <g>

I would check all of them, too, but the OP specified the setting focus.
In reality, I doubt I would check all fields. I would check only required
fields.
--
Dave Hargis, Microsoft Access MVP


"Douglas J. Steele" wrote:

> I prefer checking all the fields, rather than stopping when the first
> missing field is found:
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> Dim strMessage As String
>
> If IsNull(Me.FirstControl) Then
> strMessage = strMessage & "FirstControl Data Missing" & vbCrLf
> End If
>
> If IsNull(Me.SecondControl) Then
> strMessage = strMessage & "SecondControl Data Missing" & vbCrLf
> End If
>
> If Len(strMessage) > 0 Then
> MsgBox strMessage
> Cancel = True
> End If
>
> End Sub
>
> Granted, you don't get focus set to the invalid control doing that (but then
> if you've got multiple errors, to which field would you set focus?)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:AA76A65F-7396-4CF2-8B89-(E-Mail Removed)...
> > Use the form's Before Update event to ensure controls have data in them.
> > If
> > you find a field with missing data, you cancel the update. Here is an
> > outline:
> >
> > Private Sub Form_BeforeUpdate(Cancel As Integer)
> > Dim blnMissingData As Boolean
> >
> > If IsNull(Me.FirstControl) Then
> > MsgBox "FirstControl Data Missing"
> > Cancel = True
> > Me.FirstControl.SetFocus
> > Exit Sub
> > End If
> >
> > If IsNull(Me.SecondControl) Then
> > MsgBox "SecondControl Data Missing"
> > Cancel = True
> > Me.SecondControl.SetFocus
> > Exit Sub
> > End If
> >
> > End Sub
> >
> > Just do the same for each control you want to check using your own names.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Musa via AccessMonster.com" wrote:
> >
> >> I would like an Error Message to display the names of the Fields missing
> >> data
> >> entry BEFORE the a macro to open the next form is intiated. The forms are
> >> mostly point n click in option groups. I want to make sure the person
> >> entering data did not miss any fields before clicking the macro to open
> >> the
> >> next form. If they do miss a field, I want the name of the field
> >> displayed
> >> and the focus of the cursor set back to the missing item. I'm relatively
> >> new
> >> to VBA code, so if there is an easy code that anyone knows..it would be
> >> helpful Thanks.
> >>
> >> --
> >> Message posted via AccessMonster.com
> >> http://www.accessmonster.com/Uwe/For...forms/200801/1
> >>
> >>
>
>
>

Doug and Dave have explained that the crucial piece of the puzzle is to use
Form_BeforeUpdate to run these checks. That's the only wy to catch them all
before the record is saved.

The other part of your question was how to check ALL fields for nulls. Doug
hinted that the simplest solution is to open the table in design view, and
set the Required property to Yes for all fields. That way, Access won't
accept the record if any field is left blank. It would not be a common
scenario to turn Required on for all fields.

You were asking for some VBA code to do this. That's not a simple thing. You
could loop through the Fields of the form's Recordset, but there's a couple
of snags:
a) If the form is based on a query, some null fields from other tables may
be permissible, so you would need to check the SourceTable of the fields.
b) In alerting the user of a null, what name do you use? The field name is
not necessarily the same as the label attached to the control bound to the
field (which is the name the user knows the field by.)

Another approach is to loop through the controls on the form. This is more
effort, as:
a) some controls don't have a Control Source (e.g. lines and labels)
b) some controls may be bound to expressions (starts with =)
c) some controls may be unbound
d) some controls may be bound to calculated query fields (no SourceTable)
But you can get the attached label this way (or guess at the label the user
sees over that column in a continuous form.)

If you are interested in how to loop through the controls on a form to
determine which ones are bound to a table field, this code does that:
http://allenbrowne.com/ser-56.html
It actually seeks to lock the bound controls, but the looping example could
be useful.

For an example of how to figure out the name by which the user knows the
field, see Caption4Control() in this page:
http://allenbrowne.com/AppFindAsUTypeCode.html

Hope that's useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Musa via AccessMonster.com" <u40920@uwe> wrote in message
news:7f0b53be038dd@uwe...
>I would like an Error Message to display the names of the Fields missing
>data
> entry BEFORE the a macro to open the next form is intiated. The forms are
> mostly point n click in option groups. I want to make sure the person
> entering data did not miss any fields before clicking the macro to open
> the
> next form. If they do miss a field, I want the name of the field displayed
> and the focus of the cursor set back to the missing item. I'm relatively
> new
> to VBA code, so if there is an easy code that anyone knows..it would be
> helpful Thanks.

You could always change my code to something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctlFirstError As Control
Dim booProblem As Boolean
Dim strMessage As String

If IsNull(Me.FirstControl) Then
If booProblem = False Then
Set ctlFirstError = Me.FirstControl
booProblem = True
End If
strMessage = strMessage & "FirstControl Data Missing" & vbCrLf
End If

If IsNull(Me.SecondControl) Then

If booProblem = False Then
Set ctlFirstError = Me.SecondControl
booProblem = True
End If
strMessage = strMessage & "SecondControl Data Missing" & vbCrLf
End If

If booProblem Then
MsgBox strMessage
ctlFirstError.SetFocus
Cancel = True
End If

End Sub

However, I repeat my question about to which field you'd set focus if there
were multiple of them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" <(E-Mail Removed)> wrote in message
news:C44D9A85-4DA9-4A9D-B91A-(E-Mail Removed)...
> All of them? <g>
>
> I would check all of them, too, but the OP specified the setting focus.
> In reality, I doubt I would check all fields. I would check only required
> fields.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Douglas J. Steele" wrote:
>
>> I prefer checking all the fields, rather than stopping when the first
>> missing field is found:
>>
>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>> Dim strMessage As String
>>
>> If IsNull(Me.FirstControl) Then
>> strMessage = strMessage & "FirstControl Data Missing" & vbCrLf
>> End If
>>
>> If IsNull(Me.SecondControl) Then
>> strMessage = strMessage & "SecondControl Data Missing" & vbCrLf
>> End If
>>
>> If Len(strMessage) > 0 Then
>> MsgBox strMessage
>> Cancel = True
>> End If
>>
>> End Sub
>>
>> Granted, you don't get focus set to the invalid control doing that (but
>> then
>> if you've got multiple errors, to which field would you set focus?)
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Klatuu" <(E-Mail Removed)> wrote in message
>> news:AA76A65F-7396-4CF2-8B89-(E-Mail Removed)...
>> > Use the form's Before Update event to ensure controls have data in
>> > them.
>> > If
>> > you find a field with missing data, you cancel the update. Here is an
>> > outline:
>> >
>> > Private Sub Form_BeforeUpdate(Cancel As Integer)
>> > Dim blnMissingData As Boolean
>> >
>> > If IsNull(Me.FirstControl) Then
>> > MsgBox "FirstControl Data Missing"
>> > Cancel = True
>> > Me.FirstControl.SetFocus
>> > Exit Sub
>> > End If
>> >
>> > If IsNull(Me.SecondControl) Then
>> > MsgBox "SecondControl Data Missing"
>> > Cancel = True
>> > Me.SecondControl.SetFocus
>> > Exit Sub
>> > End If
>> >
>> > End Sub
>> >
>> > Just do the same for each control you want to check using your own
>> > names.
>> > --
>> > Dave Hargis, Microsoft Access MVP
>> >
>> >
>> > "Musa via AccessMonster.com" wrote:
>> >
>> >> I would like an Error Message to display the names of the Fields
>> >> missing
>> >> data
>> >> entry BEFORE the a macro to open the next form is intiated. The forms
>> >> are
>> >> mostly point n click in option groups. I want to make sure the person
>> >> entering data did not miss any fields before clicking the macro to
>> >> open
>> >> the
>> >> next form. If they do miss a field, I want the name of the field
>> >> displayed
>> >> and the focus of the cursor set back to the missing item. I'm
>> >> relatively
>> >> new
>> >> to VBA code, so if there is an easy code that anyone knows..it would
>> >> be
>> >> helpful Thanks.
>> >>
>> >> --
>> >> Message posted via AccessMonster.com
>> >> http://www.accessmonster.com/Uwe/For...forms/200801/1
>> >>
>> >>
>>
>>
>>

On Fri, 1 Feb 2008 10:08:07 -0500, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

>However, I repeat my question about to which field you'd set focus if there
>were multiple of them.
>

I'd say whichever was most convenient. The user will need to touch all of them
anyway; does it matter which they do first?

John W. Vinson [MVP]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Fri, 1 Feb 2008 10:08:07 -0500, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>
>>However, I repeat my question about to which field you'd set focus if
>>there
>>were multiple of them.
>>
>
> I'd say whichever was most convenient. The user will need to touch all of
> them
> anyway; does it matter which they do first?


The function I use for this sets it to the first blank control in the tab
order. It uses the Tag property to determine which controls to check.
Seeing as how we're posting sample code, here's mine:

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

On Error Resume Next

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim strMsgName 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

strMsgName = vbNullString
If .Controls.Count = 1 Then
strMsgName = .Controls(0).Caption
If right$(strMsgName, 1) = ":" Then
strMsgName = Trim$(Left$(strMsgName,
Len(strMsgName) - 1))
End If
End If
If Len(strMsgName) = 0 Then
strMsgName = .Name
Select Case Left$(strMsgName, 3)
Case "txt", "cbo", "lst", "chk"
strMsgName = Mid(strMsgName, 4)
End Select
End If

strErrorMessage = strErrorMessage & vbCr & _
" " & strMsgName

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

You'll probably have to fix line breaks in the above that were introduced by
the newsreader.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On Feb 1, 9:08*am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> You could always change my code to something like:
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> Dim ctlFirstError As Control
> Dim booProblem As Boolean
> Dim strMessage As String
>
> * If IsNull(Me.FirstControl) Then
> * * If booProblem = False Then
> * * * Set ctlFirstError = Me.FirstControl
> * * * booProblem = True
> * * End If
> * * strMessage = strMessage & "FirstControl Data Missing" & vbCrLf
> * End If
>
> * If IsNull(Me.SecondControl) Then
>
> * * If booProblem = False Then
> * * * Set ctlFirstError = Me.SecondControl
> * * * booProblem = True
> * * End If
> * * strMessage = strMessage & "SecondControl Data Missing" & vbCrLf
> * End If
>
> * If booProblem Then
> * * MsgBox strMessage
> * * ctlFirstError.SetFocus
> * * Cancel = True
> * End If
>
> End Sub
>
> However, I repeat my question about to whichfieldyou'd set focus if there
> were multiple of them.
>
> --
> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> (no e-mails, please!)
>
> "Klatuu" <Kla...@discussions.microsoft.com> wrote in message
>
> news:C44D9A85-4DA9-4A9D-B91A-(E-Mail Removed)...
>
>
>
> > All of them? <g>
>
> > I would check all of them, too, but the OP specified the setting focus.
> > In reality, I doubt I would check all fields. *I would check onlyrequired
> > fields.
> > --
> > Dave Hargis, Microsoft Access MVP
>
> > "Douglas J. Steele" wrote:
>
> >> I prefer checking all the fields, rather than stopping when the first
> >> missingfieldis found:
>
> >> Private Sub Form_BeforeUpdate(Cancel As Integer)
> >> Dim strMessage As String
>
> >> * If IsNull(Me.FirstControl) Then
> >> * * strMessage = strMessage & "FirstControl Data Missing" & vbCrLf
> >> * End If
>
> >> * If IsNull(Me.SecondControl) Then
> >> * * strMessage = strMessage & "SecondControl Data Missing" & vbCrLf
> >> * End If
>
> >> * If Len(strMessage) > 0 Then
> >> * * MsgBox strMessage
> >> * * Cancel = True
> >> * End If
>
> >> End Sub
>
> >> Granted, you don't get focus set to the invalid control doing that (but
> >> then
> >> if you've got multiple errors, to whichfieldwould you set focus?)
>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >>http://I.Am/DougSteele
> >> (no e-mails, please!)
>
> >> "Klatuu" <Kla...@discussions.microsoft.com> wrote in message
> >>news:AA76A65F-7396-4CF2-8B89-(E-Mail Removed)...
> >> > Use the form's Before Update event to ensure controls have data in
> >> > them.
> >> > If
> >> > you find afieldwith missing data, you cancel the update. *Here is an
> >> > outline:
>
> >> > Private Sub Form_BeforeUpdate(Cancel As Integer)
> >> > Dim blnMissingData As Boolean
>
> >> > * *If IsNull(Me.FirstControl) Then
> >> > * * * *MsgBox "FirstControl Data Missing"
> >> > * * * *Cancel = True
> >> > * * * *Me.FirstControl.SetFocus
> >> > * * * *Exit Sub
> >> > * *End If
>
> >> > * *If IsNull(Me.SecondControl) Then
> >> > * * * *MsgBox "SecondControl Data Missing"
> >> > * * * *Cancel = True
> >> > * * * *Me.SecondControl.SetFocus
> >> > * * * *Exit Sub
> >> > * *End If
>
> >> > End Sub
>
> >> > Just do the same for each control you want to check using your own
> >> > names.
> >> > --
> >> > Dave Hargis, Microsoft Access MVP
>
> >> > "Musa via AccessMonster.com" wrote:
>
> >> >> I would like an Error Message to display the names of the Fields
> >> >> missing
> >> >> data
> >> >> entry BEFORE the a macro to open the next form is intiated. The forms
> >> >> are
> >> >> mostly point n click in option groups. I want to make sure the person
> >> >> entering data did not miss any fields before clicking the macro to
> >> >> open
> >> >> the
> >> >> next form. If they do miss afield, I want the name of thefield
> >> >> displayed
> >> >> and the focus of the cursor set back to the missing item. I'm
> >> >> relatively
> >> >> new
> >> >> to VBA code, so if there is an easy code that anyone knows..it would
> >> >> be
> >> >> helpful *Thanks.
>
> >> >> --
> >> >> Message posted via AccessMonster.com
> >> >>http://www.accessmonster.com/Uwe/For...orms/200801/1- Hide quoted text -
>
> - Show quoted text -

I've tried all the posted ideas, and I get the same problem with each
one of them - the form closes after it goes to the control! I'm
actually just trying to check one field, but want to leave the code
open for other fields in the future. Why would the form close?

There's nothing in the code I suggested that would close the form (nor do I
see anything in the other suggestions that should close it)

What's the actual code you're using?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"magmike" <(E-Mail Removed)> wrote in message
news:15be5e7b-c0d0-4877-b49f-(E-Mail Removed)...

I've tried all the posted ideas, and I get the same problem with each
one of them - the form closes after it goes to the control! I'm
actually just trying to check one field, but want to leave the code
open for other fields in the future. Why would the form close?