Error Handling empty text box

[Flynners]

Hi all
I have a count text box on one tab in my form. In english I want to check that all records required are entered before moving on to the next tab, by looking up a control total e.g number in batch is 10, user must enter 10 records before moving onto next tab.

In the onenter event of subform in my next tab I have the follwing code but i get the "you have entered an expression that has no value" error if my count text box (txtSubTotal) is empty.

If (Not IsNull(txtsubtotal)) And (Me.txtSubtotal <> Me.NoOfSubmissions) Then
MsgBox "You must enter all Stage 1 before proceeding to Stage 2"
Me.Scoring_Stage1.SetFocus
End If

..... basically if the subtotal is not empty but does not equal the number of submissions you cant move on.

I have tried different variations of code with Not isNull, Len(Nz(txtSubtotal > 0))

What is the correct syntax I should use with reference to empty text box?

any advice greatly appreciated

 

[John Big Booty]

Try putting a break point in your code at the If statement, and see what values the various controls are holding when the code is stopped.

 

[Flynners]

I put in the break point as suggested and the error is because NoOfSubmission=Null
So I amended code as below but still get same error

If (Not IsNull(txtSubtotal)) And (Not IsNull(NoOfSubmissions)) And (Me.txtSubtotal <> Me.NoOfSubmissions) Then
MsgBox "You must enter all Stage 1 before proceeding to Stage 2"
Me.Scoring_Stage1.SetFocus
End If

in english, if the NoOfsubmissions (numeric field) is entered, and there are some records entered(therefore populating the count subtotal), then check that the number of submissions and subtotal are equal before moving on.

Is there a better way to approach this.

thanks

 

[John Big Booty]

Does it have the effect you are after?

 

[Flynners]

sorry no it doesnt. i still get the error "the expression you have entered has no value" and when i debug the error it tells me NoofSubmission=Null

 

[John Big Booty]

Try;

If (Not IsNull(txtSubtotal)) And (Not IsNull(NoOfSubmissions)) And (Me.txtSubtotal <> [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL](Me.NoOfSubmissions,-1) Then
MsgBox "You must enter all Stage 1 before proceeding to Stage 2"
Me.Scoring_Stage1.SetFocus
End If

In the Nz() above replace -1 with a number so improbable that you can be reasonably sure that txtSubtotal will never be equal to it.

 

[Flynners]

I tried as suggested above but was still getting the "you have entered an expression that has no value" error.

I took the "if NoOfSubmissions is null" out of the equation with an if statement which exited sub and setfocus to noofsubmissions. So basically you couldnt go to this tab if noofsubmissions wasnt populated

So that left, if total entered didnt equal noofsubmissions you couldnt go to this tab. the following worked for me...... i dont know why but im not questioning it!

If Len(Nz([txtSubTotal], "")) <> 0 And Me.txtSubTotal <> Me.NoOfSubmissions Then
MsgBox "Please complete Stage 1 scoring before proceeding to Stage 2 scoring"
Me.Scoring_Stage1.SetFocus
End If

I found the syntax on below link Tips for working with Null from Allen Browne
http://allenbrowne.com/casu-12.html


thanks for your response to my query and thanks to Allen Browne!