Is there a way to create an Formatted Auto Number?

[MsPeppa]

I need to create an automatic generated number. The number is for field "Filenumber" and the format is "WMB2002-" then four numbers i.e. "WMB2002-0001" is there anyway to do this, so that once a user does "WMB2002-0001" the next record wil lbe "WMB2002-0002" etc... Thanks. Also if a record is deleted is that number lost forever or will that be the next number used? Thanks again...

 

[peachmelba]

MsPeppa

I have done this in the past and they way that I did this was to have a table with an autonumber which gives you the last four digits, and a related record id (the record in you existing table). Then when you enter an new record the database opens the table recordset, finds the next number and put in the record id field the new record number. This can then be used to generate the required string.

Uses recordsets which there are several help files on.

This is also usefull as you can reverse the process and if a record is deleted then it can reuse the number.

I hope you are clear about what I am suggesting.

Regs

Peachmelba