Checking ALL fields for missing values

[Musa via AccessMonster.com]

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.

 

[Klatuu]

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.

 

[Douglas J. Steele]

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?)

 

[Klatuu]

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.

 

[Allen Browne]

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.

 

[Douglas J. Steele]

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.

 

[John W. Vinson]

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]

 

[Dirk Goldgar]

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.

 

[magmike]

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!)








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

 

[Douglas J. Steele]

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

(no private e-mails, please)



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?