Validation rule

[wojnicztrust]

The field in form should has the same rule what creating folder in Windows.
So Field shouldn't get sign like "| \ / : * ? " < > "
What I need to write in validation rule? I saw help but there are no information about my problem:/

I can't have:
Is Null OR Not Like "*[!((a-z) or (0-9))]*"

Because in my language are special letters.

Guys please help

 

[pr2-eugin]

wojnicztrust, you can use a function to find if the String has the Special Chars, if it does Cancel the update.. Using the Form Control's Before Update method.. Something along the lines of..

Private Sub theControlName_BeforeUpdate(Cancel As Integer)
    If [COLOR=Red][B]Not[/B][/COLOR] validateStr(Me.theControlName) Then
        Call MsgBox("The String cannot contain any of the following characters : "| \ / : * ? " < > ", Please enter again")
        Cancel = True
    End If
End Sub

Where validateStr is a user defined function as such..

Public Function validateStr(tmpStr As String) As Boolean
[COLOR=Green]'-------------------------------------------------------------------------
'   A Function that will take in a String that needs to be validated
'   returns a Boolean True or False based on the Check.
'
'   Input   : A String to check for Special Chars.
'   Output  : True - If the String does not have any special chars.
'             False- If the String has any of the chars.
'   Example :
'   ? validateStr("Johnny")
'     True
'   ? validateStr("Joh>ny")
'     False
'--------------------------------------------------------------------------[/COLOR]
    If InStr(tmpStr, "|") <> 0 Or InStr(tmpStr, "\") <> 0 Or InStr(tmpStr, "/") <> 0 Or InStr(tmpStr, ":") <> 0 _
        Or InStr(tmpStr, "*") <> 0 Or InStr(tmpStr, "?") <> 0 Or InStr(tmpStr, """") <> 0 _
        Or InStr(tmpStr, "<") <> 0 Or InStr(tmpStr, ">") <> 0 Then
        validateStr = False
    Else
        validateStr = True
    End If
End Function

 

[wojnicztrust]

I don't know why but it doesn't work. This is weird because I will not have any error.
I should place code of function below the text in vba
"Option Compare Database
Option Explicit", right?

I put code (theControlName_BeforeUpdate(Cancel As Integer)) on event before update in my control "Name"
Is this problem that, I have event which create new folder in windows in the same control on event after update?

 

[pr2-eugin]

You can put the validateStr function either inside the form or in a module.. It does not affect.. To make things easier, go to the design view of the form, click the control that you need to validate for.. Then on the Events tab.. Look for the BeforeUpdate event and then choose Event Procedure from the list of choices to write event.. Then it will take you to the VBA editor.. Just copy and paste the following lines of code..

    If validateStr(Me.theControlName) Then
        Call MsgBox("The String cannot contain any of the following characters : "| \ / : * ? " < > ", Please enter again")
        Cancel = True
    End If

between the Private and End Sub Statements..

 

[wojnicztrust]

Oh now I see, if I write some good chars like"john" then appears msgbox, but when I write some bad chars like "/" then nothing happens, so it function works conversely

 

[pr2-eugin]

Oops.. My bad.. Sorry.. I have changed the code..

 

[wojnicztrust]

How can I check that control "Name" is > 0
Normally I use to

If Len(Nz(Me.Name)) > 0 Then

But now when I have

If not...

I don't now how to write it.

 

[pr2-eugin]

Few things.. Name is a reserved name in Access.. A very bad choice for Field/Control name.. If you are in a position to change them I would advice you to do it ASAP.. Len() will fail if the Name input becomes Null.. As Len() requires a String input.. And Null is not an empty string.. So the line of code will remain the same, with the addition of the Empty check.. as..

If ((Not validateStr(Me.theControlName)) Or (Len(Me.[COLOR=Red][B][[/B][/COLOR]Name[COLOR=Red][B]][/B][/COLOR] & vbNullString) = 0)) Then
    Call MsgBox("The String cannot be EMPTY or cannot contain any of the following characters : "| \ / : * ? " < > ", Please enter again")
    Cancel = True
End If

 

[wojnicztrust]

Access show me error that can't save this sentence:/

so this

(Len(Me.[Name] & vbNullString) = 0))

check of my control that it isn't blank, right?

Normally I not use "Name" but I wrote this like example.
This control has name "NazwaSkrocona" which means "short name" for polish

 

[pr2-eugin]

What is the name of the control? Is it Name or NazwaSkrocona? The Control name needs to get in after the Me. keyword..

 

[wojnicztrust]

The name of control is "NazwaSkrocona" and the code is:

If ((Not validateStr(Me.NazwaSkrocona)) Or (Len(Me.NazwaSkrocona & vbNullString) = 0)) Then

Access show error that can't save in before update.

 

[pr2-eugin]

Show the complete code you have in the BeforeUpdate Event.. You cannot Assign any values to the control if they have been cancelled by the BeforeUpdate event..

 

[wojnicztrust]

If ((Not validateStr(Me.NazwaSkrocona)) Or (Len(Me.NazwaSkrocona & vbNullString) = 0)) Then
    Call MsgBox("The String cannot be EMPTY or cannot contain any of the following characters : "| \ / : * ? " < > ", Please enter again")
    Cancel = True
End If

Your first code was great, but I need add something to check that my control isn't blank

 

[pr2-eugin]

The code would take care of it.. However, I know why the code would have failed.. Try the following..

If ((Not validateStr(Me.NazwaSkrocona & vbNullString)) Or (Len(Me.NazwaSkrocona & vbNullString) = 0)) Then
    Call MsgBox("The String cannot be EMPTY or cannot contain any of the following characters : "| \ / : * ? " < > ", Please enter again")
    Cancel = True
End If

 

[wojnicztrust]

Working! I'm grateful to you for your help

 

[pr2-eugin]

Glad to help.. Good luck..