Assigning a Null value to a Control (1 Viewer)

grenee · Dec 10, 2017

Good day All,

I have 4 unbounded controls which I would like to empty after using. However assigning Null to them to achieve the emptying objective results in an error message. It was working very well before with exactly the same code but all of a sudden it gives an error message.

The error message is "you can't assign a value to this object"

Can anyone assist with this situation.

Here is my code:

PHP:

Public Sub  ClearPaymentControls()   Forms![Invoice Payments]![PayAmt] = Null   Forms![Invoice Payments]![PayDate] = Null   Forms![Invoice Payments]![PayType] = Null   Forms![Invoice Payments]![payRef] = Null   End Sub

jleach · Dec 10, 2017

Try using the explicit .Value property of the control:

Code:

Forms![Invoice Payments]![PayAmt].Value = null

When you use [PayAmt] without specifying the .Value property, you're actually referring to the control object itself (and all of it's properties, such as .Value, Top, Left etc). Access usually helps out by figuring that we really meant to use the .Value property, but can't always. Better to be explicit.

Cheers

arnelgp · Dec 10, 2017

find out among the controls whether there is an expression on its Control source.

The_Doc_Man · Dec 10, 2017

It was working very well before with exactly the same code but all of a sudden it gives an error message.

This essentially does not happen... without a cause. Code doesn't just "break" out of nowhere.

Therefore, what was the last change you made to your form's code or configuration before it stopped working?

isladogs · Dec 10, 2017

@grenee

Just to say your code appeared on 1 line because you used the PHP button
The code button is the # button to the left of that one

Solo712 · Dec 10, 2017

jleach said:
Try using the explicit .Value property of the control:

Code:

Forms![Invoice Payments]![PayAmt].Value = null

When you use [PayAmt] without specifying the .Value property, you're actually referring to the control object itself (and all of it's properties, such as .Value, Top, Left etc). Access usually helps out by figuring that we really meant to use the .Value property, but can't always. Better to be explicit.

Cheers

FYI, jleach: this is incorrect! You never need to use the .value property when using the "bang" notation to an object. Read here:

http://bytecomb.com/the-bang-exclamation-operator-in-vba/

Best,
Jiri

Solo712 · Dec 10, 2017

grenee said:
Good day All,

I have 4 unbounded controls which I would like to empty after using. However assigning Null to them to achieve the emptying objective results in an error message. It was working very well before with exactly the same code but all of a sudden it gives an error message.

The error message is "you can't assign a value to this object"

Can anyone assist with this situation.

Here is my code:

PHP:

Public Sub ClearPaymentControls() Forms![Invoice Payments]![PayAmt] = Null Forms![Invoice Payments]![PayDate] = Null Forms![Invoice Payments]![PayType] = Null Forms![Invoice Payments]![payRef] = Null End Sub

Is the Form [Invoice Payments] loaded?

Best,
Jiri

jleach · Dec 10, 2017

Solo712 said:
You never need to use the .value property when using the "bang" notation to an object.

Not quite sure I'd agree with that... you end up with a reference to an object whether you go about it from the Expression Service or via the VBA syntax. Both paths lead to the same road. In this case, the Bang operator is being applied to the left operand, accessing the default method (the controls collection) and returning the object on the right (the control). I don't believe the end result (the control) is any different, thus I'd still assert it's a better practice to explicitly note the value.

I did have a quick read through the link, but didn't see anything to change my mind. That said, I'm too lazy to prove it myself, so I'll leave it as just my opinion (personally, I tend to avoid the bang operator (any ES statements for that matter) anyway).

Cheers,

The_Doc_Man · Dec 11, 2017

Not that it makes a HUGE difference, but there ARE times when you have to use the .Value of a control. We've had posts on the subject.

Since your controls are unbound, you CAN assign a value to them without running afoul of the underlying field. There isn't one, of course.

Note, however, that there are a couple of oddball "gotcha" cases. For example, suppose that you have a listbox with multiple possible values AND you have the multi-value option. As it happens, that listbox doesn't have a .Value property because .Value is always single-valued.

I found a reference from SpikePL that .Value is required sometimes when playing with Outlook EMail as an application object. However, this is actually a hard topic to find via search because of the "." in the 1st position. The search feature of this forum won't let me search for ".Value" - well, actually, it ALLOWS the search but returns no records, which is impossible because I know I've made posts on this subject myself.

jleach · Dec 11, 2017

I actually just ran a quick test on this. Create a form with a textbox (let's be creative and call it Text0) and a button. Put some code in the button click:

Code:

Private Sub Command2_Click()

    If TypeOf Me.Text0 Is Access.Control Then Debug.Print "VBA says it's a control"
    
    If TypeOf Me!Text0 Is Access.Control Then Debug.Print "Bang (ES) says it's a control"
    
    If TypeOf Me!Text0.Value Is Access.Control Then Debug.Print "You should never see this..."

End Sub

Observe the first two outputs, but not the third. Therefore: the result returned by the Bang operator is indeed the control itself, and not the value explicitly (and therefore, one should use the .Value property explicitly in any case where they would when accessing the control via VBA: either method of reference makes no difference to the end result).

Cheers

Galaxiom · Dec 11, 2017

jleach said:
Observe the first two outputs, but not the third. Therefore: the result returned by the Bang operator is indeed the control itself, and not the value explicitly (and therefore, one should use the .Value property explicitly in any case where they would when accessing the control via VBA: either method of reference makes no difference to the end result).

TypeOf() specifies the argument as a Control so the object is provided as the parameter. If a scalar is expected then the Value property will be returned. This is standard behaviour in VBA.

To prove your claim, you would need to show a case where the object can be returned when the expression expects a scalar as the parameter.

Solo712 · Dec 11, 2017

The_Doc_Man said:
Not that it makes a HUGE difference, but there ARE times when you have to use the .Value of a control. We've had posts on the subject.

Since your controls are unbound, you CAN assign a value to them without running afoul of the underlying field. There isn't one, of course.

Note, however, that there are a couple of oddball "gotcha" cases. For example, suppose that you have a listbox with multiple possible values AND you have the multi-value option. As it happens, that listbox doesn't have a .Value property because .Value is always single-valued.

I found a reference from SpikePL that .Value is required sometimes when playing with Outlook EMail as an application object. However, this is actually a hard topic to find via search because of the "." in the 1st position. The search feature of this forum won't let me search for ".Value" - well, actually, it ALLOWS the search but returns no records, which is impossible because I know I've made posts on this subject myself.

Doc, this is arguing past my point. Can you give me one example where, a) the bang used with a form does not signify a member of its default collection, i.e. the form controls, and b) where the notation Form!Control does not return the same thing as Form!Control.Value ? Much obliged.

Best,
Jiri

Assigning a Null value to a Control (1 Viewer)

grenee

Registered User.

jleach

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

Solo712

Registered User.

Solo712

Registered User.

jleach

Registered User.

The_Doc_Man

Immoderate Moderator

jleach

Registered User.

Galaxiom

Super Moderator

Solo712

Registered User.

Similar threads

Users who are viewing this thread