Hi All,

I'm designing this database, in which, I have to give each record a unique random (or sequance) number of 5 digits, giving that the first digit must be retrived from another field.

Please Help, I need this ASAP!

Any help will be very much appreciated!

Thanks,
CS.

CS,

If you seperated them number into it's two components, it would be a lot easier.

Use the form's BeforeInsert event.

Assuming your key field is an integer called [YourKeyField] and
that the "Seed Number" is in Me.OtherField, this should do it:


NewNumber = Nz(DMax("[YourKeyField]", _
"YourTable", _
"[YourKeyField] Between " & CLng(Left(Me.OtherField, 1)) * 10000 & " And " & _
CLng(Left(Me.OtherField, 1)) * 10000 + 9999), 0) + 1



Easier with two pieces.


YourKeyFieldPart1 = Me.OtherField
YourKeyFieldPart2 = Nz(DMax("[YourKeyFieldPart2]", _
"YourTable", _
"[YourKeyFieldPart1] = " & Me.OtherField), 0) + 1


Wayne