id error check

  • Thread starter Thread starter 2Stupid2ownAputer
  • Start date Start date
2

2Stupid2ownAputer

Hello I have an ID field (primary key) of the format 0000.00.
It is set to only allow a unique value.
records would go like this:
0001.01
0001.02
0002.01
0002.02 ...
some more numbers ....
0432.01
0432.02
0432.03
however by mistake I may enter, say
0001.03 instead of 1000.03
so it is a unique key but clearly not a valid one because when ordered by ID
it would be placed between 0001.02 and 0002.01
I figure a possible solution is to ensure that any entry is greater than any
preceding entry
Using datasheet view how could I implement this ?
Thanks In Advance
Regards
 
Create a form (in datasheet view if you wish.)

Cancel the BeforeUpdate event of the form it the new entry is less than
existing ones.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Me.[ID] <= DMax("ID", "Table1") Then
Cancel = True
MsgBox " ID is too low."
End If
End If
End Sub

Presumably ID is a Number type field. It probably has the Field Size
property as Double or Single (in the lower pane of table design.) Using a
floating point number as a primary key is likely to result in matching
errors (rounding problems) at some point. You could get away with using a
Currency type field for this.
 
Hello thanks very much for that info, will try it out
Took on board your suggestion about using a currency format
It never occurred to me that I may have rounding problems
The very most I would have in the decimal part of the ID is 50 (2576.50)
thanks again and best regards
 
using a currency format
Click to expand...

This can be confusing. He's not talking about a Double or Float number
with a currency *FORMAT* - the format is irrelevant and only controls
how the data is displayed. Instead, use a currency *DATATYPE* (it's on
the dropdown list along with Text, Date, Number, and so on).

John W. Vinson[MVP]
 
Hello Yes I understand what you mean currency datatype
Can be no rounding
Thanks
Best Regards
 
Back
Top