Autonumber

  • Thread starter Thread starter Froto
  • Start date Start date
F

Froto

What I need to happen on my form is to have a textbox
automatically assign a number like RFN-1 and so on. I
don't want to use access autonumber feature. Any help
would be greatly appreciated.

Thanks
 
Is the prefix always "RFN-"? If so, then don't store it, but use an
ordinary number field (integer or long) and add the prefix using the format
"RFN-"0.

The next available number can be found using this method:
NextNumber = Nz( DMax( "NumberField", "YourTable" ), 0 ) + 1

Storing "RFN-1" etc as a text field has two major disadvantages: (a) it's
much harder to find the next number in the sequence and (b) it's hard to
sort correctly - using a text sort, RFN-10 and RFN-1003 will come before
RFN-2.

If the prefix does vary, then store it in a separate field, and make a
unique index comprising both it and the number field. If this table is also
used on the "one" side of one-to-many relationships, then I suggest you also
add an autonumber primary key and use that for the relationships.
 
Back
Top