CODEWITHSUNDEEP
ms-accesstextboxvbaauto-update
Paolo Bernasconi
Paolo Bernasconi

Reputation: 2030

Update textbox during typing

In Access, I have a form in which there are three textboxes. I am trying to update a textbox called tbxCombinedName with a combination of both:

My question is: what textbox property do I use, so that as I am typing text in tbxLastName, the CombinedName textbox is updated immediately and thereafter saved in the table Contacts.

On Microsoft's website, I have found that the step processes when typing in a textbox are as follows:

KeyDown → KeyPress → BeforeInsert → Change → KeyUp

I've tried using the OnChange and OnKeyDown properties, but to no avail. Which property, combined with what code, will allow the update-as-you-type action to work?

This is what I wrote earlier, which didn't work:

Private Sub tbxLName_change()

Dim lastName As String
Dim nameCode As String

lastName = tbxLName.Value
Debug.Print lastName
nameCode = tbxNameCode.Value
nameCode = lastName
Debug.Print nameCode

End Sub

Thanks for all your help in advance.

Upvotes: 5

Views: 16839

Answers (2)

ray
ray

Reputation: 8699

Just a couple of notes:

You may want to go with KeyPress because it provides the ability to change or negate the key the user pushed.

In the example below, only letters are allowed and lower case letters are upper cased:

Private Sub tbxLName_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
        Const ASCII_LOWER_RANGE = 65
        Const ASCII_UPPER_RANGE = 122
        Const ASCII_LOWER_A = 97
        Const ASCII_LOWER_Z = 122
        Const UPPER_MODIFIER = -32
        Const ASCII_CANCEL_CODE = 0

        Select Case KeyAscii
            Case ASCII_LOWER_RANGE To ASCII_UPPER_RANGE
                If KeyAscii >= ASCII_LOWER_A And KeyAscii <= ASCII_LOWER_Z Then
                    KeyAscii = KeyAscii + UPPER_MODIFIER
                End If
            Case Else
                KeyAscii = ASCII_CANCEL_CODE  'Cancel Key Press
        End Select
End Sub

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

This is one of the few cases where you should refer to the .text property.

In the Change event:

lastName = tbxLName.Text

The .text property is only available when a control has focus and it refers to the visible contents of the control.

However, this is a database and the general rule is that you no not store calculated fields. The full name can easily be obtained from a query.

Upvotes: 9

Related Questions