If and ElseIf question.

[Jerry Call]

Here's a sub to validate for two things: (1) entry must be numeric; (2) entry must not be zero.

[Code]
Private Sub NoInFull_Change()

If Not IsNumeric(Me.NoInFull.Text) Then
MsgBox "This field requires a numeric value." , vbOKOnly
Me.NoInFull.Undo

ElseIf Me.NoInFull.Text = 0 Then
MsgBox "Entry must be greater than zero." , vbOKOnly
Me.NoInFull.Undo


End If
End Sub
[Code]

In its present form Access correctly checks for a non-numeric value
and allows the user to enter a number. But then the second validation
doesn't run at all. So if the user first enter "bg", then responded
to the message by entering 0, the program would accept the zero.
That won't do. They've also got to make sure it's larger than zero.

Obviously I want to check both things in one operation.
Is there perhaps an "Or" clause that would check for both conditions
in one swoop? Then I could use just one MsgBox.

[davegri]

Try this:

Private Sub NoInFull_Change()


If Not IsNumeric(Me.NoInFull.Text) Then
	MsgBox "This field requires a numeric value." , vbOKOnly
	Me.NoInFull.Undo
	exit sub
End If


If Me.NoInFull.Text = 0 Then
	MsgBox "Entry must be greater than zero." , vbOKOnly
	Me.NoInFull.Undo
End If
End Sub

[accesstos]

I'm wondering how a user can respond to a message by entering 0.

Anyway, to check both things in one operation, try it in this way:

Private Sub NoInFull_Change()
    With Me.NoInFull
        If IsNumeric(.Text) And (Nz(.Text, "") > 0) Then
        Else
            MsgBox "This field requires a numeric value greater than zero.", vbOKOnly
            .Undo
        End If
    End With
End Sub

[orange]

I think accesstos' solution will do what you need. My question is whether or not the "Change event' is where this code should go, rather than in the BeforeUpdate event. BeforeUpdate is where most validation code would go.
Just a thought for consideration.

[Jerry Call]

I see a number of other solutions listed below, but none of them
would qualify as accesstos. Nor do those other solutions
seem to address my situation.

How do I get to this material.

[orange]

Click the link(s) in Similar Threads to see the responses.

If none answers your question, then perhaps you should describe the problem again.

[Jerry Call]

OK, let's start from scratch. I have a textbook control named NoInFull. It's numeric, and records
the number of individual pieces in, say, a case. So for Pepsi or Aquafina it would normally be 24. For some
snack packages, 12, and so on. But it can't be zero, and it can't be of text type.

I had been running it under the Change event, on the advice of someone else on this forum.
But the consensus seems to be that BeforeUpdate is the right spot. So that's where I'm
working with it now. (All my other procedures in BeforeUpdate are working fine).

Here's what I want to do:

If the user's entry either (1) numeric or (2) greater than zero, or both,
accept it and move to the next control, FullUnitCost, which is set to currency
and not causing any trouble.

Anything else gets rejected and the user has to try again, if necessary,
multiple times until both requirements are correct.

I have had some success as long as I stick to only one item or the
other. But I haven't been able to figure out how to check for both
of them. After the code for one runs, I can't seem to get the
code for the other to run.

For instance, if the user enters "bg" I can get rid of that and return
the user to the control. But if they then enter zero it lets it through
instead of telling them it has be greater than zero.

Presumably some form of an If / ElseIf / or whatever structure would work best.
But maybe syntax is also involved. For instance, if I write Me.NoFullUnit.Value = 0,
is it right to be using Value instead of Text? And what about the zero figure?
Presumably since it's numeric, I would think 0 would do the job. But maybe not?
Tried putting it in quotes, but that seems wrong because quotes say it's a string.
None of these throw an error, but it seems the program ignores everything after
the first "fix" is made, so no way to know for sure.

[orange]

Here is a BeforeUpdate event for a textbox Text0 from an old database. Hopefully, you can use this example and adjust your event code accordingly.

'---------------------------------------------------------------------------------------
' Procedure : Text0_BeforeUpdate
' Author    : ....
' Date      : 28/03/2011
' Purpose   : To limit a textbox named Text0 to holding only 4 numerics
'---------------------------------------------------------------------------------------
'
Private Sub Text0_BeforeUpdate(Cancel As Integer)
10       On Error GoTo Text0_BeforeUpdate_Error

20    If IsNumeric(Text0.Value) Then
30        If Len(Text0.Value) = 4 Then
          MsgBox "Numeric and Length 4 -- perfect"
40        Else
          MsgBox "Numeric but length is " & Len(Text0.Value) & " Not 4 as it should be", vbOKCancel
50        Cancel = True
60        Me!Text0.Undo
70        End If
80    Else
        MsgBox Text0.Value & " is Not Numeric ", vbOKCancel
90      Cancel = True
100     Me!Text0.Undo
110   End If

120      On Error GoTo 0
130      Exit Sub

Text0_BeforeUpdate_Error:

140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Text0_BeforeUpdate of Form_frmTextNumericLen4"
End Sub

[Jerry Call]

Let's start with just the first test--to forbid non-numeric entries.

[Code]
Private Sub NoInFull_BeforeUpdate(Cancel As Integer)

' This never gets a chance to run; it's always preempted
' by Access' default generic error message.

If Not IsNumeric(Me.NoInFull.Value) Then
MsgBox "My message box for numeric.", vbOKOnly
Cancel = True
Me.NoInFull.Undo
DoCmd.GoToControl "NoInFull"
End If

End Sub
[Code]

Assuming I can get this code to override the Access generic default message,
should this work to intercept non-numeric entries?

If we get this one solved, then we'll move on to the next hurdle: rejecting
any numeric entries that are not >0.

[orange]

Can you post a zip copy of your database? Only a few records to show the error.

Update

I created a form with 2 textboxes.
txtbx1 NoInFull
txtbx2 text2 ( need to move to this so that the before update on NoInFull is executed)

I modified the code a littleand revised again to include >0 check)

' ----------------------------------------------------------------
' Procedure Name: NoInFull_BeforeUpdate
' Purpose: To show some validation of a text box using the BeforeUpdate event.
' Procedure Kind: Sub
' Procedure Access: Private
' Parameter Cancel (Integer): Textbox whose value is to be validated.
' Author: Jack
' Date: 11-Jun-20
'
'NOTE: The validation could be done in the Forms' BeforeUpdate also
'especially is there were multiple control validating to be done.
' ----------------------------------------------------------------
Private Sub NoInFull_BeforeUpdate(Cancel As Integer)

          
10        If IsNull(Me.NoInFull) Then
20            Debug.Print "NULL  value in NoInFull"

30        ElseIf Len(Me.NoInFull) = 0 Then
40            Debug.Print "There was  no value supplied for NoInFull"

50        ElseIf Not IsNumeric(Me.NoInFull.Value) Then
              ' MsgBox "My message box for numeric.", vbOKOnly
60            Debug.Print "Value <" & Me.NoInFull & ">  in NoInFull is NOT numeric "
70            Cancel = True
80            Me.NoInFull.Undo
              '  DoCmd.GoToControl "NoInFull" '''...This when active gives error 2108
          
90        ElseIf Me.NoInFull = 0 Then
100           Debug.Print "Value <" & Me.NoInFull & ">  in NoInFull must be greater than 0"
110       Else
120           Debug.Print "Value <" & Me.NoInFull & ">  in NoInFull is numeric "
130       End If

End Sub

Sample results from the debug.print statements:

Value <12345> in NoInFull is numeric
Value <Abcde> in NoInFull is NOT numeric
Value <-234> in NoInFull is numeric
NULL value in NoInFull
Value <234> in NoInFull is numeric
Value <0> in NoInFull must be greater than 0
Value <137340> in NoInFull is numeric
NULL value in NoInFull
Value <abcdw> in NoInFull is NOT numeric

Note: The only way I could get a NULL value into NoInFull was to type a few characters, then delete them all and go to text2.


Your line

'  DoCmd.GoToControl "NoInFull" '''...This when active gives error 2108

gave me an error 2108 before I made it a comment.

[Jerry Call]

Let's take this one step at a time.

First, the test to make sure the entry is numeric

[Code]
Private Sub NoInFull_BeforeUpdate(Cancel As Integer)

If Not IsNumeric(Me.NoInFull.Value) Then
MsgBox "Entry must be numeric." , vbOKOnly
Me.NoInFull.Undo
End If
[Code]

If I run this with an entry of abc, the above code never gets a
chance to see the light of day because the Access default generic
error message comes up.

So the first thing to solve is: How to prevent the default message
from coming up? The Set Warnings function doesn't seem to have
any effect on situations like this.

[Jerry Call]

Hold it, friends! I've changed my strategy about this problem, and have come up with
something that works.

If the user enters a non-numeric valuei in NoInFull the generic Access error message
will come up. True, it's ugly and non-user-friendly, but its message is clear enough.
So I'll just let that take care of that issue.

That leaves checking for a non zero value. Here's what works:

[Code]
Private Sub NoInFull_BeforeUpdate(Cancel As Integer)
If Me.NoInFull.Value = 0 Then
MsgBox "Entry must be greater than zero.", vbOKOnly
Cancel = True
End If
End Sub
[Code]

This will repeatedly take the user back to the zero entry until they put in
something bigger. And no runtime errors are thrown.

So consider this issue closed. Sorry it got so bollixed up.
That shows I deserve my novice rating. Maybe I can aspire
to move up to Novice First Class in time. But "Battered Novice"
is more likely to be my lot.