IF Statement dependant on combo box

[AMHodgetts]

Hi,

I have a form where people select a customer from a drop down menu. This
then selects a prefix (which is unique to the customer) and the user must
then enter a 4 digit tape number.

The tape numbers are all 6 digits long, the first two being unique and the
latter four being any number ranging from 0001 to 4000.

I am now faced with the problem of people checking out tapes and not
ensuring that they are entering the correct customer in. They are just
selecting a customer and then entering the last 4 digits of the tape id -
regardless of which customer it is for.

With over 8000 tapes in our database, we are now finding that several are
going astray and with the last 4 digits all being the same.....it is
virtually impossible (without a v expensive audit of the offsite storage) to
discover the true locations of the tapes.

Can anybody suggest a way to put in any sort of safeguard to ensure that the
correct tapes go out under the correct customer? I do have validation in
place and this has helped some way towards fixing the issue, however I can
only specify vallidation to look for numbers ie ####, not specific number
ranges.......or can I?

Here is the validation script I have in place now;

Like "####" Or Like "mon" Or Like "tue" Or Like "wed" Or Like "thu" Or Like
"fri" Or Like "sat" Or Like "sun" Or Like "mon_?#a" Or Like "tue_?#a" Or Like
"wed_?#a" Or Like "thu_?#a*" Or Like "fri_?#a" Or Like "sat_?#a" Or Like
"sun_?#a" Or Like "01a" Or Like "01b" Or Like "01c" Or Like "02a" Or Like
"02b" Or Like "02c" Or Like "03a" Or Like "03b" Or Like "03c" Or Like "###"
Or Like "##" Or Like "mon_?#" Or Like "tue_?#" Or Like "wed_?#" Or Like
"thu_?#*" Or Like "fri_?#" Or Like "sat_?#" Or Like "sun_?#" Or Like "#" Or
Like "?#" Or Like "monb1" Or Like "monb2" Or Like "mona1" Or Like "mona2" Or
Like "monc1" Or Like "monc2" Or Like "tueb1" Or Like "tueb2" Or Like "tuea1"
Or Like "tuea2" Or Like "tuec1" Or Like "tuec2" Or Like "wedb1" Or Like
"wedb2" Or Like "weda1" Or Like "weda2" Or Like "wedc1" Or Like "wedc2" Or
Like "thub1" Or Like "thub2" Or Like "thua1" Or Like "thua2" Or Like "thuc1"
Or Like "thuc2" Or Like "frib1" Or Like "frib2" Or Like "fria1" Or Like
"fria2" Or Like "fric1" Or Like "fric2" Or Like "satb1" Or Like "satb2" Or
Like "sata1" Or Like "sata2" Or Like "satc1" Or Like "satc2" Or Like "sunb1"
Or Like "sunb2" Or Like "suna1" Or Like "suna2" Or Like "sunc1" Or Like
"sunc2" Or Like "mon01" Or Like "tue01" Or Like "wed01" Or Like "thu01" Or
Like "fri01" Or Like "sat01" Or Like "sun01" Or Like "mon01a" Or Like
"tue01a" Or Like "wed01a" Or Like "thu01a" Or Like "fri01a" Or Like "sat01a"
Or Like "sun01a"

Any help would be greatly appreaciated.

 

[BruceS]

Ann,

Couple of things, don't know how helpful they will be or even if you can do
anything about it. Sometimes we're stuck with what's already there.

1. You can put a unique "tapeID" on each tape that includes the CustomerID,
instead of using just the 4-digit serial number, and require entry of the
entire number. A "tapes" table would then tie the tape to the correct
customer. If the tapeID is only 4 digits, you are 80% to capacity with
4-digit numbers, anyway.

2. From what you wrote, it's difficult for me to determine what you are
trying to validate. There appear to be days of the week that can be entered
in a variety of formats, some with a sequence number appended. Is it
possible to force a consistent format, say with a combo box to select the day
or by using Format(Date,"Ddd") or something similar? With more detail on
what you're trying to accomplish, and specifics on what consistent result you
want, someone in the group may be able to help.

3. In general, you need to have more control over what a user enters.
Instead of using a Validation Rule, how about placing VBA logic in the
Before_Update event of the field? You could then try a lookup of the
customer to validate that tape is for that customer, verify the day of week
field, and perform any other test on the input that need to do. You could
also be more specific in the error messages you show the user as to why what
they entered won't work. (Call it user training!)

4. You already have 8,000 entries in the database, and I'm sure that it's
growing. You are right to want to get control over it now, before it gets
any worse.

Bruce