Distinction between using .text and .value in VBA Access

Question

I am passing the textbox1.text values into a query and sometimes into a string:

Dim combor1 As String
combor1 = comboReason1.Text

How do I know when I should put combor1 = comboReason1.Value?

Also, why do I need to set focus for a control to reference its property? That doesn't make sense to me.

Also, when I set combor4 = comboReason4.Value and the .value is null, then I get an error about invalid use of null.

Troublous

Answer 1

• ".text" gives you what is displayed on the screen
• ".value" gives you the underlying value

Both usually give the same result, except when the corresponding control is

1. a combobox or listbox control
2. the displayed value differs from the bound column

Example:

• id_Person is a combobox control in a form
• the rowsource is "SELECT id_Person, personName FROM Tbl_Person"
• column widths are "0cm;3cm"
• bound column is 1

In this situation:

• id_Person.text displays Tbl_Person.personName
• id_Person.value displays Tbl_Person.id_Person.

.text property is available only when the corresponding control has the focus.

.text is a string value, therefore it cannot be Null, while .value can be Null

EDIT: .text can only be called when the control has the focus, while .value can be called any time ...

Tensile

Note also that .text is limited to 2048 characters, while .value can have as many as 60k characters. – Ejaculation

Answer 2

You can use the Text property to set or return the text contained in a text box or in the text box portion of a combo box.

To set or return a control's Text property, the control must have the focus, or an error occurs. To move the focus to a control, you can use the SetFocus method or GoToControl action.

You can use the Value property to determine or specify if a control is selected, the selected value or option within the control, the text contained in a text box control, or the value of a custom property.

The Value property returns or sets a control's default property, which is the property that is assumed when you don't explicitly specify a property name. In the following example, because the default value of the text box is the value of the Text property, you can refer to its Text property setting without explicitly specifying the name of the property.

Forms!frmCustomers!txtLastName = "Smith"

Text Property Reference
http://msdn.microsoft.com/en-us/library/aa173453.aspx

Value Property Reference
http://msdn.microsoft.com/en-us/library/aa173476.aspx

Gaby

Answer 3

.text starts the field validation and causes an error if field validation is hurt. .value doesn't start the field validation, you may enter ANY value

Socratic

Answer 4

This thread and the answers herein explain the issue well. There are a couple of additional points I'd like to add, which I've found through experimentation:

The order of precedence of the properties is:

1. .ControlSource
2. .Value
3. .Text

From what I've been seeing in Access 2007, if .ControlSource is undefined when the form opens, .Value will be Null.

If you set the .ControlSource property to ="" (an empty string), that will cause the .Value property to default to that instead of Null.

You can set the .Value property to "" in the Form_Load event. But...I've been seeing some erratic operation there; it seems as if .Value sometimes changes from "" back to Null, and I haven't yet worked out the circumstances.

So it seems best to define .ControlSource to ="", either in Design View or in the Form_Load event. But be forewarned, that niblet is tricky because of the embedded double quotes, and it can be tricky to read.

Some ways to do it are:

• myTextbox.ControlSource = "=" & """"" (five double quotes in a row)
• myTextbox.ControlSource = "=" & Chr(34) & Chr(34)
• Etc, etc, there are many ways to do it...

Also, here's an extended tidbit. If you set the .TextFormat property to Rich Text, you can format the text in it with bold, italic, colors, etc. But be forewarned (again), beginning with Office 2007, the original Microsoft RTF format was decommissioned in favor of a "mini" version of HTML that only supports a few tags related to formatting fonts and paragraphs.

As an example, say you want the textbox to display the little ASCII checkbox character with the word "valid" in italics next to it, and make it all green. You can do it, but it all has to be in HTML, and it's not easy to read:

myTextbox.TextFormat = acTextFormatHTMLRichText
myTextbox.ControlSource = "=" & Chr(34) & "<font color=#80CA45><font face=Wingdings>" & _
Chr(254) & "</font>&nbsp;<font face=Calibri><i>Valid.</i></font></font>" & Chr(34)

Scarcely

Answer 5

If the text box is a ReadOnly control, the value property will not be used but if you set the text peoprty, the value will still be used in form data.

Sterol