I am attempting to validate user input on a bound form using VBA.

I need to check whether a user is entering the correct data type. Which is either a numeric value or text. I was wanting to know if VBA had functions I could use to do this? And where would it be best to place this test? I was thinking within the OnExit event?

I had tried writing a simple IF statement, but that didnt work, as when on running the form the code didnt not execute.

The IF statement was as follows

If Me.nametxtbox.Value = "" Then
   Me.nametxtbox.SetFocus
End If

The nametxtbox refers to the input box on the form. Is this correct or should I be referring to the actual database field.

Any ideas? as I am totally confused?


Gaz

__________________
Gaz

If you put it in the before update and set Cancel = True if it is invalid, the focus won't leave the control after your warning message. You could use another event and setfocus to the control and do a ctlname.Undo to clear the change. In general, the code for testing Number, Date and String are as follow, where I've allowed number or date entry but cancel (Before Update event only) if it is a text string.

    If Len(Me.nametxtbox & "") Then
        If IsNumeric(Me.nametxtbox) Then
            MsgBox "Is a number"
        ElseIf IsDate(Me.nametxtbox) Then
            MsgBox "Is a date"
        Else
            MsgBox "Is text"
            Cancel = True
        End If
    Else
        MsgBox "Is empty"
    End If

You could test for null rather than length of the control contents & "" but this works fine. The length test is for the case where a control had a value which has now been deleted though one is required.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Thanks for the suggestion. I have since got my code to kind of do what I wanted, but your suggestion is much clear. I am new to VBA, and although I understand the syntax, I am not aware of all the usefully functions. so thanks for the info

Gaz