Data Validation Rule problem 1

[diezzz]

Hello,

I have a table with the following fields "ID", "name", "account". I've also build a form to insert data in this table.

Every ID has 9 numbers, the problem is that if i do insert this rule in the table, when i open the form it gaves me the "validation error", because the field dosen't have 9 numbers to begin with.

All that i managed so far is to have by defaut 000000000, however i'm looking for another solution.

Anyone has some ideeas on what should be done in order to have nothing im the ID number, or 0, or NA when the form is loaded?

Often me or my collegues save the records with less than 9 numbers or with 000000000 as ID.

Thanks

 

[Remou]

Why not check the number in the Before Update event for the form? You can cancel if it does not validate.

 

[jsteph]

The validation rule could be Len([fieldname]) = 9 or is null.
--Jim

 

[SkipVought]

Here's the problem...

columns, like Invoice Numbers, Part Numbers, Employee Numbers, SSN's, Zip Codes...

are not REALLY numbers, even though they may be comprised by all numeric characters.

You will NEVER perform arithmetic operations on an IDENTIFIER.

They are ususally bounded implicitly by a LENGTH.

Leading ZEROS are significant.

So IDENTIRIERS ought to be CHARACTER of a defined length.

BIG difference between
TEXT
[tt]
00000001
[/tt]
and
NUMBER
[tt]
1
[/tt]
or
TEXT
[tt]
1
[/tt]
Neither is identical.

Skip,

_[red][/red]

 

[diezzz]

ID is set up as text, the point was that the user had to see it as a number.

Thank you all, i did some coding in the beforeupdate and now it works fine.

 

[diezzz]

ah another problem,

How can i make my form to not update in the after update event?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cucu As String
If Len(SSO) < 9 Then
cucu = msgbox(...)
exit sub
end if

Well it gaves me the msgbox, but it saves the record, witch is normal because i don't have anything to stop it save.

 

[Remou]

Use the Before Update event

 

[PHV]

Cancel = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886