Re: Checking for null values on form's BeforeUpdate event - How?
- From: "Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 15:45:40 -0500
You also made a small error that may interest you. <g>
(You forgot to concatenate the control name to the rest of the message).
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:271DD1C6-CDEC-4307-9EBD-7692C89926E2@xxxxxxxxxxxxxxxx
The reason it is setting the focus to the last control that is null is you
are always looping through the entire collection. All you need to do is
bail
out of the For Next loop as soon as you find one that fails. Here is the
fix. Notice I made a small change that may interest you:
If ctl.Tag = "GenericString" Then
If Len(Trim(Nz(ctl, vbNullString))) Then
MsgBox ctl.Name " Requires A Value", vbExclamation + vbOkOnly
Cancel = True
ctl.SetFocus
Exit For
End If
End If
--
Dave Hargis, Microsoft Access MVP
"TryingToLearnVBA" wrote:
Greetings,
I am looking for advice on how to most efficiently write a procedure to
perform the following algorithm on a form's BeforeUpdate event:
1. Cycle through a form's fields (one field at a time)
2. Check for null values or zero length strings (in each field, depending
on
data type?)
3. Display a message box [vbOK] to indicate that the record cannot be
saved
at this time...(if it finds any null or zero length)
4. Cancel the BeforeUpdate event
5. Set focus to the first null or zero length field that it finds
I have researched this topic online and have tried the following
modification of some code I found on this discussion group (see below):
'Dim ctl as Control
'For Each ctl In Me.Section(0).Controls
'If ctl.Tag = "GenericString" Then
' If IsNull(ctl) or Len(ctl) = 0 Then
' MsgBox "GenericStatement", vbOKCancel
' Cancel = True
' ctl.SetFocus
' End If
'End If
The problem with my modified code is that it sets the focus to the last
null
field it finds. Additionally, I am not sure if I am checking the data
thouroughly enough to catch all null values and zero length strings,
because
I am checking fields containing the following three data types: Text,
Number,
and Date?
I appreciate any suggestions. This is my first post, so I apologize if
it
is a bit exhaustive...
END-
--
Thank You.
.
- Follow-Ups:
- Prev by Date: Re: Copy all contents of a recordset to clipboard
- Next by Date: Re: Checking for null values on form's BeforeUpdate event - How?
- Previous by thread: Re: Checking for null values on form's BeforeUpdate event - How?
- Next by thread: Re: Checking for null values on form's BeforeUpdate event - How?
- Index(es):
Relevant Pages
|
