# MS Access - simple problem on a form

**URL:** <https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078>\
**Category:** Factual Questions\
**Created:** [November 2, 2005, 2:32pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078 "2005-11-02T14:32:13Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 2:32pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/1 "2005-11-02T14:32:13Z")

</div>

I have a database that has 1 form. The form has 1 button and 1 text box.

When the button is clicked, it should check to see if the text box is empty and return the message “Enter Something” if it is.

If the text box is not empty it should return a message with the contents of the text box.

I’m using the following code for the button;

```auto

Private Sub Command2_Click()

Dim MyTemp As Variant
Dim MyText As Variant
Dim db As DAO.Database

Set db = CurrentDb()

MyTemp = Me![Text0]

If MyTemp = Null Then
MyText = "Enter Something"
Else
MyText = MyTemp
End If

MsgBox MyText

End Sub

```

However I’m getting an error if the text box is empty and I can’t figure out why.  
It doesn’t seem to be evaluating the “If” statement and assigning the value of “Enter Something” to MyText.

I get an “invalid use of null” error on the MsgBox line.

Any thoughts?  
I could e-mail anyone a copy if it would help.

Can anyone explain this to me?

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [November 2, 2005, 2:36pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/2 "2005-11-02T14:36:49Z")

</div>

> [@zoid](#):
>
> If MyTemp = Null Then  
> MyText = “Enter Something”  
> Else  
> MyText = MyTemp  
> End If
> 
> MsgBox MyText
> 
> End Sub  
> However I’m getting an error if the text box is empty and I can’t figure out why.  
> It doesn’t seem to be evaluating the “If” statement and assigning the value of “Enter Something” to MyText.
> 
> I get an “invalid use of null” error on the MsgBox line.
> 
> Any thoughts?  
> I could e-mail anyone a copy if it would help.
> 
> Can anyone explain this to me?

“if [whatever] = null then” does not work intuitively in VBA or any species of visual basic, in fact it does nothing at all useful. Null does not equal null.

Try “If MyTemp is Null Then” That should get you the results you want i believe.

---

<div class="post-metadata">

**Author:** ![ishamael69](https://avatars.discourse-cdn.com/v4/letter/i/b9e5f3/32.png) [@ishamael69](https://boards.straightdope.com/u/ishamael69)\
**Post date:** [November 2, 2005, 2:41pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/3 "2005-11-02T14:41:09Z")

</div>

If **chrisk** ’s advice doesn’t work, try something along the lines of

If Trim(MyTemp) = “” Then

Textboxes and the like don’t usually return NULL, just an empty string. If you use trim, you can also catch if a person has just entered a space. Of course, I don’t use Access a lot…

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 2:44pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/4 "2005-11-02T14:44:13Z")

</div>

> [@chrisk](#):
>
> “if [whatever] = null then” does not work intuitively in VBA or any species of visual basic, in fact it does nothing at all useful. Null does not equal null.
> 
> Try “If MyTemp is Null Then” That should get you the results you want i believe.

I tried that and now I get the error:

Run time error ‘424’ object required

Have you seen this before?

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [November 2, 2005, 2:46pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/5 "2005-11-02T14:46:30Z")

</div>

This works:

If IsNull(Me.Text0) Then  
MsgBox “empty”  
End If

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [November 2, 2005, 2:49pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/6 "2005-11-02T14:49:13Z")

</div>

> [@zoid](#):
>
> I tried that and now I get the error:
> 
> Run time error ‘424’ object required
> 
> Have you seen this before?

Yeah, hmmm… doesn’t like comparing a variant to null huh? Well, we could try ishmael’s advice, which seems good. I’d try rewriting the code something like this:

```auto

Private Sub Command2_Click()

Dim MyTemp As string
Dim MyText As string
Dim db As DAO.Database

Set db = CurrentDb()

MyTemp = Me![Text0].text

If trim(MyTemp) = "" Then
MyText = "Enter Something"
Else
MyText = MyTemp
End If

MsgBox MyText

End Sub

```

Let me know if you still get any null errors (or any error messages at all) with that. Using string variables instead of variants and calling the .text property explicitly will tighten up your code and may help catch out what the problem is here.

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 2:49pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/7 "2005-11-02T14:49:37Z")

</div>

> [@ishamael69](#):
>
> If **chrisk** ’s advice doesn’t work, try something along the lines of
> 
> If Trim(MyTemp) = “” Then
> 
> Textboxes and the like don’t usually return NULL, just an empty string. If you use trim, you can also catch if a person has just entered a space. Of course, I don’t use Access a lot…

OK - I tried that and I still get the original error.

☹

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [November 2, 2005, 2:55pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/8 "2005-11-02T14:55:02Z")

</div>

> [@chrisk](#):
>
> Yeah, hmmm… doesn’t like comparing a variant to null huh? Well, we could try ishmael’s advice, which seems good. I’d try rewriting the code something like this:  
> Let me know if you still get any null errors (or any error messages at all) with that. Using string variables instead of variants and calling the .text property explicitly will tighten up your code and may help catch out what the problem is here.

I just tried this myself and it looks like we’re missing a step. Before checking the textbox value, add in a line saying “Me![Text0].setfocus”

Access doesn’t like reading controls that don’t have the focus. That might be where the nulls were coming from.

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 2:56pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/9 "2005-11-02T14:56:06Z")

</div>

> [@chrisk](#):
>
> Yeah, hmmm… doesn’t like comparing a variant to null huh? Well, we could try ishmael’s advice, which seems good. I’d try rewriting the code something like this:
> 
> ```auto
> 
> Private Sub Command2_Click()
> 
> Dim MyTemp As string
> Dim MyText As string
> Dim db As DAO.Database
> 
> Set db = CurrentDb()
> 
> MyTemp = Me![Text0].text
> 
> If trim(MyTemp) = "" Then
> MyText = "Enter Something"
> Else
> MyText = MyTemp
> End If
> 
> MsgBox MyText
> 
> End Sub
> 
> ```
> 
> Let me know if you still get any null errors (or any error messages at all) with that. Using string variables instead of variants and calling the .text property explicitly will tighten up your code and may help catch out what the problem is here.

Now I get:

Run-time error ‘2185’:  
You can’t reference a property or method for a control unless the control has the focus.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [November 2, 2005, 2:57pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/10 "2005-11-02T14:57:23Z")

</div>

Just use **if IsNull(Me.Text0) then…** fercryinoutloud!

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [November 2, 2005, 2:59pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/11 "2005-11-02T14:59:55Z")

</div>

> [@Mangetout](#):
>
> Just use **if IsNull(Me.Text0) then…** fercryinoutloud!

Are you sure that will actually accomplish the goal?? (Not just testing for null… actually reading the textbox value.)

Zoid, if you haven’t noticed yet, post 8 contains the solution to the error message in post 9.

---

<div class="post-metadata">

**Author:** ![BadBadger](https://avatars.discourse-cdn.com/v4/letter/b/22d042/32.png) [@BadBadger](https://boards.straightdope.com/u/BadBadger)\
**Post date:** [November 2, 2005, 3:01pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/12 "2005-11-02T15:01:11Z")

</div>

Can’t you just :

Private Sub Command2\_Click()

If IsNull(Me.Text0) Then  
MsgBox “Empty”  
Else  
MsgBox Me.Text0.Value  
End If  
End Sub

Works for me…

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 3:02pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/13 "2005-11-02T15:02:44Z")

</div>

> [@Mangetout](#):
>
> Just use **if IsNull(Me.Text0) then…** fercryinoutloud!

LOL - Sorry missed your first post 😃

Works just fine!  
Thanks!

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 3:04pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/14 "2005-11-02T15:04:08Z")

</div>

> [@chrisk](#):
>
> Are you sure that will actually accomplish the goal?? (Not just testing for null… actually reading the textbox value.)
> 
> Zoid, if you haven’t noticed yet, post 8 contains the solution to the error message in post 9.

Sorry - I’m a little slow today - I just saw that.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [November 2, 2005, 3:04pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/15 "2005-11-02T15:04:12Z")

</div>

> [@chrisk](#):
>
> Are you sure that will actually accomplish the goal?? (Not just testing for null… actually reading the textbox value.)
> 
> Zoid, if you haven’t noticed yet, post 8 contains the solution to the error message in post 9.

Well, it’s _supposed to work_, also, I tested it and it did work, if that’s what you mean…

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [November 2, 2005, 3:05pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/16 "2005-11-02T15:05:33Z")

</div>

> [@BadBadger](#):
>
> Can’t you just :
> 
> Private Sub Command2\_Click()
> 
> If IsNull(Me.Text0) Then  
> MsgBox “Empty”  
> Else  
> MsgBox Me.Text0.Value  
> End If  
> End Sub
> 
> Works for me…

Hmmm… yes, that works too. Strange.

Despite what the error message 2185 says, you can access the value property for a textbox that doesn’t have the focus… but not the text property. (sigh.)

This is why I like VB6 better, hehe.

Glad you got it working zoid. Hope I didn’t just confuse the issue.

---

<div class="post-metadata">

**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\
**Post date:** [November 2, 2005, 3:07pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/17 "2005-11-02T15:07:38Z")

</div>

> [@Mangetout](#):
>
> Well, it’s _supposed to work_, also, I tested it and it did work, if that’s what you mean…

Sorry, guess I was being a little snippy. There are multiple ways to accomplish any goal, and I don’t know everything…  
[sub]yet![/sub] 😃

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [November 2, 2005, 3:08pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/18 "2005-11-02T15:08:11Z")

</div>

> [@chrisk](#):
>
> you can access the value property for a textbox that doesn’t have the focus… but not the text property. (sigh.)

I’ve seen that before; Access is a bit funny about that, I’m not sure why, but I’d love to know, if there’s anyone watching that knows the answer.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [November 2, 2005, 3:09pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/19 "2005-11-02T15:09:13Z")

</div>

> [@chrisk](#):
>
> Sorry, guess I was being a little snippy.

Sorry, I didn’t mean the reply to read quite as curtly as it turned out…

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [November 2, 2005, 3:18pm UTC](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078/20 "2005-11-02T15:18:27Z")

</div>

…and zoid instigates an Access THROWDOWN! 😃  
Seroiusly, thank you ALL for your help and let me just say yet once again…

There is **NOTHING** you guys don’t know!

[Next page](https://boards.straightdope.com/t/ms-access-simple-problem-on-a-form/329078.md?page=2)