Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Vba Syntax Question

Posted by: jabm May 22 2020, 08:16 PM

Hello all
I can't seem to find the correct phrase to 'Google' the syntax I am looking for.

How do I refer to a control (in my specific case a text box) in its own event code.

To be more specific - on my textbox's on click event I'd like to see if the value is null and if so then do something, if not do something else - so how do I refer to the textbox and it's value in its own on_click event?

Thank you


Posted by: jabm May 22 2020, 08:22 PM

Never mind -- I think I figured it out
just Me.textboxcontrolname.value

Access documentation is so spread out and the official documentation is so sparse....

Posted by: RJD May 22 2020, 08:37 PM

Ah, I see you figured it out ... I was going to post ...

Private Sub txtMyField_1_Click()
If IsNull(Me!txtMyField_1) Then
    MsgBox "This is Null", , "Indicator"
    MsgBox "This is Not Null", , "Indicator"
End If
End Sub

So I'll not post the demo I prepared.


Posted by: jabm May 22 2020, 09:05 PM

Thank you Joe -- took me a few tries to get it to show that it was null me.textbox.value = "" was not working and len(me.textbox.value) = 0 wasn't working both kept showing it was not null even though it was -- using the isnull(me.textbox.value) = true worked!

Posted by: RJD May 22 2020, 10:31 PM

Glad you got that solved. thumbup.gif

Continued success ...


Posted by: tina t May 23 2020, 12:26 AM

hi Julia, here's a little extra info about testing for Null that you might find useful, since you've indicated recently that you're working on expanding your base of knowledge. :)

as you discovered, the only way, AFAIK, to directly check for Null in VBA is by using the IsNull() function, as

If IsNull(Me!MyControlName) Then

and its' opposite

If Not IsNull(Me!MyControlName) Then

when you're wanting to rule out Nulls. you can't use

If Me!MyControlName Is Null Then

because VBA doesn't understand it, I think because Is Null is SQL. but hopefully somebody with more knowledge will read this and post an explanation and we'll both learn something. :)

you can't use

If Me!MyControlName = Null Then

because Null can't be equal to anything. you also can't use

If Me!MyControlName = "" Then

because "" is a zero-length string (ZLS), which is not the same as Null. as a sidebar here, zero-length strings are a pain in the patoot, because if you open a table and look at a field, it's impossible to tell if the field is Null, or contains a ZLS. often experienced developers will prevent the issue by setting the AllowZeroLengthString field property to False (No) in all text fields in all tables. but if you find yourself working with a database that does allow ZLS in one or more table fields, you can check for both Null and ZLS at the same time, in VBA, by using either of the following, as

If Len(Me!MyControlName & "") = 0 Then


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

if you're not familiar with Len() and Nz() functions, suggest you read up on them in Help. there may be other ways too, but those two i'm familiar with; though i don't know if there is any particular advantage to using one over the other.

another interesting (well, to me, anyway) point is that you can use Is Null (and Is Not Null) outside of VBA, probably just about anywhere you can write a criteria expression. in a query, of course

WHERE MyFieldName Is Null

works fine. you can also use the syntax in the criteria clause of domain aggregate functions, as

DLookup("MyFieldName", "MyTableOrQueryName", "MyFieldNameOrOtherFieldName Is Not Null")

in the True/False test in IIf() functions, as

IIf(MyFieldName Is Null,"return this","otherwise return this")

when i need to test for Null outside of VBA, i make a habit of using the Is Null syntax first. if it works, good. if not, i try using IsNull(). the reason i try Is Null first is because there's no point in calling a function if you don't need to.

and btw, here's another interesting (oh boy, to me) tidbit. if you try to manually enter trailing spaces (just hitting spacebar) in a Text field in a table, or in a form control bound to a table Text field, Access will let you do it - but then when you leave the field, the trailing spaces are dropped. but, you can import or append trailing spaces into a table field, and they will "stick". you can, AFAIK, import or append a string that is nothing but spaces (think spacebar again), though again can't do it through direct manual data entry.

who cares, right? well, if you open a table and look at a field that has a space value, " ", (or multiple space values which i can't represent here) you can't tell that the field is not null. same problem we have with ZLS. the only way i know to remove trailing spaces from data in an Append query is to wrap the field name in a trim function, as


LeftTrim() function will remove leading spaces, and the Trim() function will remove both leading and trailing spaces at the same time. if you're importing data using the Import Wizard or a stored import specification, i don't know any way to directly remove leading/trailing spaces. however, you can rule out values that are nothing but one or more spaces, with

If Len(Trim(Me!MyControlName & "")) = 0 Then


If Trim(Nz(Me!MyControlName, "")) = "" Then

okay, okay, that's probably TMI and then some! i just got on a roll. but it's some basic info that may ring a bell at some point when you need it, and at least give you an idea of what to search for, even if you don't remember details. :)


Posted by: Gustav May 23 2020, 03:01 AM

If the textbox can be expected to have focus, you can use generic code like this:

Private Sub YourTextbox_Click()

    Dim ThisTextbox As TextBox
    Set ThisTextbox = Me.ActiveControl
    MsgBox ThisTextbox.Value
End Sub

Posted by: jabm May 26 2020, 08:12 AM

Thank you both Tina and Gustav!
Definitely some great information. will be saving this thread

Posted by: cheekybuddha May 26 2020, 08:30 AM

One extra thing, .Value is the default property of a textbox (and other controls which have a .Value property eg combobox), and as such it is rarely required to write it out explicitly.

Private Sub txtMyField_1_Click()

  If Len(Me.txtMyField & vbNullString) = 0 Then
    MsgBox "txtMyField is either Null or contains a zero-length string"
  End If

End Sub



Posted by: jabm May 27 2020, 12:42 PM

Thank you! I'm learning so much from all of you on this board!

Posted by: tina t May 27 2020, 01:58 PM

you're welcome, Julia, we're all happy to help! :) tina

Posted by: t.heisler May 27 2020, 09:11 PM

Don't know why but I always check for empty strings too

If Not IsNull(Me.txtabc) and Me.txtabc <> "" then

End if

Posted by: tina t May 27 2020, 11:26 PM

yes, those two expressions together do the same thing as

If Len(Me!MyControlName & "") = 0 Then


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

it's not a bad habit to get into, checking for ZLS, because you never know when you might find yourself writing code to check a field where ZLS has not been prohibited. :)


Posted by: jabm May 28 2020, 02:09 PM

Great information! Thank you!

Posted by: hci_jon May 28 2020, 02:45 PM

I usually use the


or the opposite:

Most of the time Null and empty string mean the same thing in my logic tests, but not ALWAYS.

I tend to stay away from the & operator because I have heard that string concatenation is a lengthy operation in VBA if you are doing a loop over thousands of records for example.