Form Validation w/ VBA function

thisisntwally · Sep 7, 2007

ok, so im trying to validate the values in a forms fields with vba. the function is called at the start of my onclick event as such:

Code:

Call amdValid(Forms![Input Form])

While the values in my If statements display the correct values when stepping through, it seems to ignore this, skips the message box and cancel = true, and proceeds to the next if all the way through the function. I thought that all i needed was if ....then msgbox "..." cancel = true end if. Is my If statement invalid? or what? Im kinda baffled here.

this is the function:

Code:

Public Function amdValid(vFrm As Form)

    If (vFrm!txtSSD - vFrm!txtDOR) > 3 And vFrm!txtComments = "" Then
        MsgBox "Turnaround Time is greater than 3 days, please comment."
        Cancel = True
    End If

    If (vFrm!txtSSD Or vFrm!txtDOR) = "" And vFrm!chkStatus = "" Then
        MsgBox "Please include Date of Reciept or Site Submission Date"
        Cancel = True
    End If
    
    If vFrm!objRequest = "" Then
        MsgBox "Please attach Request Document"
        Cancel = True
    End If
    
    If vFrm!cboPurchaseOrder = "" Then
        MsgBox "Please Select a PO number.  If you need to input a new PO number click the add button to the right of the PO field"
        Cancel = True
    End If
    
    If vFrm!txtvalue = "" Then
        MsgBox "Please Include a Dollar Value for this Record"
        Cancel = True
    End If
    
End Function

MarkK · Sep 8, 2007

A couple of things. I never use the "!" in a qualified name of anything. Not sure if that matters. But more likely, if nothing has been entered in a text box it's value is Null, not "", so your boolean expressions never evaluate to true.
The easiest way to check for both a Null value or "", both of which look exactly the same on screen, is to use the Nz() function.
The Nz() function evaluates its first parameter. If the first parameter is Null it returns the second parameter, otherwise it returns the first parameter. Read more in Access help.
Code would look something like this...

Code:

    If Nz(vFrm.txtvalue, "") = "" Then
        [COLOR="Green"]'the field is either Null, or ""[/COLOR]
        MsgBox "Please Include a Dollar Value for this Record"
        Cancel = True
    End If

thisisntwally · Sep 10, 2007

thanks for the nz bit, that was spot on. on to new Problems...

Form Validation w/ VBA function

thisisntwally

CE&A

MarkK

bit cruncher

thisisntwally

CE&A

Similar threads

Users who are viewing this thread