MS Access 2007 VBA: validating text field with VBA and Chr()

Question

I am trying to validate entry of a form field using the following VBA. This works very well. My problem is this Access app creates a variety of XML data files, and I don't want certain characters within that xml....namely soft-returns (Shift+Enter). I believe that the Chr for this is Chr(11), but I don't think I can just add , Chr(11)) to the end of the array below and this will work...how can I use Chr(#) in link manner?

 Dim i, j As Integer
 dim myField as variant

 varNo = Array("\", "/", ":", "*", "?", """", "<", ">", "|")

 If IsNull(Me.FieldValue) = False Then
    myField = Me.FieldValue 
    For i = 0 To UBound(varNo)
    j = InStr(1, myField , varNo(i))
    If j > 0 Then
        MsgBox "Cannot use character:" & Chr(13) & Chr(10) & Chr(13)
        & Chr(10) & varNo(i), vbCritical, " Illegal Character"

        Exit Sub
        Exit For
    End If
    Next
 End If

again the above works great for those things in the array, but I would like to include Chr() as well.

I think you may be better of with regex and a check for what you are allowed, which seems to include little more than alpha numeric. — Fionnuala, Nov 13 '12 at 14:54
You could have another number array and check instr for chr(numarray(i)) — Fionnuala, Nov 13 '12 at 14:55
@Remou...I see. Perhaps this is the way to go, just have no idea about RegEx or how to pull this off, but I appreciate the direction. I really only want to allow alpha-numeric characters. — Justin, Nov 13 '12 at 15:32

HansUp · Accepted Answer · 2012-11-13T16:18:39.753

1

You can build your array with an empty string as the new last element, then change the value of the last element to Chr(11).

varNo = Array("\", "/", ":", "*", "?", """", "<", ">", "|", "")
varNo(UBound(varNo)) = Chr(11)

Actually, I'm unsure why that should be necessary. This works for me ...

varNo = Array("\", "/", ":", "*", "?", """", "<", ">", "|", Chr(11))

Based on the comments, I think it will be useful to confirm the text you're evaluating actually contains the characters you expect. Feed the text to this procedure and examine its output in the Immediate window.

Public Sub AsciiValues(ByVal pInput As String)
    Dim i As Long
    Dim lngSize As Long
    lngSize = Len(pInput)
    For i = 1 To lngSize
        Debug.Print i, Mid(pInput, i, 1), Asc(Mid(pInput, i, 1))
    Next
End Sub

edited Nov 13 '12 at 16:18

answered Nov 13 '12 at 15:05

HansUp

95,961
11
77
135

@Hans...been a while. How are ya? so this will work unless I want to add more than one right? – Justin Nov 13 '12 at 15:33
also I don't seem to catch it with this either....not sure why but access doesn't seem to act like it knows its there?? correction: the msgbox displays the value if I add an illegal chr at the end i.e. : so it can see it, but the above doesn't cause the j variable to become 1 just evaluating the soft returns....i can see that weird circle with an arrow symbol and this end up in the xml – Justin Nov 13 '12 at 15:35
on your edit: really!?! i can't get it to work for me...it there a setting or something i missed?? thanks for your help btw – Justin Nov 13 '12 at 15:41
Glad to see you again, Justin. What do you mean by don't seem to catch it? I'm lost there. – HansUp Nov 13 '12 at 15:41
I'm not aware I did anything special or unusual with that edit. I added a break point after the assignment to `varNo`, then in the Immediate window `? varNo(UBound(varNo))` gave me `[]` and `? Asc(varNo(UBound(varNo)))` gave me 11. – HansUp Nov 13 '12 at 15:45
so I am testing this with a form and it has a text box which I am typing this into, on a button click event I am using the above code to test the values in the form. I have added Chr(11) to the array as you did in your edit.....type in a couple words with Shift+Enter and click the botton....it passes.....add a : at the end and the validation catches it (fires the msgbox) and if I haven't changed anything actually shows both the weird symbol for soft return and the :....remove the colon....nothing. It seems that j is not increased to 1 by the soft return alone???? – Justin Nov 13 '12 at 15:46
Confirm Shift+Enter really enters a character into your text box. I just entered `a` plus Shift+Enter plus `b` into `txtFoo`, and then from break mode `Len(Me.txtFoo)` answers 2. Doesn't seem like Shift+Enter added anything. – HansUp Nov 13 '12 at 15:55
yeah...apologies...now I am completely confused with how this ends up in the xml. thanks for the help though...i think I am back to the drawing board – Justin Nov 13 '12 at 16:01

Fionnuala · Answer 2 · 2012-11-13T16:00:26.243

1

Here are a few notes on another approach:

'' Microsoft VBScript Regular Expressions library
'' or CreateObject ("VBScript.RegExp")

Dim rex As New RegExp

With rex
   .MultiLine = False
   .Global = True
   .IgnoreCase = False
End With

'A line for testing
sLine = "abc" & Chr(11)

'Anything that is NOT a-zA-Z0-9 will be matched
rex.Pattern = "[^a-zA-Z0-9]"
If rex.Test(sLine) Then
    Debug.Print "Problem: include only alpha numeric"
End If

edited Nov 13 '12 at 16:00

answered Nov 13 '12 at 15:44

Fionnuala

90,370
7
114
152

thanks very much for the above Remou....one final question....do you know of a good resource for learning simply that pattern aspect? like if I wanted to add spaces to what is acceptable to the above? thanks for the help – Justin Nov 13 '12 at 16:26
@Justin Your Access app creates XML files. Is there a need to inform the user when those files contain unwanted characters? IOW, would it be reasonable to just discard (or substitute) the unwanted characters? If so, look at the regexp replace method. http://www.regular-expressions.info/vbscript.html – HansUp Nov 13 '12 at 16:43
thanks to you both. @Remou...I am using this now and have added some things to the RegEx test list such as [^a-zA-Z0-9\s'-] and this catch quite a few things but it doesn't catch everything....like a greater than/less than which considering the xml can be a problem. am I missing something about the concept? it is suppose to use that line as the ONLY acceptable values right? – Justin Nov 14 '12 at 13:56
@Hans ...yeah that is a cool idea. I was just going to not let them save the values at the beginning if there were not good characters just to keep them out. But I will certainly check that out too. Thanks guys! – Justin Nov 14 '12 at 13:57
I think you may be looking for `rex.Pattern="\W."` re http://www.regular-expressions.info/reference.html see also http://stackoverflow.com/questions/2991901/regular-expression-any-character-that-is-not-a-letter-or-number – Fionnuala Nov 14 '12 at 14:34

MS Access 2007 VBA: validating text field with VBA and Chr()

2 Answers2