Textbox Condition and Validations

[AnhCVL via AccessMonster.com]

Hi all,

any suggestion on how to make this work:

Private Sub NEXT_Click()
On Error GoTo Exit_NEXT_Click:

If Nz(iSTATUS, "") = "" Then

MsgBox "Select a Status to continue", vbOKOnly, "Missing Data"
DoCmd.CancelEvent
iSTATUS.SetFocus

If Nz(iSTATUS, "") = "yes" And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
DoCmd.CancelEvent
sDATE.SetFocus
End If


Else
DoCmd.GoToRecord , , acNext

End If

Exit_NEXT_Click:
Exit Sub
End Sub

I have also tried this on the inner if loops:

If Nz(iSTATUS, "yes") = "yes" And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
DoCmd.CancelEvent
sDATE.SetFocus
End If

If iSTATUS.text = "yes" And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
DoCmd.CancelEvent
sDATE.SetFocus
End If

If iSTATUS.Value = "yes" And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
DoCmd.CancelEvent
sDATE.SetFocus
End If

"iStatus" is a combo box with dropdown selections with "yes" and "NO", and
"sDate" is a TexBox on the same form.

when I run the codes, the outer If loop work but it ignored the inner If loop
regardless of the value of "iStatus".

I am trying to get it to prompt user to enter a Date onto "sDate" field if
"iStatus" selection is "Yes" but the Textbox for "sDate" is empty when the
command button "NEXT" being clicked.

Thanks.
Mark

 

[Beetle]

Better to use the *form's* Before Update event if you want to ensure
values get entered. It has a built in Cancel argument;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Nz(iSTATUS, "") = "" Then
MsgBox "Select a Status to continue", vbOKOnly, "Missing Data"
Cancel = True
iSTATUS.SetFocus
ElseIf Me!iStatus = "yes" And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
Cancel = True
sDATE.SetFocus
End If

End Sub

This may, however, cause the procedure for your NEXT button to error
out and throw up an additional message box. You can trap for that
particular error in the code for your NEXT click event;

Private Sub NEXT_Click()
On Error GoTo Err_NEXT_Click

DoCmd.GoToRecord , , acNext

Exit_NEXT_Click:
Exit Sub

Err_NEXT_Click:
If Err.Number = 2105 Then
Resume Exit_Next_Click
Else
MsgBox Err.Description
Resume Exit_NEXT_Click
End If

End Sub

 

[AnhCVL via AccessMonster.com]

Hi Beetle,

Thanks for the solution and the tips, I apologized for the double post. I've
tried your suggestions by placing the codes under Form_BeforeUpdate event,
the program doesn't generate any error but it still ignored the missing date
on the sDate field. any idea?

Thanks
Mark

Better to use the *form's* Before Update event if you want to ensure
values get entered. It has a built in Cancel argument;

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Nz(iSTATUS, "") = "" Then
MsgBox "Select a Status to continue", vbOKOnly, "Missing Data"
Cancel = True
iSTATUS.SetFocus
ElseIf Me!iStatus = "yes" And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
Cancel = True
sDATE.SetFocus
End If

End Sub

This may, however, cause the procedure for your NEXT button to error
out and throw up an additional message box. You can trap for that
particular error in the code for your NEXT click event;

Private Sub NEXT_Click()
On Error GoTo Err_NEXT_Click

DoCmd.GoToRecord , , acNext

Exit_NEXT_Click:
Exit Sub

Err_NEXT_Click:
If Err.Number = 2105 Then
Resume Exit_Next_Click
Else
MsgBox Err.Description
Resume Exit_NEXT_Click
End If

End Sub

Hi all,

[quoted text clipped - 57 lines]

Thanks.
Mark

 

[tkelley via AccessMonster.com]

Here is how I would do this:

Private Sub NEXT_Click()
If Nz(iSTATUS, "") = "" Then
MsgBox "Select a Status to continue", vbOKOnly, "Missing Data"
Me.iSTATUS.SetFocus
Exit Sub
Endif

If Me.iStatus = "yes" then
If Nz(sDATE, "") or IsDate(sDATE) = FALSE then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
Me.sDATE.SetFocus
Exit Sub
Endif
Endif

'rules passed successfully
DoCmd.GoToRecord , , acNext
End Sub


HOWEVER ... If you need to worry about padded spaces (in other words, looks
null, but isn't an empty string)

Change this:
If Nz(iSTATUS, "") = "" then
To this:
If len(trim(iSTATUS))=0 then

Hi Beetle,

Thanks for the solution and the tips, I apologized for the double post. I've
tried your suggestions by placing the codes under Form_BeforeUpdate event,
the program doesn't generate any error but it still ignored the missing date
on the sDate field. any idea?

Thanks
Mark

Better to use the *form's* Before Update event if you want to ensure
values get entered. It has a built in Cancel argument;

[quoted text clipped - 40 lines]

 

[AnhCVL via AccessMonster.com]

Hi Kelley,

I tried but still react the same way, does not generate error but ignored the
"sDate" regardless of "iStatus" value. I changed the "iStatus" from to a
combobox and use the listindex control, it worked. The code is as below:

If iSTATUS.ListIndex = 0 And Nz(sDATE, "") = "" Then
MsgBox "Enter a valid Date to continue", vbExclamation, "Missing Required
Data!"
DoCmd.CancelEvent
sDATE.SetFocus
sDATE.SelStart = 0
Else
DoCmd.GoToRecord , , acNext
End If

I placed this under the Next_click() command button.

Thanks for the suggestion thought.
Mark

Here is how I would do this:

Private Sub NEXT_Click()
If Nz(iSTATUS, "") = "" Then
MsgBox "Select a Status to continue", vbOKOnly, "Missing Data"
Me.iSTATUS.SetFocus
Exit Sub
Endif

If Me.iStatus = "yes" then
If Nz(sDATE, "") or IsDate(sDATE) = FALSE then
MsgBox "Enter a valid Date to continue", vbOKOnly, "Missing Data"
Me.sDATE.SetFocus
Exit Sub
Endif
Endif

'rules passed successfully
DoCmd.GoToRecord , , acNext
End Sub

HOWEVER ... If you need to worry about padded spaces (in other words, looks
null, but isn't an empty string)

Change this:
If Nz(iSTATUS, "") = "" then
To this:
If len(trim(iSTATUS))=0 then

Hi Beetle,

[quoted text clipped - 11 lines]