Validation; LostFocus & BeforeUpdate; form vs control level; change/modify user entry during validation; bound vs unbound controls; Access 2007

Question

I would like to insure traits about data in one control on a form.  I would like the testing to occur on attempting to leave the control [beforeUpdate or Lostfocus?].

In response to invalid data, I would like to be able to either void the entry and return to the control [I can do this with BeforeUpdate...] or make the correction to the data if appropriate.

I use a simple textbox control to  polish the programming skill set for my other, later, tasks.  The table's field is a text string, Length=5.   The traits I want:

      Left Justified; No Internal Spaces, All Caps, Uniqueness.

I would like this testing to occur on existing records and on new records as the user leaves the control in either case.

The user should be able to amend an existing record's value to another value, also unique.  I have a seperate ID as the primary, linking key.  This 5 letter code simplifies data entry.  Using the hidden ID eliminates much of the Cascade issues on changes to the code.

I can place tests in the beforeUpdate for this control -- using cancel = true and --.undo during the tests. 

I would prefer that the testing occur on leaving that particular control, rather than at the form level on updates

At the moment, I have bound controls.  But I could create unbound controls, and manually read/stuff back data to the dbs using recordsets [alot more programming, to my mind].  I'm not sure if this is easier [solving, too issues on leaving edits open on multiuser environments.

In attempting to get this to work cleanly, I have tried: setting the table's traits to required and not; indexes w/ or w/o duplicates; variations on coding and placement into events [BeforeUpdate and LostFocus]

I have these problems:

1. I can't seem to get the test for non-blank to work, as a user can leave a control [without making a change/entry and dirtying the content].   I'm not sure if "required" in the table design solved this -- but if it does, I think I got a system error, rather than a user friendly myError routine.

2.  I can't figure out, after all the toying around with the code, if some of these tests have to be in the LostFocus [say, to allow for modification -- ie: stripping blanks]

3.  I can't seem to figure out how to use the input mask, as when I use it the data entry gets funky compared to without it.

4.  I am tempted to think I have to use recordsets and programmed Add/Save and etc., to get the control [unbound] to be restuffed with a "corrected" value.  In this trivial case, the changes would be stripping of spaces, but generally, for more complex uses contemplated, I can't see another way -- and wish there were.

5.  Masking doesn't prevent spaces, I would have thought "LLLLL" would do it from the access help file's implications and wording.

6.  I am about to take delivery on Off2010, and wonder if the rules about Everything Will Change, if I install Acc2010 over 2007...

So, I seek suggestions.  Pointers to other URLs would be ok, too.

On:  Programming with Bound vs Unbound, in the context of my problems

On:  Programming utility and speed: Using unbound&Recordsets vs native handling by Access of bound controls

On:  The placement of testing: LostFocus vs BeforeUpdate or other events; Form level versus Control level

---

What follows is one iteration of the control's coding that I played around with in the beforeUpdate event.

Private Sub txtAttorneyCode_BeforeUpdate(Cancel As Integer)

    Dim str As String

        If "" = Nz(Me!txtAttorneyCode, "") Then

        MsgBox "Value Required in this Field"

        Cancel = True

        Me!txtAttorneyCode.Undo

        Exit Sub

    End If

    ' check for internal spaces

    '    str = Trim(Me!txtAttorneyCode)

    If 0 <> InStr(Me!txtAttorneyCode, " ") Then

        'spaces are not legal

        MsgBox "No spaces permitted in this field"

        Cancel = True

        Me!txtAttorneyCode.Undo

        Exit Sub

    End If

    'Prevent Duplicate Values

        'special case: THIS [existing] record's value is changed back to what it was

        ' when the user started changes, and then ends up just retyping the same

        If Me.txtAttorneyCode = Me.txtAttorneyCode.OldValue Then

            Cancel = False

            Exit Sub    ' no changes to value, so its ok

        End If

    'do lookup

    If (Not IsNull(DLookup("[fldAttorneyCode]", _

            "tblAttorney", "[fldattorneycode] ='" _

            & Me!txtAttorneyCode & "'"))) Then

        MsgBox "Code already exists -- choose another"

        Cancel = True

        Me!txtAttorneyCode.Undo

        Exit Sub

    End If

End Sub

Answer 1

Thank you, both, for your answers.

10yrs ago, between the 90s and preKatrina, I had been writing code with VB/VBA, recordsets and the like, and while no expert then or now, I was facile enough with them to be moderately fluent.  Now, the learning curve reappears, and I have the right questions without answers.

For example, I had seemed to remember, vaguely, an easier way to strip out the spaces after the user leaves the control, and after your comments now think the keycode trap might be the way to go [rather than a LostFocus routine], especially if an adjacent label says 'no spaces....'

Recovering my memory on fluent coding for recordsets, and SQLs, ahead....I suspect.

Answer 2

On Sat, 29 May 2010 17:25:17 +0000, engrattny wrote:

That's a lot of questions for one post. I'll try to hit the

highlights.

As you already said, Access is designed to do control-level validation

in the myControl_BeforeUpdate event. Set Cancel=True if the validation

fails.

In most cases LostFocus should not be used for validation. It does not

have the benefits of BeforeUpdate. I write entire applications without

ever using the Focus events.

You should typically allow people to tab through your control without

entering anything. Later on, when they try to save the record, it will

fail because the control is bound to a Required field. Then they have

to come back to the control, enter some data, and

myControl_BeforeUpdate will fire.

To test for spaces in the value write this one-liner in

control_BeforeUpdate:

Cancel = (Instr(Me.myControl.Value, " ") > 0)

Or you could write a bit more verbosely:

dim blnHasSpaces as boolean

blnHasSpaces = (Instr(Me.myControl.Value, " ") > 0)

if blnHasSpaces then

Msgbox "Yo! No spaces.", vbCritical

Cancel = True

end if

If you wanted to intercept space characters and "eat" them, you can

write code in the myControl_KeyDown event:

If KeyCode = vbKeySpace And Shift = 0 Then

KeyCode = 0

End If

Personally I would find this highly irritating.

No, don't work unbound, especially as a beginner. Long story.

(of course you replace myObjectNames with yours)

-Tom.

Microsoft Access MVP

>

>

>I would like to insure traits about data in one control on a form. I would like the testing to occur on attempting to leave the control [beforeUpdate or Lostfocus?].

>

>In response to invalid data, I would like to be able to either void the entry and return to the control [I can do this with BeforeUpdate...] or make the correction to the data if appropriate.

>

>I use a simple textbox control to polish the programming skill set for my other, later, tasks. The table's field is a text string, Length=5. The traits I want:

>

> Left Justified; No Internal Spaces, All Caps, Uniqueness.

>

>I would like this testing to occur on existing records and on new records as the user leaves the control in either case.

>

>The user should be able to amend an existing record's value to another value, also unique. I have a seperate ID as the primary, linking key. This 5 letter code simplifies data entry. Using the hidden ID eliminates much of the Cascade issues on changes to the code.

>

>I can place tests in the beforeUpdate for this control -- using cancel = true and --.undo during the tests.

>

>I would prefer that the testing occur on leaving that particular control, rather than at the form level on updates

>

>At the moment, I have bound controls. But I could create unbound controls, and manually read/stuff back data to the dbs using recordsets [alot more programming, to my mind]. I'm not sure if this is easier [solving, too issues on leaving edits open on multiuser environments.

>

>In attempting to get this to work cleanly, I have tried: setting the table's traits to required and not; indexes w/ or w/o duplicates; variations on coding and placement into events [BeforeUpdate and LostFocus]

>

>I have these problems:

>

>1. I can't seem to get the test for non-blank to work, as a user can leave a control [without making a change/entry and dirtying the content]. I'm not sure if "required" in the table design solved this -- but if it does, I think I got a system error, rather than a user friendly myError routine.

>

>2. I can't figure out, after all the toying around with the code, if some of these tests have to be in the LostFocus [say, to allow for modification -- ie: stripping blanks]

>

>3. I can't seem to figure out how to use the input mask, as when I use it the data entry gets funky compared to without it.

>

>4. I am tempted to think I have to use recordsets and programmed Add/Save and etc., to get the control [unbound] to be restuffed with a "corrected" value. In this trivial case, the changes would be stripping of spaces, but generally, for more complex uses contemplated, I can't see another way -- and wish there were.

>

>5. Masking doesn't prevent spaces, I would have thought "LLLLL" would do it from the access help file's implications and wording.

>

>6. I am about to take delivery on Off2010, and wonder if the rules about Everything Will Change, if I install Acc2010 over 2007...

>

>So, I seek suggestions. Pointers to other URLs would be ok, too.

>

>On: Programming with Bound vs Unbound, in the context of my problems

>

>On: Programming utility and speed: Using unbound&Recordsets vs native handling by Access of bound controls

>

>On: The placement of testing: LostFocus vs BeforeUpdate or other events; Form level versus Control level

>

>--------------------------

>

>What follows is one iteration of the control's coding that I played around with in the beforeUpdate event.

>

>Private Sub txtAttorneyCode_BeforeUpdate(Cancel As Integer) Dim str As String If "" = Nz(Me!txtAttorneyCode, "") Then MsgBox "Value Required in this Field" Cancel = True Me!txtAttorneyCode.Undo Exit Sub End If ' check for internal spaces ' str = Trim(Me!txtAttorneyCode) If 0 <> InStr(Me!txtAttorneyCode, " ") Then 'spaces are not legal MsgBox "No spaces permitted in this field" Cancel = True Me!txtAttorneyCode.Undo Exit Sub End If 'Prevent Duplicate Values 'special case: THIS [existing] record's value is changed back to what it was ' when the user started changes, and then ends up just retyping the same If Me.txtAttorneyCode = Me.txtAttorneyCode.OldValue Then Cancel = False Exit Sub ' no changes to value, so its ok End If 'do lookup If (Not IsNull(DLookup("[fldAttorneyCode]", _ "tblAttorney", "[fldattorneycode] ='"

>_ & Me!txtAttorneyCode & "'"))) Then MsgBox "Code already exists -- choose another" Cancel = True Me!txtAttorneyCode.Undo Exit Sub End If End Sub

---

-Tom. Microsoft Access MVP

Answer 3

I have these problems:

1. I can't seem to get the test for non-blank to work, as a user can leave a control [without making a change/entry and dirtying the content].   I'm not sure if "required" in the table design solved this -- but if it does, I think I got a system error, rather than a user friendly myError routine.

Use the BeforeUpdate event of the FORM to check the contents of any control. Something like:

If Instr([txtControlName], " ") >= 1 Then

2.  I can't figure out, after all the toying around with the code, if some of these tests have to be in the LostFocus [say, to allow for modification -- ie: stripping blanks]

3.  I can't seem to figure out how to use the input mask, as when I use it the data entry gets funky compared to without it.

4.  I am tempted to think I have to use recordsets and programmed Add/Save and etc., to get the control [unbound] to be restuffed with a "corrected" value.  In this trivial case, the changes would be stripping of spaces, but generally, for more complex uses contemplated, I can't see another way -- and wish there were.

5.  Masking doesn't prevent spaces, I would have thought "LLLLL" would do it from the access help file's implications and wording.

6.  I am about to take delivery on Off2010, and wonder if the rules about Everything Will Change, if I install Acc2010 over 2007...

No, Access 2010 has the same behavior

So, I seek suggestions.  Pointers to other URLs would be ok, too.

I would just use the AfterUpdate event of the control to make everything upper case:

Me.txtControlName = UCase(Me.txtControlName)

On:  Programming with Bound vs Unbound, in the context of my problems

On:  Programming utility and speed: Using unbound&Recordsets vs native handling by Access of bound controls

On:  The placement of testing: LostFocus vs BeforeUpdate or other events; Form level versus Control level

In addition to the inline comments, the left justify requirement is a function of your design, and has nothing to do with data entry, and the check for duplicates (uniqueness) is usually accomplished using DLookup or or better yet, DCount in the control's BeforeUpdate event. In earlier days, using a recordset was significantly faster than a looup function, but now the lookup functions run at approximately the same speed.

--

Arvin Meyer, MCP, MVP

http://www.datastrat.com

http://www.accessmvp.com

http://www.mvps.org/access

Co-author: "Access 2010 Solutions", published by Wiley

---

Arvin Meyer MCP, MVP MS-Access