Validation of Field Value

[CPGDeveloper]

Hi All -- I administer an Application -- Access Front End/Azure SQL Server Back End -- I'm trying to create validation for a bound text field on a form, which is bound to a SMALLINT (32,767 max range) field in our back end.

This field can only be between 0 and 1000. While I could create that validation for the field in the back end, if I do that, as far as I can tell, there is no way to avoid system generated messages if someone enters, say 2000:

"The value you entered isn't valid for this field"

Instead of that, the client wants a polished message, that in this case, says something to the effect of 'You cannot enter a value over 1000'. So if I write some kind of validation in the BeforeUpdate Event:

If txtField1.value>1000 Then
Me.txtField1.Undo
docmd.openform "fErrorMessageForm" 'form with company logo and error message
End If

The Undo function doesn't seem to work here. It simply is not producing the previous value in the field, but odd random values. 'Me.Undo' works, however, it would undo any other changes the user may have made. 'Me.Undo' also works if I use it on the AfterUpdate Event, but Me.txtField1.Undo does nothing. In addition, if I leave this field as purely a SMALLINT and the user enters say '50,000', the system generated 'The value you entered isn't valid for this field' message will pop-up, and this code will not execute.

Is there something I'm missing here? Or perhaps there's a much better way to do this? Thanks in advance for your time and feedback, much appreciated!

 

[welshgasman]

There is no .Undo property for a control? I have just tried, and intellisense does not show it?
You cannot just make stuff up.

I would set it to Null and then set Cancel = True
Now that would cancel the update of the record, but that is what you want until you get a valid value in your control.

You should also compile your code before trying to run it. That would have alerted you to this fact

 

[CPGDeveloper]

Thank you for your response.

I assure you I am not 'making stuff up' nor would I think to waste anyone's time like that -- and yes, I do understand the concept of compiling my code. The TextBox.Undo method is detailed in this article from Microsoft - TextBox.Undo method (Access)

I'm using Microsoft Access 365, and again, I assure you, this is is compiling without issue:

Although as I test this scenario again, it does seem to be 'Undoing' to the previous value correctly. Somehow the first time I was working on this, it didn't seem to be working properly -- but perhaps I've been staring at this too long.

Either way, thanks for responding. It does appear to be working now.

 

[welshgasman]

Hmm, wonder why it balked for me then?
I was only offered Value, but when I keyed in undo, it did convert to Undo, but then immediately complained when I compiled?
I am now on 2019, so what version are you on?

I still think you need the Cancel = True though?

OK, my sincere apologies I inadvertently used the source field name and not the control name.

Edit: Just tested with the control. The Undo did not work until I used Cancel = True.
I got the error message but the 7 remained in regardless.
That link of yours does not infer that?

Private Sub txtFactor_BeforeUpdate(Cancel As Integer)
If Me.txtFactor > 5 Then
    MsgBox "More than 5"
    Me.txtFactor.Undo
    Cancel = True
End If
End Sub

 

[CPGDeveloper]

Yes, the Microsoft article makes no mention of 'Cancel = True', and for me it appears to be working with or without the 'Cancel = True', but regardless it does seem that using the TextBox.Undo method in the BeforeUpdate Event of the textbox control is the way to address this issue:

Private Sub txtField1_BeforeUpdate (Cancel As Integer)

'Limit field to 100 or below
If Me.txtField1.Value > 100 Then
Me.txtField1.Undo
Msgbox "Field must be 100 or less"
Cancel = True 'Not sure if this is needed
End If

End Sub

 

[CPGDeveloper]

In addition, I found a solution to the other part of my question -- I can bypass the system message when a user enters a value outside of the scope of the field type. So in this example, as a SMALLINT, the maximum value is 32,767. If the user enters 50000, the above BeforeUpdate Event does not execute. Instead, I get the following system message:

To bypass this, in the Form_Error Event of the form in question, I add the following -- I hope this is useful to others:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 Then 'please check error number
        with Me.ActiveControl
            if .Name = "ControlName" Then
                msgbox "your message"
                .Undo
                Response = acDataErrContinue
            end if
        End with
    End If
End Sub

 

[Micron]

Perhaps worth mentioning that a bound textbox has the OldValue property which may be useful at times, although Undo and Cancel should work. AFAIK textbox does have an undo method so not sure what's up with that here.

 

[CPGDeveloper]

Thanks Micron -- I'll play with the OldValue property as well.