UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Syntax Question, Access 2016    
 
   
jabm
post May 22 2020, 08:16 PM
Post#1



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


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

Julia
Go to the top of the page
 
jabm
post May 22 2020, 08:22 PM
Post#2



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


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....

Go to the top of the page
 
RJD
post May 22 2020, 08:37 PM
Post#3


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


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

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

So I'll not post the demo I prepared.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jabm
post May 22 2020, 09:05 PM
Post#4



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


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!
Go to the top of the page
 
RJD
post May 22 2020, 10:31 PM
Post#5


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Glad you got that solved. thumbup.gif

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post May 23 2020, 12:26 AM
Post#6



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


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

or

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

RightTrim(MyFieldName)

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

or

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. :)

hth
tina
This post has been edited by tina t: May 23 2020, 01:02 AM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Gustav
post May 23 2020, 03:01 AM
Post#7


UtterAccess VIP
Posts: 2,257
Joined: 21-February 07
From: Copenhagen


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

CODE
Private Sub YourTextbox_Click()

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


--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
jabm
post May 26 2020, 08:12 AM
Post#8



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


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

Go to the top of the page
 
cheekybuddha
post May 26 2020, 08:30 AM
Post#9


UtterAccess Moderator
Posts: 12,992
Joined: 6-December 03
From: Telegraph Hill


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.
CODE
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


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
jabm
post May 27 2020, 12:42 PM
Post#10



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thank you! I'm learning so much from all of you on this board!
Go to the top of the page
 
tina t
post May 27 2020, 01:58 PM
Post#11



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
t.heisler
post May 27 2020, 09:11 PM
Post#12



Posts: 20
Joined: 28-January 19



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

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

End if

Go to the top of the page
 
tina t
post May 27 2020, 11:26 PM
Post#13



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


yes, those two expressions together do the same thing as

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

or

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. :)

hth
tina
This post has been edited by tina t: May 27 2020, 11:26 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
jabm
post May 28 2020, 02:09 PM
Post#14



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Great information! Thank you!
Go to the top of the page
 
hci_jon
post May 28 2020, 02:45 PM
Post#15



Posts: 5
Joined: 3-December 19



I usually use the
CODE
Nz([Field],"")=""

or the opposite:
CODE
Nz([Field],"")<>""


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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 06:53 AM