Customised autonumber

[noonan1]

I have a project id number field that must contain project codes begining with P and 5 characters in length (this has already been validated for in VBA).I have a button for new projects, when button is pressed I want the number to generate the next value eg:if first project number is P0001, once button is pressed P0002 should automatically be generated, is that possible,if not can it be worked around that just P1 appears and then when button pressed P2. I would welcome any suggestions you may have, thank you for your time.

 

[Darrylles]

Hi,

This is simple string manipulation (although string-manipulation in general is the most difficult aspect of any dev. tool).

I'm assuming that the enclosed 5 digit number started at 00001 and will continue to 99999.

The project id should come from a table like this:

tblProject
ID_pref (key / string)
ID_num (key / number)

this table will only ever hold ONE SINGLE record, and that is the LAST ID to be created (or the NEXT - whichever you choose).

The single record in this table currently has the following values:

ID_pref = "P"
ID_Num = 07

You need a new Project ID, use this code wherever you need it...

Dim NewNum as integer
Dim Prefix as string
Dim ProjID as String

'Open table in memory held in rstProjID....
Set rstProjID = CurrentDb.OpenRecordset("tblProject", dbOpenDynaset)

rstProjID.movefirst                  'Move to first record.
rstProjID.ID_Num = rstProjID.ID_Num + 1  'Increment number.
rstProjID.update                              'Save new Id.

NewNum = rstProjID.ID_Num        'Get New ID into variable.
Prefix = rstProjID.ID_Pref       'Get Prefix into variable.

rstProjID.Close                    'Close memory recordset.

'Now Project Id using NewNum and Prefix variables.

select case (len(str(NewNum))        'Get length of NewNum.
  case 1                                           '1 to 9.
     NewNum = Prefix + "0000" + Str(NewNum)      
  case 2                                         '10 to 99.
     NewNum = Prefix + "000" + Str(NewNum)      
  case 3                                       '100 to 999.
     NewNum = Prefix + "00" + Str(NewNum)      
  case 4                                     '1000 to 9999.
     NewNum = Prefix + "0" + Str(NewNum)      
  case 5                                   '10000 to 99999.
     NewNum = Prefix + Str(NewNum)      
end select

myform!txtProj_Id = NewNum    'Project Id field = 'P00008'.

--------------------------------------

Gotta say - use autonumber instead and label this as the project number - instead of adding a 'P' to it.
MUCH simpler.


Regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Darrylles]

Hi again,

Sorry, following each of Case 1, Case 2, Case 3 etc it should say:

Proj_Id = Prefix + etc

NOT... NewNum = Prefix + etc.

And:

myform!txtProj_Id = NewNum 'Project Id field = 'P00008'.

should read:

myform!txtProj_Id = Proj_ID 'Project Id field = 'P00008'.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[noonan1]

Ill try that straigh away Darrylles and let you know how I get on, thanks for your help.