Solved If Not [ComboBox] Is Null Statement in VBA (1 Viewer)

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
Hi, all
On the form I have combo box and text box. I want to do this: If Not [ComboBox] Is Null then, MsgBox: "Please, add in textbox"
When I have chose in ComboBox and try to add something else in the subform or close form before I add in the text box I need warning message MsgBox: "Please, add in textbox"
What the code should be and where to place it (BeforeUpdate ?)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
21,358
Yes, sounds like the BeforeUpdate event would be a good place to put it.
 

Micron

AWF VIP
Local time
Today, 11:21
Joined
Oct 20, 2018
Messages
3,476
The syntax would be If Not IsNull([ComboBox]) Then

IsNull is a VBA function, Is Null is for sql.
 

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
The syntax would be If Not IsNull([ComboBox]) Then
IsNull is a VBA function, Is Null is for sql.
Thanks for the tip and the code correction, Micron! :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:21
Joined
May 21, 2018
Messages
8,463
A common check is something like this or a len check.
if trim(me.combobox & "") = "" then

This checks for several "empty" conditions
Null
Empty string
Space/s

Empty strings and spaces are hard to generate but can happen, especially when importing from external source.
 

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
I added this code:
Code:
Private Sub cboCode_BeforeUpdate(Cancel As Integer)
If Not IsNull([cboCode]) Then
        MsgBox "Please enter the number of products!", vbInformation, "Atention!"
    End If
End Sub
It is works, but when I choose from cbo the message appears. I need when I forget to add in the text box and go to the subform or close form to show the message "Please enter the number of product"
 
Last edited:

Micron

AWF VIP
Local time
Today, 11:21
Joined
Oct 20, 2018
Messages
3,476
It is works, but when I choose from cbo the message appears.
That is how you coded it. Are you sure you don't want
If IsNull([ComboBox]) Then ?
MajP is correct, but I prefer something else that he posted a while back in another thread:
If Nz(someControl,"") = "" Then
AFAIK, that should deal with Null or empty string but not if there is a leading or trailing space in a value being checked I suppose. If that check is being performed on a combo, then I'd say the chance that a value chosen from the list would ever have a leading or trailing space is virtually nil.

Not sure, but it seems you're saying that you want the test to be applied against a textbox after the combo update. We seem to be providing code for checking the combo isn't Null, not the textbox. Which is it?
 

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
Not sure, but it seems you're saying that you want the test to be applied against a textbox after the combo update. We seem to be providing code for checking the combo isn't Null, not the textbox. Which is it?
My text box is Data Type - Number.
The point of what I need to do is to dont forget to enter a value in text box if I have chosen value in ComboBox.
Any ideas and suggestions please
 
Last edited:

Micron

AWF VIP
Local time
Today, 11:21
Joined
Oct 20, 2018
Messages
3,476
Then IMO the right place is the form before update event, like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Nz(Me.cboCode,"") = "" And Nz(Me.textboxName,"") = "" Then
  msgbox "If x is chosen, you must provide a value for y. "
  Cancel = True
  Exit Sub
End If

'do other stuff?

End Sub
or use the other test for null proposed by MajP.
EDIT - I forgot this was about testing a textbox, so I've modified the code. The test now is, if there is a combo value AND the textbox is Null (or contains an empty string) then the form update should not occur. Still, not sure I see the need to use Nz on a combo.
 
Last edited:

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
I am trying and trying but it doesn't work. I created а simple DB to explain what I need.
In the picture I have attached I want to make it so when I have a value entered in
What I want to do, shown in the photo, is remember to enter value into numberOfpsd if I got selected into psdCode.
The same thing what I want is in next subform with fields - TypeOfproduct and CountOfproduct
So if I go to new record, close form or someting else I need to show message "Please, enter value......"
 

Attachments

  • DB_v2.zip
    64.8 KB · Views: 101
  • 1.png
    1.png
    26.7 KB · Views: 159
Last edited:

bob fitz

AWF VIP
Local time
Today, 15:21
Joined
May 23, 2011
Messages
4,717
Not sure if this is what you want. In the db attached, I've put the following code in the BeforeUpdate event of Form2:

Code:
    If Not IsNull(Me.cbopsdCode) And IsNull(Me.numberOfpsd) Then
        MsgBox "Data entry required."
        Cancel = True
    End If
 

Attachments

  • DB_v2Bob01.zip
    33.3 KB · Views: 112

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
Not sure if this is what you want. In the db attached, I've put the following code in the BeforeUpdate event of Form2:
Thanks for you help bob fitz, but still does not works. The text box numberOfpsd is not require to be completed if I have chosen from combobox "psdCode"

 

bob fitz

AWF VIP
Local time
Today, 15:21
Joined
May 23, 2011
Messages
4,717
Delete the "Not" from the code I gave. Amended code would then be:
Code:
    If IsNull(Me.cbopsdCode) And IsNull(Me.numberOfpsd) Then
        MsgBox "Data entry required."
        Cancel = True
    End If
 

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
I apologize for the late reply but I was at work. Тhese two codes works, but they are only the half of the task.
If Not IsNull (Me.cbopsdCode)) Then
MsgBox "Please enter the number of products!", vbInformation, "Atention!"
Cancel = True
Exit Sub
End If
End Sub
Code:
 If Not IsNull([cbopsdCode]) Then
[CODE]If Not IsNull(Me.cbopsdCode) And IsNull(Me.numberOfpsd) Then
        MsgBox "Please enter the number of products!", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
End Sub

.......but I need to add " And IsNull(Me.numberOfpsd)". When add the second part the code not works! :oops:
I need these two conditions:
If Not IsNull(Me.cbopsdCode And IsNull(Me.numberOfpsd then
MsgBox "Please enter the number of products!", vbInformation, "Atention!"
Cancel = True
Exit Sub
End If
End Sub
 
Last edited:

Micron

AWF VIP
Local time
Today, 11:21
Joined
Oct 20, 2018
Messages
3,476
I need these two conditions:
If Not IsNull(Me.cbopsdCode And IsNull(Me.numberOfpsd then
Is that not what I provided in post 10?

If Not Nz(Me.cboCode,"") = "" And Nz(Me.textboxName,"") = "" Then means
if the first is NOT "" then it has a value. If the second IS "" then it does not have a value but it must so then message box.
 

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
Is that not what I provided in post 10?
Sorry, but it is not works. I uploaded a new one DB_v3 with the last code. Could you please, check it?
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not Nz(Me.cbopsdCode, "") = "" And Nz(Me.numberOfpsd, "") Then
        MsgBox "Please enter the number of products!", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
End Sub
 

Attachments

  • DB_v3.zip
    122.6 KB · Views: 109
Last edited:

Micron

AWF VIP
Local time
Today, 11:21
Joined
Oct 20, 2018
Messages
3,476
Look again - closely. What you have and what I posted are not the same thing. While you're staring at that, I'll modify your db code to see if it works when it's the same as what I wrote.
EDIT - first issue I see is that this will never work because you left the default value of 0 on number fields when you created the tables. So when you go to a new record, the textbox(es) will never be null. Remove that setting.
Also, my apologies for not seeing your image in post 11 before. I'm surprised that no one mentioned your form is on a sub form on a main form - so it is buried 2 levels deep. This changes things, as your code is on the wrong form. Still looking...
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 15:21
Joined
May 23, 2011
Messages
4,717
The code in your attached file DB_v3 does not seem to work but the code below does
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not Nz(Me.cbopsdCode, "") = "" And IsNull(Me.numberOfpsd) Then
        MsgBox "Please enter the number of products!", vbInformation, "Atention!"
        Cancel = True
        Exit Sub
    End If
End Sub
I can't explain why the test for a ZLS doesn't work with a numeric field but I'm sure other more knowledgeable members here will.
 

tihmir

Registered User.
Local time
Today, 08:21
Joined
May 1, 2018
Messages
257
Look again - closely. What you have and what I posted are not the same thing. While you're staring at that, I'll modify your db code to see if it works when it's the same as what I wrote.
My mistake. I forgot to add And Nz(Me.textboxName,"") = "" but it still doesn't work
 

Users who are viewing this thread

Top Bottom