Input Mask, and Validating sections of a text field

[hermanko]

Hi,

I have a database that tracks document information. some of the fields
include [File Code], [File Name], [File Location], etc.

I have set the [File Code] with an input mask of >LLL\-0000\-000 (i.e.
ALC-2006-001), where the letters are taken from a fixed list, the
middle section represents a year, and the last section is the document
#. So my example above is document 1 in the ALC-2006 category.

My database allows users to input new document info including entering
a new unique File Code (i have error checking to ensure the code is
unique from the existing database), however what is the best way to
ensure that the "ALC-2006" is taking out of a set list of possible
values? In other words, I want to limit it so that the user won't
accidentally type in the year 3006, of type a 3-letter acronym that
isn't on my fixed list of acronyms.

In my database I have about 10 different possible acroynms that can be
used, and the year can be anywhere from 1999 to 2006.

P.S. this field is NOT a primary key field because of my database being
able to store versions of the same file

Thanks in advance for any recommendations!
Herman

 

[hermanko]

I've begun with some simple code to grab the sections that require
checking:
Again, the input mask forces the File Code field to be entered like so,
for example:
ALC-2006-001

Dim strKey As String
Dim strYear As Integer

strKey = Left(Me.[File Code], 3)
strYear = Mid(Me.[File Code], 5, 4)

My gut is telling me to create a static table that list all possible
3-letter alpha acronyms and then in code, somehow check the "strKey"
against the table and if there is no match then msgbox an error
message. Similar with the Year section, i'd like to check that the year
is not greater than the "current" year (i think there is a function
that returns the current year based on the internal clock).

My vba isn't strong. I know what I want to do but if someone can help
with the syntax that would be great

Herman