Distinction between using .text and .value in VBA Access
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.
- ".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
- a combobox or listbox control
- 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 ...
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
.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
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:
.ControlSource
.Value
.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> <font face=Calibri><i>Valid.</i></font></font>" & Chr(34)
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.
精彩评论