validating against a list - Please Help!

[Guest]

I am wanting to validate numbers as they are keyed in against a list of valid numbers. For example, I will have a list of 1000 - 9 digit numbers, and I would like to check a number as it is entered against that list. Furthermore, I would like a valid number, once entered to no longer be valid, or only be allowed to be entered once

For instance, my valid numbers list is 800000001-800000999 and when 800000235 is eneterd it is determined to be valid, however I do not want that exact number to be allowed again

I imagaine that this can be done in either Excel or Access and I am fine with using either

Any help would be greatly-greatly appreciated

Thanks

dcs

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/NoDupEntry.htm
for preventing duplicate entries.

--
Regards
Frank Kabel
Frankfurt, Germany

I am wanting to validate numbers as they are keyed in against a list

of valid numbers. For example, I will have a list of 1000 - 9 digit
numbers, and I would like to check a number as it is entered against
that list. Furthermore, I would like a valid number, once entered to no
longer be valid, or only be allowed to be entered once.

For instance, my valid numbers list is 800000001-800000999 and when

800000235 is eneterd it is determined to be valid, however I do not
want that exact number to be allowed again.

 

[Tim Ferguson]

For instance, my valid numbers list is 800000001-800000999 and when
800000235 is eneterd it is determined to be valid, however I do not
want that exact number to be allowed again.

All on one line:

= DCount("*","ValidNumbers","IDNumber = " & SuggestedNumber) > 0 AND _
DCount("*","ActualTable","IDNumber = " & SuggestedNumber) = 0


This works if (a) you delete records from ActualTable and (b) you are
allowed to re-use the numbers. If you don't want to re=use them, you need

= DCount("*", "ValidNumbers", _
"IDNumber = " & SuggestedNumber & " AND Used = False")

but if you have multiple users you run the risk of reading the value twice
and trying to use it twice (IYSWIM). In that case you have to set the flag
before using it:

strSQL = "UPDATE ValidNumbers SET Used = TRUE " & _
"WHERE IDNumber = " & SuggestedNumber

db.Execute strSQL, dbFailOnError

If db.RecordsAffected = 0 Then
MsgBox "That number was not available"

Else
' It was okay

End If

Hope that helps (PS I have not tested any of this code!!)

Tim F