Organising Chaos

[Sprocket]

I have a database that I inherited, which I now have to maintain.
Works OK in the main but one table is causing me problems.

The key field ... Student ID is a 9 character text field.

There are several possible entry formats for this

S******* = 1 letter and 7 numbers

S******X = letter, six numbers, letter

*******X = seven numbers and 1 letter

******* = seven numbers


Eventually the first three forms will dissapear and only format ie. the last form with 7 numbers will exist. I will then be able to change the field type to long and put a few restrictions on it, but that could still be three years away - when students graduate.

However, in the meantime I am finding that data entry is a problem as some people end up entering the student's name in the number field and I don't know how to prevent this. There appears to be no way to validate this error out - being a text field and all. I can't use an input mask as there are four forms.

Any suggestions welcome

 

[Mile-O]

On the Before_Update event of the name textbox you could have something like this:

If IsNull(Me.txtForename) Then Exit Sub
If IsNotName(Me.txtForename) Then Cancel = True

Then, in a module:

Public Function IsNotName(ByVal strText As String) As Boolean

    Dim intCounter As Integer, intValue As Integer
    
    strText = UCase(strText)
    
    For intCounter = 1 To Len(strText)
        intValue = Asc(Mid(strText, intCounter, 1))
        If ((intValue >= 65) And (intValue <= 90)) Or ((intValue = 45) Or (intValue = 32) Or (intValue = 39)) Then
            IsNotName = False
        Else
            IsNotName = True
            Exit Function
        End If
    Next intCounter

End Function

 

[Sprocket]

Mile-O-Phile.....Many thanks for the pointer

Works a treat.

Sprocket