How to generate random or sequance number

[CSCS]

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.

 

[WayneRyan]

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