Input mask - basic problem

F

fraser_lindsay

Access wannabe
Local time
Today, 22:31
Joined
Sep 7, 2005
Messages
218
Hi,

I woudl like to apply an input mask to a field to ensure the following:

- All caps
- Max 15 text characters
- Max 4 numnerical characters at the end

NO symbols, as this reference ultimately beomes part of a PDF filename.

Caps is easy, >

and I can sort of get what I want with the text and numbers using this:

>???????????????9999


However, my references are not always that exact length and may be shorter with only 3 numerical values at the end. The problem is that the user has to know to click right at the end for the numbers, which isn't obvious.

Also, it's very messy as the text inserts anywhere within that mask.

Is it possible to just start typing at the start of the field (in caps) but up to the maximum character number permitted and with optional numbers, but absolutely no symbols?


Thanks,

Fraser
 
Because of the varying string length you may need to employ validation on the AfterUpdate Property of the control. Testing for invalid characters and make up of the field.

An alternative is to use the KeyPress/KeyDown event to trap each key stroke and test against a given set of characters.

For the former option you could use something along the lines of

Code: 
Sub TextBox_AfterUpdate()
Me.TextBox = Trim(UCase(Me.Textbox))

If Me.TextBox = "" Then 
   Exit Sub ' Nothing to test
End If

Dim AllowableCharacters As String
Dim bFlag As Boolean

AllowableCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

For X = 1 To Len(Me.Textbox)
   If Instr(AllowableCharacters ,Mid(Me.TextBox,X,1)) = 0 Then
      bFlag = True
      Exit For
   End If
Next

If bFlag = True
   Msgbox "You have entered one or more illegal characters. Please revise and retry",vbExclamation+vbOkOnly,"Bad Name"
   Me.TextBox.SetFocus
End If

End Sub

This is aircode and as such is untested. Remember to use your control names not mine.

David
 
David,

Thanks for generating that, I have dropped it in and renamed the controls. Now, just to confuse issues this isn't actually a text box. It's a combo which looks up from a table of references. However, if it is a new entry the user can type into the combo and add to the list via code I added.

I have set the mask in the underlying table and on the combo to this:

>???????????????9999

Do I still need an input mask as well as the code or does the code replace this completely?

I can't quite get the code to do anything yet, you did say it was untested and I appreciate you taking the time to knock up the strong for me to try.

Do any of the factors above change the principle of applying your code or a variant of it?


If I type to add a new reference it still works - i.e. it ask if I want to add to the list and also allows symbols.

However, if I select a pre-exisiting refernce from the list it trips the debugger at the following line with an 'incorrect syntax' error:

Code: 
If bFlag = True


The full code is here:

Code: 
Private Sub cboJSAref_AfterUpdate()

Me.cboJSARef = Trim(UCase(Me.cboJSARef))

If Me.cboJSARef = "" Then
   Exit Sub ' Nothing to test
End If

Dim AllowableCharacters As String
Dim bFlag As Boolean

AllowableCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

For X = 1 To Len(Me.cboJSARef)
   If InStr(AllowableCharacters, Mid(Me.cboJSARef, X, 1)) = 0 Then
      bFlag = True
      Exit For
   End If
Next

If bFlag = True
   MsgBox "You have entered one or more invalid characters for this reference. Please revise and retry", vbExclamation + vbOKOnly, "Bad Name"
   Me.cboJSARef.SetFocus
End If


End Sub

Thanks,

Fraser
 
Just realised the trip was because I was missing a 'Then' after the start of the IF statement. That stops it falling over if I select a pre-exisiting reference from the combo.
 
If bFlag = True Then

My fault

That's aircode for you.

If we work on the assumption that the data is validated before it hits the table then you can remove the input mask from the field. I assume you are using the OnNotInList() event for adding new references, if so, run the validation there before appending to the table. Don't let tham add it until it is corrcetly formatted. That way there is no need to validate it afterwards.

David
 
ok, that sounds like a good plan. Yes, I am using the 'notinlist' event. I have rather crudely dropped in my code after my exisiting notinlist code, but after a quick try and geuessing that was probably too crude as it didn't do anything.

So, I assume I have to append it inside my existing code?


My current 'notinlist' looks like this:

Code: 
'This code allows the user to create a new entry in a combo list and prompts the user to see if they want to add it

Private Sub cboJSAref_NotInList(NewData As String, Response As Integer)
    On Error GoTo cboJSAref_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The JSA reference " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "ENOC JSA Database")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblJSAref([JSAref]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new JSA reference has been added to the list." _
            , vbInformation, "ENOC JSA Database"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a unique JSA reference from the list." _
            , vbInformation, "ENOC JSA Database"
        Response = acDataErrContinue
    End If
cboJSAref_NotInList_Exit:
    Exit Sub
cboJSAref_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboJSAref_NotInList_Exit
End Sub


I presume I need to validate before it gets added which would mean it needs inserted somewhere around this line:

Code: 
If intAnswer = vbYes Then

Is that right? Should I just insert the validation code in after this line?
 
Just before the If Answer = vbYes is where you need to validate the NewData

The sub I posted earlier needs to be saved as a function in a module (not of the same name as the function.

Code: 
Public Function IsValidFormat(AnyRef As String) As Boolean

Dim AllowableCharacters As String
Dim bFlag As Boolean
bFlag = True

AllowableCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

For X = 1 To Len(AnyRef )
   If InStr(AllowableCharacters, Mid(AnyRef , X, 1)) = 0 Then
      bFlag = False
      Exit For
   End If
Next
IsValidFormat = bFlag
End Function

Then after

Code: 
If intAnswer = vbYes Then
   If IsValidFormat(NewData) = False Then
      MsgBox "You have entered one or more invalid characters for this reference. Please revise and retry", vbExclamation + vbOKOnly, "Bad Name"
      Response = acDataErrContinue
End If

Again this is aircode
 
Ok, here's where I am:

Code: 
'This code allows the user to create a new entry in a combo list and prompts the user to see if they want to add it

Private Sub cboJSAref_NotInList(NewData As String, Response As Integer)
    On Error GoTo cboJSAref_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The JSA reference " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "ENOC JSA Database")
    If intAnswer = vbYes Then
                Call JSArefvalidation
                Next
                If IsValidFormat(NewData) = False Then
                MsgBox "You have entered one or more invalid characters for this reference. Please revise and retry", vbExclamation + vbOKOnly, "Bad Name"
                Response = acDataErrContinue
                End If
        strSQL = "INSERT INTO tblJSAref([JSAref]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new JSA reference has been added to the list." _
            , vbInformation, "ENOC JSA Database"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a unique JSA reference from the list." _
            , vbInformation, "ENOC JSA Database"
        Response = acDataErrContinue
    End If
cboJSAref_NotInList_Exit:
    Exit Sub
cboJSAref_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboJSAref_NotInList_Exit
    

    
End Sub


Good news I had it triggering when I deliberately typed symbols into the combo and it gave me the "Bad Name" error, but then it doesn't stop me entering the reference, it still adds the incorrect reference quite happily.



Then I realised I still had to call the module/procedure so I chucked that in where I thought it should it go. Only it definitely doesn't work now.

I never get modules correct - how do I call it correctly?

Thanks for your help.

Fraser
 
What goes on here?
Call JSArefvalidation
Next
Click to expand...

The IsValidFormat(NewData) should handle the invalid characters

What you really need after that is to check if the make up of the string matches your initial input mask?

>???????????????9999
Click to expand...
To do this you would need the following

first how long is the string

Code: 
If Len(NewData) <> 19 Then
   'Wrong size
End If

'Is it 15 characters followed by 4 digits

Code: 
  For x = 1 to 15
     If IsNumeric(Mid(NewData,x,1) ) = True Then
        'Left 15 characters contains a digit not a character
        Exit For
     End If

   Next

Code: 
If IsNumeric(Right(NewData,4)) = False Then
   'Right 4 digits are not numbers
End If


If any of the above fails then you need to redirect your code to goto

cboJSAref_NotInList_Exit:
Click to expand...
 
I am completely lost now.

I have edited the code in the 'notinlist' event to this. I think I have the validated code inserted in the correct place, but it isn't working so probably not.

Code: 
Private Sub cboJSAref_NotInList(NewData As String, Response As Integer)

'This code allows the user to create a new entry in a combo list and prompts the user to see if they want to add it


    On Error GoTo cboJSAref_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The JSA reference " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "ENOC JSA Database")
    
                If IsValidFormat(NewData) = False Then
                MsgBox "You have entered one or more invalid characters for this reference. Please revise and retry", vbExclamation + vbOKOnly, "Bad Name"
                Response = acDataErrContinue
                End If
                
        If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblJSAref([JSAref]) " & _
        "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new JSA reference has been added to the list." _
            , vbInformation, "ENOC JSA Database"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a unique JSA reference from the list." _
            , vbInformation, "ENOC JSA Database"
        Response = acDataErrContinue
    End If
cboJSAref_NotInList_Exit:
    Exit Sub
cboJSAref_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboJSAref_NotInList_Exit
    

    
End Sub




and I have a module/function with the following text:


Code: 
Public Function IsValidFormat(AnyRef As String) As Boolean

Dim AllowableCharacters As String
Dim bFlag As Boolean
bFlag = True

AllowableCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

If Len(NewData) <> 19 Then
   'Wrong size
End If

For X = 1 To 15
     If IsNumeric(Mid(NewData, X, 1)) = True Then
        'Left 15 characters contains a digit not a character
        Exit For
     End If

   If IsNumeric(Right(NewData, 4)) = False Then
   'Right 4 digits are not numbers
End If

Next
IsValidFormat = bFlag
End Function


I don't understand how these two are working together and I can't get the data to be validated to my format or capitalised.

(I don't have an input mask on the field).

Thank you very much for your help but do you have any other pointers?

Fraser
 
Can you send a snippet of your mdb to look at please
 
You must log in or register to reply here.

Similar threads

J
Solved searching with a partial string
Replies
15
Views
908
John Sh
J
S
Interesting puzzler
2 3
Replies
52
Views
3,672
Gasman
Gasman
E
Solved Time Difference
Replies
7
Views
1,481
E9-Tech
E
AOB
Textbox Input Mask - cursor always defaults to rightmost location (end of mask)
Replies
12
Views
2,364
arnelgp
arnelgp
A
Secure Storage Model for Microsoft Access: Stream-Based Encrypted Data Architecture(for discussion)
Replies
5
Views
430
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom