Solved if field is 0 or empty then (1 Viewer)

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
Hello,
Not sure why this is not functioning correctly.
I have a check box, LblScheduled_Click(), when I click it (True) then it checks 2 conditions before moving on to the next piece of code.
The first one works no problem, the second one elseif, is not producing the txtmessage, not highlighting red etc. but it does set the focus.
1638412761063.png

Thoughts? Part Code below.

Code:
Private Sub LblScheduled_Click()
    Dim txtmessage As String
    txtmessage = ""

        If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
            txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)
            Me.TxtCustomerPreferredDate.BackColor = vbRed
            Me.TxtCustomerPreferredDate.ForeColor = vbWhite
            Me.TxtCustomerPreferredDate.SetFocus
        ElseIf IsNull(Nz(TxtSchedMins)) = 0 Then ' Check Minintes entered
            txtmessage = "Enter Total of SetOut and Scheduling minutes." & vbCrLf & txtmessage
            Me.TxtSchedMins.BackColor = vbRed
            Me.TxtSchedMins.BackColor = vbWhite
            Me.TxtSchedMins.SetFocus
        Else
            Me.TxtCustomerPreferredDate.BackColor = vbWhite
            Me.TxtCustomerPreferredDate.ForeColor = vbBlack
            Me.TxtSchedMins.BackColor = vbWhite
            Me.TxtSchedMins.BackColor = vbBlack
            
            If ChkSetOut_Complete Then
                If ChkScheduled = True Then
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
see the textbox in design view and your need to set the Back Style to Normal
for the colors to appear.
 

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
see the textbox in design view and your need to set the Back Style to Normal
for the colors to appear.
Yes the colours etc are fine. I dont think its picking up the 0 maybe?
If I comment out the TxtSchedMins of the code everything moves forward and functions.

also even if i put a number in there, still no action.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:55
Joined
May 21, 2018
Messages
8,463
To check if a date is entered use
if not isdate(Me.TxtCustomerPreferredDate) Then

nz function without a second argument returns a zero length string not the value 0
nz(someTextbox) returns "" if sometextbox is empty
nz(someTextbox,0) returns 0

so the following makes no sense
ElseIf IsNull(Nz(TxtSchedMins)) = 0
try
elseif nz(txtSchedMins,0) = 0
 

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
To check if a date is entered use
if not isdate(Me.TxtCustomerPreferredDate) Then

nz function without a second argument returns a zero length string not the value 0
nz(someTextbox) returns "" if sometextbox is empty
nz(someTextbox,0) returns 0

so the following makes no sense
ElseIf IsNull(Nz(TxtSchedMins)) = 0
try
elseif nz(txtSchedMins,0) = 0
G'day mate,

I adjusted the code to
Code:
Private Sub LblScheduled_Click()
    Dim txtmessage As String
    txtmessage = ""

        If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
            txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)
            Me.TxtCustomerPreferredDate.BackColor = vbRed
            Me.TxtCustomerPreferredDate.ForeColor = vbWhite
            Me.TxtCustomerPreferredDate.SetFocus
        ElseIf Nz(TxtSchedMins, 0) = 0 Then ' Check Minintes entered
            txtmessage = "Enter Total of SetOut and Scheduling minutes." & vbCrLf & txtmessage
            Me.TxtSchedMins.BackColor = vbRed
            Me.TxtSchedMins.BackColor = vbWhite
            Me.TxtSchedMins.SetFocus
        Else
            Me.TxtCustomerPreferredDate.BackColor = vbWhite
            Me.TxtCustomerPreferredDate.ForeColor = vbBlack
            Me.TxtSchedMins.BackColor = vbWhite
            Me.TxtSchedMins.BackColor = vbBlack
            
            If ChkSetOut_Complete Then
                If ChkScheduled = True Then

The following happens:
if TxtSchedMins is 0
the focus changes to TxtSchedMins, however not red back ground, not white foreground and NO text message.
If I put in a number, say 10mins,
the rest of my code engages ticks my box and adds my date. However, the focus sets back to TxtSchedMins and the whole back ground is black.
1638415143228.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
check your code again, you are only changing the BackColor (not the Forecolor, also on the Else statement).
 

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
check your code again, you are only changing the BackColor (not the Forecolor, also on the Else statement).
Im a knob, thanks.
ok the back and forecolors working.

not getting my text message when empty or 0
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
can you post the whole code?
 

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
can you post the whole code?
sure..

Code:
Private Sub LblScheduled_Click()
    Dim txtmessage As String
    txtmessage = ""

        If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
            txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)
            Me.TxtCustomerPreferredDate.BackColor = vbRed
            Me.TxtCustomerPreferredDate.ForeColor = vbWhite
            Me.TxtCustomerPreferredDate.SetFocus
        ElseIf Nz(TxtSchedMins, 0) = 0 Then ' Check Minintes entered
            txtmessage = "Enter Total of SetOut and Scheduling minutes." & vbCrLf & txtmessage
            Me.TxtSchedMins.BackColor = vbRed
            Me.TxtSchedMins.ForeColor = vbWhite
            Me.TxtSchedMins.SetFocus
        Else
            Me.TxtCustomerPreferredDate.BackColor = vbWhite
            Me.TxtCustomerPreferredDate.ForeColor = vbBlack
            Me.TxtSchedMins.BackColor = vbWhite
            Me.TxtSchedMins.ForeColor = vbBlack
            
            If ChkSetOut_Complete Then
                If ChkScheduled = True Then
            
                Dim iResponse As String
                'Ask if they are sure they want to change it and store their response in iResponse.
                iResponse = MsgBox("Do you wish to change the status of this job for Scheduling?  ", vbYesNo + vbExclamation + vbApplicationModal + vbDefaultButton1, "Change Scheduling.")
                
                Select Case iResponse
                    Case vbYes: 'They answered Yes
                        Dim strPassword As String
                        strPassword = InputBox("Please enter the admin password", "Change Status of Scheduling") 'get password with input box
                        If strPassword = "Haus" Then
                            ChkScheduled = False 'sets the check box to false.
                            Form_ReworkF.TxtScheduled = ""
                            LblScheduled.Caption = ""
                            Me!CboJobTypeID.Enabled = True
                            Me!TxtCustomerPreferredDate.Enabled = True
                        Else
                            Call MsgBox("The password you entered is incorrect.  ", vbOKOnly + vbCritical + vbApplicationModal + vbDefaultButton1, "Incorrect Password")
                        End If
                    Case vbNo: 'If they select no to changing it it does nothing.
                End Select
            Else ' it is false
                ChkScheduled = True
                Form_ReworkF.TxtScheduled = Date
                LblScheduled.Caption = Chr(252)
                Me!CboJobTypeID.Enabled = False
                Me!TxtCustomerPreferredDate.Enabled = False
            End If
            Else
                iResponse = MsgBox("You cannot schedule a job until SetOut is Complete.  ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Cannot Schedule.")
            End If
        End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub LblScheduled_Click()
    Dim txtmessage As String
    txtmessage = ""

    If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
        txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)
        Me.TxtCustomerPreferredDate.BackColor = vbRed
        Me.TxtCustomerPreferredDate.ForeColor = vbWhite
        Me.TxtCustomerPreferredDate.SetFocus
    ElseIf Nz(TxtSchedMins, 0) = 0 Then ' Check Minintes entered
        txtmessage = "Enter Total of SetOut and Scheduling minutes." & vbCrLf & txtmessage
        Me.TxtSchedMins.BackColor = vbRed
        Me.TxtSchedMins.ForeColor = vbWhite
        Me.TxtSchedMins.SetFocus
    Else
        Me.TxtCustomerPreferredDate.BackColor = vbWhite
        Me.TxtCustomerPreferredDate.ForeColor = vbBlack
        Me.TxtSchedMins.BackColor = vbWhite
        Me.TxtSchedMins.ForeColor = vbBlack

        If ChkSetOut_Complete Then
            If ChkScheduled = True Then

                Dim iResponse As String
                'Ask if they are sure they want to change it and store their response in iResponse.
                iResponse = MsgBox("Do you wish to change the status of this job for Scheduling?  ", vbYesNo + vbExclamation + vbApplicationModal + vbDefaultButton1, "Change Scheduling.")

                Select Case iResponse
                    Case vbYes: 'They answered Yes
                        Dim strPassword As String
                        strPassword = InputBox("Please enter the admin password", "Change Status of Scheduling") 'get password with input box
                        If strPassword = "Haus" Then
                            ChkScheduled = False 'sets the check box to false.
                            Form_ReworkF.TxtScheduled = ""
                            LblScheduled.Caption = ""
                            Me!CboJobTypeID.Enabled = True
                            Me!TxtCustomerPreferredDate.Enabled = True
                        Else
                            Call MsgBox("The password you entered is incorrect.  ", vbOKOnly + vbCritical + vbApplicationModal + vbDefaultButton1, "Incorrect Password")
                        End If
                    Case vbNo: 'If they select no to changing it it does nothing.
                End Select
            Else ' it is false
                ChkScheduled = True
                Form_ReworkF.TxtScheduled = Date
                LblScheduled.Caption = Chr(252)
                Me!CboJobTypeID.Enabled = False
                Me!TxtCustomerPreferredDate.Enabled = False
            End If
        Else
            iResponse = MsgBox("You cannot schedule a job until SetOut is Complete.  ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Cannot Schedule.")
        End If
    End If
    'arnelgp
    If Len(txtmessage) Then
        MsgBox txtmessage, vbInformation + vbOKOnly, "Scheduling"
    End If
End Sub
 

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
Code:
Private Sub LblScheduled_Click()
    Dim txtmessage As String
    txtmessage = ""

    If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
        txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)
        Me.TxtCustomerPreferredDate.BackColor = vbRed
        Me.TxtCustomerPreferredDate.ForeColor = vbWhite
        Me.TxtCustomerPreferredDate.SetFocus
    ElseIf Nz(TxtSchedMins, 0) = 0 Then ' Check Minintes entered
        txtmessage = "Enter Total of SetOut and Scheduling minutes." & vbCrLf & txtmessage
        Me.TxtSchedMins.BackColor = vbRed
        Me.TxtSchedMins.ForeColor = vbWhite
        Me.TxtSchedMins.SetFocus
    Else
        Me.TxtCustomerPreferredDate.BackColor = vbWhite
        Me.TxtCustomerPreferredDate.ForeColor = vbBlack
        Me.TxtSchedMins.BackColor = vbWhite
        Me.TxtSchedMins.ForeColor = vbBlack

        If ChkSetOut_Complete Then
            If ChkScheduled = True Then

                Dim iResponse As String
                'Ask if they are sure they want to change it and store their response in iResponse.
                iResponse = MsgBox("Do you wish to change the status of this job for Scheduling?  ", vbYesNo + vbExclamation + vbApplicationModal + vbDefaultButton1, "Change Scheduling.")

                Select Case iResponse
                    Case vbYes: 'They answered Yes
                        Dim strPassword As String
                        strPassword = InputBox("Please enter the admin password", "Change Status of Scheduling") 'get password with input box
                        If strPassword = "Haus" Then
                            ChkScheduled = False 'sets the check box to false.
                            Form_ReworkF.TxtScheduled = ""
                            LblScheduled.Caption = ""
                            Me!CboJobTypeID.Enabled = True
                            Me!TxtCustomerPreferredDate.Enabled = True
                        Else
                            Call MsgBox("The password you entered is incorrect.  ", vbOKOnly + vbCritical + vbApplicationModal + vbDefaultButton1, "Incorrect Password")
                        End If
                    Case vbNo: 'If they select no to changing it it does nothing.
                End Select
            Else ' it is false
                ChkScheduled = True
                Form_ReworkF.TxtScheduled = Date
                LblScheduled.Caption = Chr(252)
                Me!CboJobTypeID.Enabled = False
                Me!TxtCustomerPreferredDate.Enabled = False
            End If
        Else
            iResponse = MsgBox("You cannot schedule a job until SetOut is Complete.  ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Cannot Schedule.")
        End If
    End If
    'arnelgp
    If Len(txtmessage) Then
        MsgBox txtmessage, vbInformation + vbOKOnly, "Scheduling"
    End If
End Sub
hmm
ok sort of working.
when I delete the date I get my message box and the field is highlighted. all good.
But then I get this:
1638421834468.png


when shed mins is 0 or empty i get my message box. all good.
and then ticks get done there after.

not sure what the additional message box is?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
i think it is here:



If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)


so change it to:



If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
txtmessage = "Please enter Delivery Date."
 

Chief

Registered User.
Local time
Today, 00:55
Joined
Feb 22, 2012
Messages
156
i think it is here:



If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
txtmessage = MsgBox("Please enter Delivery Date." & vbCrLf & txtmessage)


so change it to:



If Len(Nz(Me.TxtCustomerPreferredDate)) = 0 Then ' Check for Delivery Date
txtmessage = "Please enter Delivery Date."
ok cool, thank you.

working!

I've done similar code on previous stuff that hasn't got your additional code.
Can you please educate me in what this does? :)

'arnelgp
If Len(txtmessage) Then
MsgBox txtmessage, vbInformation + vbOKOnly, "Scheduling"
End If
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
I've done similar code on previous stuff that hasn't got your additional code.
maybe each If..Else.. code has it's own Msgbox.
 

Users who are viewing this thread

Top Bottom