Validation Text doesn't come up

[Diana van den Berg]

I have a Validation Rule and Validation Text which works in the numeric field of a table and the query. In the form, the Validation Rule works, but the Validation Text doesn't come up.

How can this happen. I have put it in as normal and I can't see what else to check.

Thank you for any help.

[HansV]

What do you mean by 'the Validation Rule works'? That the built-in validation message is displayed?

[Diana van den Berg]

By "the Validation Rule works", I mean that I have made the rule Is Not Null and it doesn't allow Null.

By the Validation Text doesn't come up" I mean that the text that I have put into Validation Text does not display when the Validation Rule works and thus stops the user from leaving the field empty.

[HansV]

So what message do you get if you clear the field in the form?

[Diana van den Berg]

There is no message. That is the problem. If I type in 0 or press Esc (because the default is 0), the problem is resolved, but I don’t want the user to have to guess or be told that.

I have just discovered that the problem is caused by the On Error message in what I used of Roger Carlson in the form, to generate sequential numbering for a jobcard number (the field of which is called TicketNumber).

The problem (of no Validation Text) arises in the field called HoursFRACOnFollowingVisits.

The same problem is also caused in the field InvoiceNumber in the same form. That field will only take numerical digits and if the user tries (unlikely, but a slip of the hand could do it) to enter anything other than numerical digits, no Validation Text comes up.

This is the link to what I used to generate the sequential numbering.

http://www.rogersaccesslibrary.com/forum/topic395.html

I used the version for a Multi-User environment as my database is for a network. I don’t want to have to leave out the On Error code because that is what generates a number if 2 users try to use the same number for TicketNumber (the jobcard number). Perhaps that code could be modified – or perhaps additional code could be added that would apply just to the relevant fields of HoursFRACOnFollowingVisits and InvoiceNumber.

Here are the 2 items of code that my database uses in the main form for the sequential numbering and its On Error:

Code: Select all

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

    Response = IncrementField(DataErr)
    
Exit_Form_Error:
    Exit Sub

Err_Form_Error:
    MsgBox Err.Description
    Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
    If DataErr = 3022 Then
       Me!TicketNumber = DMax("TicketNumber", "Q07:Jobcard") + 1
       IncrementField = acDataErrContinue
    End If
End Function

Thanks for any help.

[HansV]

I'l see if I can come up with a modification.

[BenCasey]

I have a Validation Rule and Validation Text which works in the numeric field of a table and the query. In the form, the Validation Rule works, but the Validation Text doesn't come up.

How can this happen. I have put it in as normal and I can't see what else to check.

Thank you for any help.

Validation Rules text will only appear if the values entered by the user are incorrect.
If you want them to appear when the user hovers over the text box then put them in the ToolsTip property (they can be in both if you wish).

[Diana van den Berg]

Thank you, Hans! You are a star!

Worst case scenario, if it can't be resolved, I can put in a message in the OnScreen MiniManual, but, if at all possible, I would love the Validation Texts to come up.

Ben, I think you have missed some of what I said, but thank you for responding anyway. However, the message does come up when hovering over the field despite the On Error problem - even in a Runtime version, so that is certainly better than nothing, so thank you for that, but if at all possible, I would like the Validation Texts to come up if there is an error as well.

[BenCasey]

What is the input mask that you have for the control you are referring to?
If for example the mask only permits numerics then it will not be possible for anybody to enter any other character.
Also, have you actually tried to enter a non-numeric character in the field? If so, what happens?

[Diana van den Berg]

Ben, the cause of the problem is detailed in my third entry above.

[BenCasey]

Ben, the cause of the problem is detailed in my third entry above.

OK, I will therefore assume (because you haven't told me) that the input mask is ok.

All I can offer then is to say that I would not put the error code into the OnForm Error handler. I would put it into the Error handler for the control and in that check for error 3022 and - using the nz() function to handle any nulls. If it is zero then add the value via the code you have,otherwise report the error etc. If not 0 then handle the other errors accordingly.

Also, I am not sure your Form error handler is correct. You appear to be relying upon the default status of the acDataErrContinue, if the function which only handles 3022. What if it isnt a 3022 error?

[HansV]

The form's On Error event procedure has to be rewritten, indeed.

[HansV]

Try this version:

Code: Select all

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    On Error GoTo Err_Form_Error

    If DataErr = 3022 Then
        Response = IncrementField(DataErr)
    End If

Exit_Form_Error:
    Exit Sub

Err_Form_Error:
    MsgBox Err.Description
    Resume Exit_Form_Error
End Sub

Function IncrementField(DataErr)
    Me!TicketNumber = DMax("TicketNumber", "Q07:Jobcard") + 1
    IncrementField = acDataErrContinue
End Function

[Diana van den Berg]

Thank you, Hans. That works perfectly for the Validation Text coming up. I can't test whether it precludes TicketNumber having duplicate numbers entered by different users in the multi-user environment as I don't have a network here, but I will get the users to test that when I pass it on.

Thank you for your input Ben.