Inserting default value in part of input mask

[Rajtomar]

I have a DB where user enters CourseName and Then CourseNumber
(Primary key and Unique).. I run a code to check and display a custom
error message if the value in CourseNumber is duplicate.

The CourseName are actually abbreviations like AFF or PSCRB, or MFA
etc...

CourseNumber contains three parts i.e. CourseSerialNumberInCurrentyear
+CourseName+last2digitsofCurrentYrneeds
for example 001AFF09 or 029MFA09 or 010MFA10..

because of different user using the db they enter the same course no.
in different formats like 001AFF09 or 001AFF2009 or 001/AFF/09 or AFF/
001/09 and so many which makes it impossible to check for duplicacy..

I therefore want to force an input mask in CourseNumber Field and need
to know the following...

first three numbers necesaarily to be numbers, last two numbers should
represent year's last two digits, center part can have numbers,
alphabets, symbols and can be of from 02 characters to 10 characters
length.

The input mask for first part and last part is easy, only the center
part is troublesome,. If it is possible something like this
000"/"[CourseName]"/"YY (coursename automaticaly inserts after exit
from Coursename field and is uneditabel, user can only enter 1st and
last parts....


Any help is deeply appreciated.... Icant think of any other place
providing solution form my access problems, this forum has till now
helped me a lot..

Thanks a lot

 

[John Spencer]

The real problem here is that you are attempting to store three pieces of
information in one field. A better design would be to have three fields to
store the information: CourseSerialNumber, CourseName, and CourseYear.

You can set up a unique multi-field index on the three fields and even use
that as the primary key if you desire. Anytime you need to you can concatenate
the three parts together (in a report for instance).

Then your entry form is simplified since you have a control for each part of
the "CourseNumber". I would have a combobox for the CourseName part and let
the users choose from a list based on a query against the table. The
CourseYear could be set up to default to the current year (or some other
value). The course Serial Number could also be generated for new records if
it is a sequential number.

IF you have to live with the current structure, then I would build a VBA
function to validate the value input into the field in the after update. I
don't believe there is any good input mask that you can use to control the
data entry.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Rajtomar]

Thanks a lot for understanding the problem. I completely understand
the flaw in the design. but the thing is that it is very old db
containing lakhs of records, so the first option is not at all
possible.
So can you give me a hint how to validate the entry in the after
update event.

I just want to make sure that first 3 digits and last 02 digits are
only numbers, center part can have upto ten characters... all without
any spaces

Thanks

 

[John W. Vinson]

Thanks a lot for understanding the problem. I completely understand
the flaw in the design. but the thing is that it is very old db
containing lakhs of records, so the first option is not at all
possible.
So can you give me a hint how to validate the entry in the after
update event.

I just want to make sure that first 3 digits and last 02 digits are
only numbers, center part can have upto ten characters... all without
any spaces

Thanks

The problem is the "middle part can be any string" bit.

001AFF09 or 001AFF2009 or 001/AFF/09 all meet that criterion - with middle
strings "AFF" and "AFF20" and "/AFF/" respectively. All of these meet your
criterion - "center part can have numbers, alphabets, symbols and can be of
from 02 characters to 10 characters length". Are they all correct? Are they
all the same, or should they be considered to all be the same? Should they all
be treated as the same value? Should 001.AFF.09 or 011_AFF_09 or 011*AFF*09
all be accepted? Are there *ANY CONSTRAINTS* at all on these?

You have years of data, so there are really no meaningful constraints on the
last two digits either, right? It could be 10 or 01 or 97 if your data goes
back that far.

If the ONLY two meaningful constraints are three numeric digits at the start,
two at the end, and any sort of dog's breakfast (up to ten characters) in the
middle, then you could use

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not (IsNumeric(Left(Me!CourseNumber, 3)) _
AND IsNumeric(Right(Me!CourseNumber), 2))) Then
Cancel = True
MsgBox "Coursenumber must start with three digits and end with two"
End If
End Sub

For data entry you could use Format(Date, "yy") to generate the current year
and save them typing it, and of course you could generate the CourseNumber in
code and not PERMIT them to enter it... but it appears that's not practical.

Your problem is... garbage in, garbage out. You're permitting the user to
enter this critical coursenumber field without any constraints on what they
can enter... so you're stuck dealing with whatever they choose to put in.